If logic problem

  • Thread starter Thread starter Jeff Smith
  • Start date Start date
J

Jeff Smith

Hi,

There has been an amendment to a National Standard that has created a
problem for me.

Products are in the (strength) range 17.5,20,25,30,35 etc up to 70
I have isolated the unique problem with the 17 being 17.5 and dealt with it.

My problem is sometimes the values I am extracting are in the 1st and 2nd
postion e.g:

=IF(Create!D6="","",IF(LEFT(Create!D6,2)="17",17.5,VALUE(LEFT(CreateMix!D6,2
))))

BUT now, some of the values I need to extract are in the 2nd and 3rd
position e.g. In this situatiuon the first character is always a letter
followed by the two numbers I need to extact.

=IF(Create!D6="","",IF(LEFT(CreateMix!D6,2)="17",17.5,VALUE(MID(Create!D6,2,
2))))

I have tried all kinds of new formulas to no avail. In english I need to
say:

If the cell in Create! D6 is empty, do nothing.
If the contents of Create!D6 is (say) 20xxxxxxxxx take the value "20" from
the first two characters.
If the contents of Create!D6 is (say) P20xxxxxxxxxx take the value "20" from
the 2nd and 3rd characters.

I hope I have described my problem clearly. Any assistance would be greatly
appreciated.

Thanking you in anticipation,

Jeff Smith
 
try

=IF(ISERROR(--LEFT(A1,1)),--MID(A1,2,2),--LEFT(A1,2))

... with your stuff wrapped around it

:|
 
...
...
I have tried all kinds of new formulas to no avail. In english I need to
say:

If the cell in Create! D6 is empty, do nothing.
If the contents of Create!D6 is (say) 20xxxxxxxxx take the value "20" from
the first two characters.
If the contents of Create!D6 is (say) P20xxxxxxxxxx take the value "20" from
the 2nd and 3rd characters.
Click to expand...
...

=IF(ISNUMBER(-LEFT(Create!D6,2)),LEFT(Create!D6,2)+(LEFT(Create!D6,2)="17")/2,
IF(ISNUMBER(-MID(Create!D6,2,2)),MID(Create!D6,2,2)+(MID(Create!D6,2,2)="17")/2,
IF(Create!D6="","","<UNEXPECTED>")))
 
Thanks for the quick reply Dave. I don't understand about wrapping my stuff
around it :- (

I tried inserting your suggested solution in several places but to no avail.
Would I be asking too much for you to copy/past my if statement and insert
your suggested ISERROR code so I can better understand.

(I do have a glimmer of an understanding about your solution. There is the
makings of me learning something really new so I can use this technique in
other appplications). I do appreciate you taking the time to help me.

sincerely

Jeff Smith
 
Harlan,

This works well. I'm going to have to do some further reading in Excel as I
don't know why this works ... but I'm learning.

Thnaks for taking the time to help. It is appreciated.

sincerely

Jeff Smith
 
Back
Top