how do I combine multiple IF statements to come up with 1 value?

J

Johanna

I am trying to create an automated commission calculation sheet for my sales
team, however I can't find how to create the formula.

What I'd like to achieve is the following:

revenue:
0 - 10 ==> no commission
10.1 - 20 ==> 2.5% commission of the amount over and above 10
20.1 - 30 ==> 5% commission of the amount over and above 20
etc.

The formula I created now double counts the amount of revenue and that is
not the intention.

Thank you for your help!
 
B

Bill Kuunders

Set up a table

treshold marginal rate differential rate
10000 0.025 0.025
20000 0.05 0.025
30000 0.075 0.025
40000 0.1 0.025
50000 0.125 0.025


use the formula
=SUMPRODUCT(--(D5>$A$4:$A$8), (D5-$A$4:$A$8), $C$4:$C$8)
where D5 holds the amount sold
A4 to A8 is the tresholds
C4 to C8 are the incremental %'ges

you can change the borders and or the rates in the table without having to
change formula's

for a detailed explanation go to
http://www.mcgimpsey.com/excel/variablerate.html
 
J

JLatham

Assuming the amount to calculate commission on is in A1:
=IF(A1>20,0.05 * (A1-20),IF(A1>10,0.025*(A1-10),0))

You don't say what to do for over 30? I presume anything above 20 is
calculated at the 5% of amout over 20.

I guess the thing to notice is that the tests should run to test the largest
value first, then work down to a no-commission value. The first true
condition will be the one that the result is based on.
 
M

Martin Fishlock

Just a word of warning on the table approach it will recalc all the
historical data linked to it if you change the table it may be better to use
a macro that pastes the value in there.

Give me your thaughts.
 
D

Dana DeLouis

0 - 10 ==> no commission
10.1 - 20 ==> 2.5% commission of the amount over and above 10
20.1 - 30 ==> 5% commission of the amount over and above 20
Click to expand...

Hi. Another way...
=MAX(0,(A1-10)/40,(A1-15)/20)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to make "If,then" formulas with THREE outcomes 4
How do I set up a commission calculation chart w/ "if" condition? 2
How Do I DO IF with DATE 2
Commission Calculation with IF Stmt. 2
Multiple If statements, Calculating quarterly % holding costs 0
How do I set up multiple if statements in Excel? 2
sales needed to cover costs that increase as sales do 5
How do I create a commission chart based on dates worked? 0

Top