Proportional | cumulative sum of bonus

[Danny Puype]

Hi,
I've come across this message board looking for inspiration to solve following situation:

Suppose you have a salesperson having sold 45000 € -
a cumulative bonus needs to be calculated based on a table, considering the proportional bonus for each scaling :

0 0,0%
20000 2,5%
30000 5,0%
40000 7,5%
50000 10,0%
60000 12,5%
70000 15,0%
80000 17,5%

So in other words:
(2,5% * 10000) + (5% * 10000) + (7,5% * 5000) i.e.
upto 20000 there's no bonus, below 30000, there's 2,5%, below 40000 there's 5% etc. (the vlookup logic)

Beyond the exact figures which are just exemplary, i'm interested in specific functions or vba which could be of help achieving this in a simple way.
I know you could proceed with nested if's in combination with vlookup, but if the scales become large, this becomes heavy if not impossible.

Anyway, I'm happy already to have found this message board with it's quality postings
Thanks in advance for any posted directions

 

[Pete_UK]

Have a look at this site:

http://www.mcgimpsey.com/excel/variablerate.html

which shows how you can implement variable rate commission.

Hope this helps.

Pete

 

[Sheeloo]

Try this
1. Insert a column between the values given below (assuming they are in Col
A & B);

0 0,0%
20000 2,5%
30000 5,0%
40000 7,5%
50000 10,0%
60000 12,5%
70000 15,0%
80000 17,5%

Enter this in B2 and copy down...
=(A2-A1)*C1
You should get the following
0 0.00%
20000 0 2.50%
30000 250 5.00%
40000 750 7.50%
50000 1500 10.00%
60000 2500 12.50%
70000 3750 15.00%
80000 5250 17.50%

2. Now assuming you have the value to calculate the bonus in H1
enter this in G1
=VLOOKUP(H1,A:B,2,TRUE)+(H1-VLOOKUP(H1,A:A,1,TRUE))*VLOOKUP(H1,A:C,3,TRUE)

 

[TomPl]

Sometime I have good ideas, sometimes I have bad ideas. Try this:

Build you lookup table like this:
"A" "B" "C:
Sale Base Increment
0 0 0.000
20000 0 0.025
30000 250 0.050
40000 750 0.075
50000 1500 0.100
60000 2500 0.125
70000 3750 0.150
80000 5250 0.175

Assume the sale amount is in cell "E2", put this formula in cell "E3":

=VLOOKUP(E2,$A$2:$C$9,2,TRUE)+(E2-VLOOKUP(E2,$A$2:$C$9,1,TRUE))*VLOOKUP(E2,$A$2:$C$9,3,TRUE)

That should work.

Tom