# Thread: More than 7 Nested Ifs (Excel 2002)

1. ## More than 7 Nested Ifs (Excel 2002)

Hi

We are frequently advised we cannot use more than 7 IFs, which is normally so, However on searching various websites I found this little gem, there are probably better ways of doing things, but if you want to go just over the seven this would be ideal (please see attached sheet). In this sheet it uses Thirteen.

I hasten to point out this not my own doing. If I remember where I found it I will post it.

2. ## Re: More than 7 Nested Ifs (Excel 2002)

Thanx Braddy. Very interesting! Thanx for the idea.

I speak for myself though....
I'd rather use VLookup and a reference table before I do it this way though! (Much to much to check if modifications are needed!)

Cheers

3. ## Re: More than 7 Nested Ifs (Excel 2002)

Hi Rudi

Thanks for your input, I just though it might be useful for simpler spreadsheets, the kind which people at my level create. But I agree with you in principle.

4. ## Re: More than 7 Nested Ifs (Excel 2002)

<hr>might be useful for simpler spreadsheets<hr>

A spreadsheet that calls for 7 or more nested IFs is never going to be simple!

Even so, the following formula would give the same result as the one in your spreadsheet, with no IFs, LOOKUPs etc, and no need for an intermediate cell to calculate the result:
=(\$A\$4=1)*11+(\$A\$4=2)*22+(\$A\$4=3)*33+(\$A\$4=4)*44+( \$A\$4=5)*55+(\$A\$4=6)*66+(\$A\$4=7)*77+(\$A\$4=8)*88+(\$A \$4=9)*99+(\$A\$4=10)*100+(\$A\$4=11)*110+(\$A\$4=12)*120 +(\$A\$4=13)*130

Cheers

5. ## Re: More than 7 Nested Ifs (Excel 2002)

HI Macropod

I see what you mean, but then not everthing is simple until you know how to do it is it? <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

Thanks

6. ## Re: More than 7 Nested Ifs (Excel 2002)

Of course the rather silly example in cell I4 and I5 can be done this way too:

Or:

7. ## Re: More than 7 Nested Ifs (Excel 2002)

Hi Jan Karel

It's begining to look as if I am too inexperienced to have made this post in the first place!.

I will stick to asking for help in future. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

8. ## Re: More than 7 Nested Ifs (Excel 2002)

oo i never thought about that, good idea jan karel! <img src=/S/lightbulb.gif border=0 alt=lightbulb width=15 height=15>

9. ## Re: More than 7 Nested Ifs (Excel 2002)

What you found works both for you and the person you found it from. Not everybody is like Rudi and can automatically think in VLOOKUPs.

10. ## Re: More than 7 Nested Ifs (Excel 2002)

Hi JK,

Not quite - for \$A\$4<10 the multiplier is 11. Not like you to miss that.

Taking my earlier response one step further:
=(INT(\$A\$4)<10)*(INT(\$A\$4)>0)*INT(\$A\$4)*11+(INT(\$A \$4)>9)*INT(\$A\$4)*(INT(\$A\$4)<10)*10
Of course, if the user can be certain that \$A\$4 will only ever hold integers, all the INT expressions in the above can be deleted.

Cheers

11. ## Re: More than 7 Nested Ifs (Excel 2002)

<hr>It's begining to look as if I am too inexperienced to have made this post in the first place!.<hr>
Its impossible to compare your brain with 10 million other brains out there! You will come up feeling like a dumbo 100% of the time!
I've lots of times posted tips and ideas, thinking they were great, and then to discover a guru posting some other way that is 100 times better than my tip. The GREAT thing about this is that you learn and it opens your eyes to the flexibilities that Excel does provide.

Never back away from posting anything. It always provides an answer for someone else, and provides you with new ideas from that which you posted.

Lounge slogan : TODAY, TOMORROW, TOGETHER WE RULE!

12. ## Re: More than 7 Nested Ifs (Excel 2002)

The method you posted is valid. It may not be the best solution in all circumstances, but I'm sure it will be useful to some.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•