IIf and value-errors

[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?

--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

[Ian]

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

--
Ian
--
"david" <[email protected]> wrote in message
news:1ha8fml.11oh8s8ndnm8lN%[email protected]...
>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?
>
> --
> A: Because it messes up the order in which people normally read text.
> Q: Why is top-posting such a bad thing?
> A: Top-posting.
> Q: What is the most annoying thing on usenet and in e-mail?

[Ken Johnson]

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

[Dave Peterson]

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 wrote:
>
> 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

--

Dave Peterson

[david]

Ken Johnson <[email protected]> wrote:

> 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?

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.


--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

[david]

Ian <[email protected]> wrote:

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

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?


--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

[Ken Johnson]

Thanks for that David.
Ken Johnson

[Ken Johnson]

Thanks Dave
Ken Johnson

[Ken Johnson]

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]

Hi David,
Or, is the formula addressing the correct row in column B?
Ken Johnson

[david]

Ken Johnson <[email protected]> wrote:

> Can you guarantee that there are no leading spaces. If not then perhaps
> you should include the TRIM function as well...

At least searching for space in that column didn't return any hits.

--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

[david]

Dave Peterson <[email protected]> wrote:

> 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.

I used find because I thought I had too.

Any other suggestions? Isn't it weird that the first two 07... returns
different results?

--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

[Dave Peterson]

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)?

david wrote:
>
> Dave Peterson <[email protected]> wrote:
>
> > 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.
>
> I used find because I thought I had too.
>
> Any other suggestions? Isn't it weird that the first two 07... returns
> different results?
>
> --
> A: Because it messes up the order in which people normally read text.
> Q: Why is top-posting such a bad thing?
> A: Top-posting.
> Q: What is the most annoying thing on usenet and in e-mail?

--

Dave Peterson

[Ian]

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
--
"david" <[email protected]> wrote in message
news:1ha8kq2.8lsiu290o3sN%[email protected]...
> Ian <[email protected]> wrote:
>
>> 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"))
>
> 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?
>
>
> --
> A: Because it messes up the order in which people normally read text.
> Q: Why is top-posting such a bad thing?
> A: Top-posting.
> Q: What is the most annoying thing on usenet and in e-mail?

[david]

Ian <[email protected]> wrote:

> Looking at the results you have shown, I tend to agree with Ken Johnson's
> suggestion that you may be referencing the wrong row.

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.

--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

[Ken Johnson]

Hi david,
That clears up that mystery.
Thanks for letting us know.
Ken Johnson