Need a formula to calculate Average Sales Commission

[sam1259]

Haven’t had any luck with this. Could someone lead me in the righ
direction.
I am building a spread sheet for my company that can calculate
salespersons
Average Sales Commission based on a Graduated Scale

If a sales person earned $125,000 a year, based on the followin
conditions, what is their average commission rate for the year.

Their first $30,000 is based on 50% of gross revenue
0-$30,000 is 50%

From $30,001 to $60,000 they are paid 65% of gross revenue

From $60,000 to $90,000 they are paid 80% of gross revenue

From $90,000 up they are paid 90% of gross revenue

I am looking for a formula that will calculate what they averaged fo
commission
percentage. Any help with a formula would be greatly appreciated.
Thanks in advance

 

[AlfD]

Hi!

This seems to work:

=IF(A8<30000,A8,30000)*50%+IF(A8-IF(A8<30000,A8,30000)<30000,A8-IF(A8<30000,A8,30000),30000)*65%+IF((A8-IF(A8<30000,A8,30000))-IF(A8-IF(A8<30000,A8,30000)<30000,A8-IF(A8<30000,A8,30000),30000)<30000,(A8-IF(A8<30000,A8,30000))-IF(A8-IF(A8<30000,A8,30000)<30000,A8-IF(A8<30000,A8,30000),30000),30000)*80%+(((A8-IF(A8<30000,A8,30000))-IF(A8-IF(A8<30000,A8,30000)<30000,A8-IF(A8<30000,A8,30000),30000))-IF((A8-IF(A8<30000,A8,30000))-IF(A8-IF(A8<30000,A8,30000)<30000,A8-IF(A8<30000,A8,30000),30000)<30000,(A8-IF(A8<30000,A8,30000))-IF(A8-IF(A8<30000,A8,30000)<30000,A8-IF(A8<30000,A8,30000),30000),30000))*90%

where A8 contains the sales figure.

But I don't like it! It somehow lacks elegance.

In case it is a bit opaque, here it is again but using bands of 30000
29999,29998
instead of regular 30000's in your example.

=IF(A8<30000,A8,30000)*50%+IF(A8-IF(A8<30000,A8,30000)<29999,A8-IF(A8<30000,A8,30000),29999)*65%+IF((A8-IF(A8<30000,A8,30000))-IF(A8-IF(A8<30000,A8,30000)<29999,A8-IF(A8<30000,A8,30000),29999)<29998,(A8-IF(A8<30000,A8,30000))-IF(A8-IF(A8<30000,A8,30000)<29999,A8-IF(A8<30000,A8,30000),29999),29998)*80%+(((A8-IF(A8<30000,A8,30000))-IF(A8-IF(A8<30000,A8,30000)<29999,A8-IF(A8<30000,A8,30000),29999))-IF((A8-IF(A8<30000,A8,30000))-IF(A8-IF(A8<30000,A8,30000)<29999,A8-IF(A8<30000,A8,30000),29999)<29998,(A8-IF(A8<30000,A8,30000))-IF(A8-IF(A8<30000,A8,30000)<29999,A8-IF(A8<30000,A8,30000),29999),29998))*90%

Al

 

[AlfD]

Hi!

I forgot about your request for an average. Don't quite know whic
average you have in mind but it seems likely it is (tota
commission/total sales)*100%.
The horrible formula I gave you calculates the total commission on
given level of sales.
Assume the total sales is in A8: the formula is in B8: put =B8/A8 i
C8 and format as a percentage.

I tried an alternative using a user-defined function and got a shorte
formula.
Welcome to it, if you like! But it only does the same thing. And it i
comparatively simple to set it out step by step in a sequence of cells


Something in my bones tells me there is a much better way: I loo
forward to seeing it.

Al