Excel 2007 : sales commission formula

**grace43** · 11-17-2009, 02:17 AM

Hi,

My problem is similar to ShinyMagics:
http://www.excelforum.com/excel-gene...n-formula.html

My boss just gave me this spreadsheet and wants it done pronto. I may have given him the impression my excel skills were better than they actually are

Here is what I need to calculate:

Base salary paid
Up to 2.5 times base salary made in sales- 3%
Another 100000 on top of that is 20%
anything over that is 35%

So en example:
Base Salary 50000
up to 125000 earns 3% (3750)
125001 to 225000 earns 20% (20000)
over 225001 earns 35% (8750)
So commission earnt is $32,500 on top of $50,000.

I used the formula DonkeyOte posted in the thread I mentioned above. I made it work for my figures, but only when I typed numbers, not added formulas instead.

How do I create a formula so that my boss can enter a different base salary and hypothetical sales amount and calculate the commission earnt?

PLEASE HELP!

P.S Sorry for posting my thread into a previous thread - in my desperation to solve the problem - I missed reading the rules! Thanks.

**DonkeyOte** · 11-17-2009, 03:32 AM

If we assume

A1: base salary
B1: =0
C1: =B1*2.5
D1: =C1+100000

Then if we want to make rates variable also let's put those in row below, ie

B2: =3%
C2: =20%
D2: =35%

To keep things simple in the calc itself let's then use the row below to calculate the incremental movement in each tier

B3: =B2-A2
copied across to D3

Now let's assume sales amount is entered into A5 and commission is to be calculated in B5

B5: =SUMPRODUCT(--($A5>$B$1:$D$1),$A5-$B$1:$D$1,$B$3:$D$3)

Your boss should then find by changing value(s) in A1/B2:D2/A5 so the output in B5 will adjust accordingly.

**grace43** · 11-17-2009, 04:12 PM

Hi DonkeyOte,

Thank you so much for helping me so quickly!
I copied your instructions into excel and double checked them.
I got a result of $72,500 (base salary + commissions).
When I did the sum on my calculator, I got $82,500.
I'm not sure how I got the different result - I double checked everything.
Example attached.