Multifunction for one cell

[JeanC]

I need to calculate based on "if "scenarios for the purpose of commission
calculations.
i.e. if b3 (commission to be split) = 0-$5000. then c3 =b3*50%, if b3
=5001-10001, then c3 = b3*55% and so on. Is this too much to ask? How many
"if's" can I place to one cell? Thanks for any help.

 

[Arvi Laanemets]

Hi

To answer your last question - 7 IF's nested - there is no limit for not
nested ones.
About formula - you don't need any IF's (except to check for missing data,
maybe).

When the percent step is always 5%, then like this:
=B3*(45%+5%*MATCH(B3,{0;5001;1001;1501;...},1))
(you can have up to 24 different value intervals to match)

When there isn't any pattern, then like this:
=B3*CHOOSE(MATCH(B3,{0;5001;1001;1501;...},1),50%,55%,60%,...)