Proportional | cumulative sum of bonus

D

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
 
R

rylo

HI

In the range A1:A8 enter the numbers
0,20000,30000,40000,50000,60000,70000,80000

B1: 0
B2: =(A2-A1)*C1+B1
Copy from B2 to B8
C1:C8 - enter the percentages 0%, 2.5%...17.5%
E1: the amount being examined: 45,000

Formula:
=VLOOKUP(E1,A1:B8,2)+(E1-VLOOKUP(E1,A1:A8,1))*VLOOKUP(E1,A1:C8,3)

hth

rylo
 
S

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%
Click to expand...
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)
 
T

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
 
D

Danny Puype

thanks rylo -
simplest solution to understand indeed :)

makes my initial approach with if construction redundant indeed

it keeps me wondering about all those brilliant minds coming up with all these solutions :)
 
Top