IF formula not working for commision calculation

[excel_2009]

Hi excel gurus,

I have a bit of a dilemma in regards to an IF formula, here is a breakdown of the issue i am facing:

I have a commission structure:

£0-12,000 = 10%
£12,001 – £24,000 = 15%
£24,001+ = 20%

This is worked out on what we have done in the month – but isn’t as simple as one fixed percentage. Let me explain:

So If I bill £14,000 in one month, I earn 10% on the first £12,000 and 15% on the remaining £2,000. So I’ll get £1,500 commission. Or if I do £25,000 in one month, I’ll earn 10% on the first £12,000 (£1,200) plus 15% on the next 12,000 (£1,800) plus 20% on whatever I have left over £24,000 (£200). So all in all, I will have made £3,200.

The problem I have is that I can’t seem to do multiple “IF” formulas. I thought I had fixed it with =IF((J3*1)<12001,(J3*10%),IF(J3*1)<24001,(J3*15%)) but unfortunately it doesn't work as I cant seem to add a third IF rule.

Please can someone help me? perhaps there's a far more simpler method?

Thank you!

 

[Andrew Poulsom]

Look here:

McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates

 

[excel_2009]

Hi Andrew,

I have attempted to use this function however my figures are wrong, please can you help?

=SUMPRODUCT(--(J3<{12000;24000;24001}),(J3*{0.1;0.15;0.2}))

For £14,000 it is showing as £4,900.00

but it should show £1500 (If I bill £14,000 in one month, I earn 10% on the first £12,000 and 15% on the remaining £2,000.)

Thank you

 

[Andrew Poulsom]

You need differential rates:

=SUMPRODUCT(--(J3<{12000;24000;24001}),(J3*{0.1;0.05;0.05}))

returns 1400.

 

[excel_2009]

Thank you so much Andrew but the answer should be 1500?

 

[lexxie2013]

Alternatively (changing A1 to wherever the 14000 is)

=IF(A1<12001,0.1*A1,IF(A1<24001,12000*0.1+(A1-12000)*0.15,12000*0.1+12000*0.15+(A1-24000)*0.2))

 

[Andrew Poulsom]

Yes, it's:

=SUMPRODUCT(--(J3>{0;12000;24000}),(J3-{0;12000;24000}),{0.1;0.05;0.05})

 

[excel_2009]

Thanks everyone, both methods work a treat