IF function limit

[jim]

Can anyone sugget a way of getting around limit to 7
nested IF statements?
thanks
jim

 

[Andy B]

Hi

AFAIK there is no way to extend the number of nested IFs beyond 7. The
chances are that there is a better way to handle the problem - maybe with a
table of data and using VLOOKUP or INDEX and MATCH.

 

[Ron Rosenfeld]

Can anyone sugget a way of getting around limit to 7
nested IF statements?

There is no way of nesting more than seven functions. The "way of getting
around" it is to use a different algorithm. Usually INDEX, MATCH or one of the
LOOKUP functions will be a cleaner way of doing something.


--ron

 

[Gord Dibben]

ron

Have a look at Chip Pearson's named range method of getting around the limit
of 7 nested ifs.

http://www.cpearson.com/excel/nested.htm

Gord Dibben Excel MVP

 

[Ron Rosenfeld]

ron

Have a look at Chip Pearson's named range method of getting around the limit
of 7 nested ifs.

http://www.cpearson.com/excel/nested.htm

Gord Dibben Excel MVP

I've seen that, and it does have some uses. But I still believe that, in
general, one of the lookup functions will be a better solution.


--ron

 

[Frogpod]

One way to effectively double the number of if statments allowed is t
use an "AND" or "OR" statment. For example, I wanted to apply i
statments for if value=x, divide a1 by 8. If value =y, divide a1 by 9


You can say if a1 is greater than or equal to 2 but less than 8, the
do if statment1, if statment2...if statment7

Set the FALSE value for the AND statment to if statment8...if statmen
14.

It only works on values you can get a true, false from an and or o
statment. I suppose you could put several and/or statments in th
original if statment

 

[Ron Rosenfeld]

One way to effectively double the number of if statments allowed is to
use an "AND" or "OR" statment. For example, I wanted to apply if
statments for if value=x, divide a1 by 8. If value =y, divide a1 by 9.


You can say if a1 is greater than or equal to 2 but less than 8, then
do if statment1, if statment2...if statment7

Set the FALSE value for the AND statment to if statment8...if statment
14.

It only works on values you can get a true, false from an and or or
statment. I suppose you could put several and/or statments in the
original if statment.

Sure, there are all kinds of complicated workarounds so you can get oodles of
IF statements. But all of the ones I've seen have been a lot more complicated
(and consequently difficult to modify and/or debug) than a relatively simple
lookup table.

At least it has always seemed that way to me. I have not come across an
instance where there appears to be a real advantage to a complicated IF
construct, compared with other methods that are easier to modify and debug.

I'm not saying that doesn't exist -- just that at my relatively low level of
experience, I have not seen it.




--ron