Help Required with Formula for Sales Comissions Please!

[aston]

Hi,

I wondered if anyone would be kind enough to help me please? I am getting very stressed trying to work out how to do this! I am trying to devise a spreadsheet in Excel for a sales consultant in my team. He has a sales threshold of £16,875 before he makes any commission. Once he hits this figure, I want him to be paid different percentages for his billings in excess of £16,875 based on the following bandings.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Anything in excess of £16,875 and up to £20,250 10%
Anything in excess of £20,250 and up to £21,937.50 15%
Anything in excess of £21,937.50 20%<o></o>
Is there a formula that can do this?<o></o>
Thank you very much for your help.<o></o>
Aston

 

[VoG]

With sales in A1 try

=A1*LOOKUP(A1,{0,16875,20251,21937.5},{0,0.1,0.15,0.2})

 

[aston]

Thank you very much for coming back to me so quickly Peter. This looks good, but there's a slight problem here. To explain, if I put in sales of £17,875, the figure comes out as £1,787.50, which is obviously 10% of this figure. However, I'm wanting it to calculate 10% of the sales in excess of the threshold of £16,875 . i.e. if the sales are £17,875, I need it to calculate 10% of £1,000.

Thanks again and hope to hear from you.

 

[VoG]

Perhaps

=(A1-16875)*LOOKUP(A1,{0,16875,20251,21937.5},{0,0.1,0.15,0.2})

 

[barry houdini]

What should the commission be if A1 is 21,000?

Is it 20,250-16,875 = 3,375 @ 10% + 21,000-20,250 = 750 @ 15%

=337.5+112.5 = 450?

If so try this formula

=SUMPRODUCT((A1>{0,16875,20250,21937.5})+0,A1-{0,16875,20250,21937.5},{0,0.1,0.05,0.05})

 

[aston]

Thanks again Peter. I think it's nearly there and I'm starting to feel much better now! However, there's still a slight problem I'm afraid. See, I need the differenet percentages only to apply to the specific bandings, and not everything over £16,875.

So, 10% would only apply to everything over £16,875 and up to £20,250.

15% would only apply to any amount over £20,250 and up to £21,937.50

20% would only apply to any amount over £21,937.50.

I find it hard to exaplain, but when he gets to the higher bandings, I don't want the higher percentages to backdate all the way back to £16,875. The percentages only need to apply to the amounts within the individual bandings.

Thanks again and hopefully, you will be able to make my week by solving this!

 

[barry houdini]

My suggestion should give you the required results, I think. It follows the method shown here

....or a different approach to give the same result

=A1*20%-MIN(21937.5,A1)*(20%-15%)-MIN(20250,A1)*(15%-10%)-MIN(16875,A1)*10%

 

[sen_edp]

hello there

pls try this formula



=SUMPRODUCT(--(A1>{16875,20250,21937.5}),(A1-{16875,20250,21937.5});{0.10,0.05,0.05})

 

[aston]

Hi,

I just want to thank you all as I've been trying to sort this all week! Barry, as your idea came first, I tried that and it worked. Thank you! I feel like I can get on with my life now!
Final question if that's ok... Is there a way with your formala, where instead of stating the amounts i.e. 16875 or 20250 etc, I can refer to amounts in pecific cells? this will make it easier for me to manipulate my data.

Thanks so much

 

[barry houdini]

If you look at the link the suggested formula there is like this

=SUMPRODUCT(--(A1>$J$2:$J$5), (A1-$J$2:$J$5), $L$2:$L$5)

where J2:J5 contains the thresholds for each band and L2:L5 contains the differential rates, i.e. the difference between each rate and the previous