MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Nested IF


Posted by bill roberts on February 28, 2000 9:03 AM

On a worksheet, I have an 8 level NESTED IF statement.

I want more!

Any idea?

An event handler with a SELECT CASE sound good but..


Posted by Chris on February 28, 2000 11:35 AM

Bill,

Nested Ifs, as you have found out, have a limitation of 7 levels. I generally don't use nested ifs at all. Instead, I use boolean logic in my formulas. It is easier to follow, and is unlimited in the number of levels.

An example of this logic is at:

http://geocities.com/aaronblood/ExamplesLogic.html

Look at BinSwitch.xls

You can also name ranges containing nested ifs and compare those to create a nested if with more criteria. An example of this can be found at:

www.cpearson.com

HTH,
Chris

Posted by Jaime on February 29, 2000 6:51 AM

Instead of an if() statement , Use a combination of the index() formula and match()


Posted by bill.roberts on February 29, 2000 8:35 AM

OK, been there done that.

This is quite a radically different approach to the IF statement.

In fact, my first attempt actually started of with an IF! Of course it was a TYPO <GRIN>.

Now for the meat...

I have 10 columns spaced 3 apart.

The column structure is used to track the completion of an activity.

My objective is to determine the greatest value.

All 10 columns could equal 100%

Or, the first might equal 15%

The next 2 equal 32%

The next (4th) might be 57%

The 5th through the 8th could be at 89%

Finally, the 9th and 10th equal 100%

A simple range reference seemingly is not the answer.

A named range would seem bulky:
1) i'd need 20 or so rows also named.
2) I assume the sheet to grow in the future.

Posted by Chris on March 01, 2000 5:29 AM

Bill,

I still can't figure out what it is that you are trying to do. In your last message you state that you want to find the greatest value. If that was the case, you would use the MAX() formula. I presume it is not that simple or you wouldn't be posting here. What do you want?

Chris

Posted by bill.roberts on March 01, 2000 7:03 AM


Chris,

You probably won't belive but you answered my question.

I did want the MAX value of a range but since I did NOT know that you could specify a mixed range without naming it first, I used the NESTED IF.

Also, I was unaware of the MAX function.

Also, I would NOT have used the MAX function had I of known about it because my mind was not tuned in this way when I started this endeavour.

Only after your last response did I look at the problem differently and realize i DID want the MAX value. Then I experimented with...BHAM!!

bill

Posted by Chris on March 01, 2000 11:23 AM

Bill,

Sometimes it just takes a fresh perspective.

Chris