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!
David
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)
or
=VLOOKUP(A1,{0,"No";119,5%;139,10%;159,15%;179,20%},2,0)&" commission"
Aladin
Posted by D on July 31, 2001 11:54 AM
Thanks! But it has to be using Nested IFs for a comp.science 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.
Aladin
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! :)
David
