Nested IF, reports incorrectly, can't find anything like it to ref

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris T-M - 11 Sep 2007 17:46 GMT

I've boiled down a problem Function to the following statement:
=IF(I54=OR(2,3,4),"Low","")
I54 = 2
Function returns ""

Reply to this Message

Michael - 11 Sep 2007 17:50 GMT

Try this:
=IF(OR(I54=2,I54=3,I54=4),"Low","")

Signature

If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

> I've boiled down a problem Function to the following statement:
> =IF(I54=OR(2,3,4),"Low","")
> I54 = 2
> Function returns ""

Reply to this Message

Chris T-M - 11 Sep 2007 17:56 GMT

Duh! Thank you very much.

Now if I can just simplify 21 nested IF statements into 7 I'll be set.

> Try this:
> =IF(OR(I54=2,I54=3,I54=4),"Low","")
[quoted text clipped - 3 lines]
> > I54 = 2
> > Function returns ""

Reply to this Message

JMB - 12 Sep 2007 04:58 GMT

Alternate solutions often use a lookup function instead (eg Lookup, Vlookup,
Index/Match). Choose might also be worth taking a look at (see XL help for
description).

Other methods (defined names or user defined functions) are discussed here:
http://www.cpearson.com/Excel/nested.htm

> Duh! Thank you very much.
>
[quoted text clipped - 7 lines]
> > > I54 = 2
> > > Function returns ""

Reply to this Message

ilia - 12 Sep 2007 15:20 GMT

What are the statements?

On Sep 11, 12:56 pm, Chris T-M <Chri...@discussions.microsoft.com>
wrote:

> Duh! Thank you very much.
>
[quoted text clipped - 14 lines]
>
> - Show quoted text -

Reply to this Message

Ron Coderre - 11 Sep 2007 17:52 GMT

Each component of the OR function must return a TRUE/FALSE value....
however, if those components return numeric values:
0=FALSE,
any other number=TRUE.

Consequently, OR(2,3,4) is equal to OR(TRUE, TRUE, TRUE)

Try on of these:
=IF(OR(I54=2,I54=3,I54=4),"Low","")
or
=IF(OR(I54={2,3,4}),"Low","")

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)

> I've boiled down a problem Function to the following statement:
> =IF(I54=OR(2,3,4),"Low","")
> I54 = 2
> Function returns ""

Reply to this Message

Chris T-M - 11 Sep 2007 18:08 GMT

Thank you also. They both work, but the second is less "wordy"

> Each component of the OR function must return a TRUE/FALSE value....
> however, if those components return numeric values:
[quoted text clipped - 22 lines]
> > I54 = 2
> > Function returns ""

Reply to this Message