Excel Commission formula?

Question

I'm trying to create a formula in excel using the following factors. I was hoping you could help. I'm trying to calculate the commissions based on the following tiered commissions:

Sales of $362,000

0-250,000 25%
250,001-500,000 27%
> 500,001 30%

The first $250,000 is calculated at 25%, the next $162,000 is calculated at 27%.

Answer 1

In your example, I think you've done your sums wrong. Surely you mean the next $112,000 is at 27%? Because $250k + $162k = $412k, not your example of $362k.

Anyway, here's the formula, but follow my instructions after it:

=IF(A1>500000,((A1-500000)
*0.3)+((250000*0.25)+(250000
*0.27)),IF(A1>250000,((A1-
250000)*0.27)+(250000*0.25),
(A1*0.25)))

1. This is assuming that you want to calculate the commission of the salary in cell A1: If not, you should change the reference accordingly)

2. I've had to split the formula up several times, because Yahoo hides long formulae when submitted (see the formulae from the other answerers!)

So there shouldn't be any spaces in the above formula (Copy/Paste into Excel, then remove the carriage returns), but it still seems to work with them still in it.

This will take too long to explain in detail, but basically the formula is checking the size of the salary before applying your rates and skipping the higher rates if the salary doesn't fit into the bracket.

If it's a list, then just drag the formula down.

If your commissions change, then there is a cleaner way to calculate commissions based on a dynamic commission rate in a reference table, but for what you asked for this should suffice.

Hope that helps.

Answer 2

If commssion calculation is repeated operation for you

create a table with minimum values of commission ie 0 ,250001 , 500001 in column 1 ; in next column you put your related commission ie a better way 0.25 , 0.27 , 0.30

select the first column 3 cells with valuses 0,250001, and 50000 rclick right mouse and name the range sales

like wise name next column with .25 , .27 and .30 as rate

now in your data area where you want to have your calculated commission give the formula (lookup( A1(ie the range where your sales figure lies),sales ,rate)

then copy this no the column just next to sales figures where you want your commission to display

thats it
good luck

Answer 3

=IF(A1<=250000, A1*0.25, 62500 + IF(A1<=500000, (A1-250000)*0.27, 130000 + (A1-500000) * 0.3))

And unless you intended the first tier to end at $200000 (which would require a change in the formula), its not the next $162K, it's the next $112K calculated at 27%.

Answer 4

Try using this formula you can change it as far as you want to
=IF(A1<=250000,(A1*25/100),(250000*25/100)+((A1-250000)*27/100))

Answer 5

Put any value in cell A1 In cell B1 paste this =IF( A1>40000, A1+A1*10%, IF( A1>20000, A1+A1*7.5%, A1+A1*5%)) Enjoy my profile, I am the VBAXLMan here

Answer 6

Let us do it parametically:
B1= 0 B2=250001 b3=500001
c1= 25% c2=37% c3=30%
d1= (b2-1)*c1 d2=(B3-B2)*C2
e2=362,000
The calculated commission is :
IF(E2
D1 and D2 are calculated to simplify the formulae for the total commission