If Then Calculations

[Guest]

Help!
I'm a small business owner trying to design a commission spreadsheet based
on commissions that depend on profits.

For Instance:
Profits = 1000 - 5000, Commission = 2%
Profits = 5001 - 10000, Commission = 4%
Profits = 15001 - 20000, Commission = 5%

I can figure out how to make words fill the cell based on the number input
but I cant get it to perform a calculation based on the number input.

Help!

Marti

 

[JE McGimpsey]

If your commission schedule is NOT progressive (i.e., if a profit of
$5,000 earns $5,000*2% = $100, while $5,001 earns $5,001*3% = $150.03):

=A1*LOOKUP(A1, {0,0;1000,0.02;5000.01,0.03;15000.01,0.05})

If your schedule IS progressive (e.g., $5,001 earns $100 + $1*3% =
$100.03):

http://www.mcgimpsey.com/excel/variablerate.html

 

[Guest]

Thanks a million! It worked!!!

Marti

If your commission schedule is NOT progressive (i.e., if a profit of
$5,000 earns $5,000*2% = $100, while $5,001 earns $5,001*3% = $150.03):

=A1*LOOKUP(A1, {0,0;1000,0.02;5000.01,0.03;15000.01,0.05})

If your schedule IS progressive (e.g., $5,001 earns $100 + $1*3% =
$100.03):

http://www.mcgimpsey.com/excel/variablerate.html

 

[T. Valko]

You have an uncovered range in your schedule: greater than 10000 but less
than 15001.

Try this:

=A1*LOOKUP(A1,{0,1000,5001,10001,15001},{0,0.02,0.04,0.045,0.05})

I've "covered" that missing range with a commission of 4.5%. Change to suit.

Note: any value greater than 15000 will receive a 5% commission.