Maxed out in nesting "if" statements

**MNN** · 2011-09-24 19:36

It appears that I maxed out in an if formula with a total of 9 if statements. Any way around this dilemma. at this time I will need to test for several more criteria. What is the Max number one can use in this formula.

Thanks

**johnhutchison** · 2011-09-24 20:56

The limit is 7.

The way around this depends on exactly what you are doing.

One solution is to use a Vlookup, but that is not always applicable.

**pccoyle** · 2011-09-25 13:42

Check out Chip Pearson's site, he has a technique using named ranges that may help you HTH

**RetiredGeek** · 2011-09-25 13:53

Another option is to create a user defined function {UDF} that does all the testing and returns the appropriate answer into the cell.

**RussB** · 2011-09-25 19:41

Or make the spreadsheet less complex and simpler for the next person and break it into two or more cells.

**MNN** · 2011-09-25 22:02

What is a UDF user defined function and how will this help me accomplish my goal?

**johnhutchison** · 2011-09-25 23:17

A function is something like Sum or Count, where the word is the name of a procedure. Sum and Count are prewritten functions that come with Excel.

It is possible to create your own functions, by writing out in VBA (Visual Basic) what job you want done. these are User Defined functions.

VBA is more flexible so you would not be limited to 7 if statements.

You could find out more about them with a google search for Excel UDF. This is a page with more info and some examples.

**sdckapr** · 2011-09-26 04:59

Perhaps if you provided some details, someone could come up with a formula or lookup scenario that worked or help to create a UDF for you. Without some details, there are just too many possibilities...

Steve

**Maudibe** · 2011-09-27 02:50

An alternative to nesting "IF" statements may be to use the "CHOOSE" function. Without knowing exactly what criteria you are evaluating, this may or may not apply to your case. If you want the value of a cell to be a certain value picked from a list (value1, value2, value3,...) based on the value of another cell or experssion (index_num), then this is the formula you want. CHOOSE(Index_num, value1, value2, value3,...)
ex: CHOOSE(1, "hello", 6, E7, "good bye", 10/H5) Index_num of 1 selects the first value in the list "hello"
CHOOSE(K2, "hello", 6, E7, "good bye", 10/H5) The value of cell K2 indicates the the selection of the value list. If cell K2= 3 then evaluates to the contents of cell E7
CHOOSE(A1+B1, "hello", 6, E7, "good bye", 10/H5) The expression value of Cells A1 + B1 determines selection of the vaule list. If A1=3 and B1=2 then the formula
evaluates to the 5th value in the list which is 10 divided by the vaule of cell H5
This my shorten the process of elimination by nesting "If" statements if this is what you are trying to achieve. HTH

**WebGenii** · 2011-09-28 13:20

Also, what version of Excel are you using?

Thread: Maxed out in nesting "if" statements

Thread Tools