MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Nested IFs

Posted by David McLeish on July 31, 2001 11:27 AM

How would I type a formula to display varying levels of commission, displaying the message "No commission" if the figure was below a certain value?

For a sale over 119, 5% commission; a sale over 139, 10% commission; a sale over 159, 15% commission; a sale over 179, 20% commission.

A textual piece of data stating the percentage of commission is to be shown, rather than the ACTUAL commission figure.

Please help!


Posted by Aladin Akyurek on July 31, 2001 11:33 AM

=VLOOKUP(A1,{0,"No Commission";119,5%;139,10%;159,15%;179,20%},2,0)


=VLOOKUP(A1,{0,"No";119,5%;139,10%;159,15%;179,20%},2,0)&" commission"


Posted by D on July 31, 2001 11:54 AM

Thanks! But it has to be using Nested IFs for a assignment.

Posted by Aladin Akyurek on July 31, 2001 12:25 PM

I hope they know the side-effects what they are trying "teach." :-)

=IF(A1>179,20,IF(A1>159,15,IF(A1>139,10,IF(A1>119,5,"No"))))&"% commision."

Give the lecturer or whatever the IF-formula along with the VLOOKUP version. & tell him/her that the human cognition is about good/confortable with 3 nested IFs, and not much more.


Posted by David on July 31, 2001 12:58 PM

hehe thank you very much! Admittedly clumsy, but unfortunately it's a specification of this bloody assessment! Bah!

Many thanks again mate! :)