IIf and value-errors

  • Thread starter Thread starter david
  • Start date Start date
D

david

I have a column with numbers A (as text, I want to keep initial zeros):

05...
07...
08...
12...
41...

In another column B I want a letter that depends on the initial numbers
in the A-column. If the initial number is 0 I want an "F" in B, if it is
07 I want "M" in B and in all other cases I want an "U" in B:

F 05...
M 07...
F 08...
U 12...
U 41...

I created a formula for this purpose:

=IF(
FIND("07";LEFT(B3;2);1);
"M";
IF(FIND("0";LEFT(B3;1);1);"F";"U"))

The problem is that it only works for the M-cases. All other cases
return a value-error. However, if I break out line 4 in the formula it
works as expected. I am puzzled.

Any ideas why it doesn't work and how I can fix it?

This is in MacExcel but that shouldn't matter, should it?
 
Not sure why your original formula doesn't work (not sure why you're using
FIND) but this should do what you want.

=IF(LEFT(B3;2)="07";"M";IF(LEFT(B3;1)="0";"F";"U"))
 
Hi David,
You're puzzled? What about me?
My Excel rejected the use of ; where there should have been ,
I came up with the same formula as Ian, but with commas, not
semicolons.
What's going on there?
Using FIND didn't work because if the text is not found the #VALUE
error is returned rather than FALSE and this stuffs up the whole
formula.
Ian's formula works because it traps the 07's first so they're then not
included in the testing for the 0's
Ken Johnson
 
The list separator is defined by a windows setting--under regional settings.
Most people in the USA use a comma for the list separator. Many in other parts
of the world use a semicolon.

And if you did want to use =find() or =search() (not a particularly good fit in
this example), you could check to see if it actually found something:

=if(isnumber(find("ken",a1)),"Yep","nope")

=find() is case sensitive
=search() is not.
 
Ken Johnson said:
You're puzzled? What about me?
My Excel rejected the use of ; where there should have been ,
I came up with the same formula as Ian, but with commas, not
semicolons.
What's going on there?
Click to expand...

I think the reason for the comma/semicolon issue is that I don't use US
number format, but a European number format with comma as the decimal
indicator.
 
Ian said:
Not sure why your original formula doesn't work (not sure why you're using
FIND) but this should do what you want.

=IF(LEFT(B3;2)="07";"M";IF(LEFT(B3;1)="0";"F";"U"))
Click to expand...

This one behaved very unpredictable. The firs few lines from my sheet,
erroneous results are marked with an * with the correct letter in the
next column:

M 07...
U 07... * M
F 41... * U
M 01... * F
M 07...


This didn't look to good;-) Any further suggestions?
 
Hi David,
Can you guarantee that there are no leading spaces. If not then perhaps
you should include the TRIM function as well...

=IF(LEFT(TRIM(B3);2)="07";"M";IF(LEFT(TRIM(B3);1)="0";"F";"U"))

Ken Johnson
 
Ken Johnson said:
Can you guarantee that there are no leading spaces. If not then perhaps
you should include the TRIM function as well...
Click to expand...

At least searching for space in that column didn't return any hits.
 
Dave Peterson said:
And if you did want to use =find() or =search() (not a particularly good
fit in this example), you could check to see if it actually found
something:

=if(isnumber(find("ken",a1)),"Yep","nope")

=find() is case sensitive
=search() is not.
Click to expand...

I used find because I thought I had too.

Any other suggestions? Isn't it weird that the first two 07... returns
different results?
 
I was responding more to Ken's followup.

But I would have thought that Ian's formula would have been sufficient.

Are you sure those 07's are really text--not numbers with a custom format (like
00)?
 
Looking at the results you have shown, I tend to agree with Ken Johnson's
suggestion that you may be referencing the wrong row. The quoted formula
referencing B3 should be in row 3. It looks like it may be in row 2,
therefore referencing the row below itself (hence the first M is from the
second 07, U from the 41, F from the 01, M from the 07 and I suspect the
next entry below the ones quoted begins 07).
 
Ian said:
Looking at the results you have shown, I tend to agree with Ken Johnson's
suggestion that you may be referencing the wrong row.
Click to expand...

It seems you are right. Don't know how it happened and how it slipped
me. I checked for it several times without catching the problem.

Thanks.
 
Back
Top