If logic problem

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",1 7.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

:|




"Jeff Smith" wrote in message
...
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",1 7.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

"Jeff Smith" wrote...
...
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.
...

=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")))

--
To top-post is human, to bottom-post and snip is sublime.

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




"Dave R." wrote in message
...
try

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

.. with your stuff wrapped around it

:|




"Jeff Smith" wrote in message
...
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",1 7.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

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




"Harlan Grove" wrote in message
...
"Jeff Smith" wrote...
..
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.
..


=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")))

--
To top-post is human, to bottom-post and snip is sublime.