Please enable JavaScript.
Coggle requires JavaScript to display documents.
Excel - Fixed and sliding scales - Chapter 9 - Coggle Diagram
Excel - Fixed and sliding scales - Chapter 9
Fixed scale
Vlookup can be used to create fixed scales. Fixed scales are used in calculations where the lookup value consists of ranges
Sliding scale - Example
Excel table
0
1%
0
0
2
10
2%
1000
1001
3
Cumulative value
Commission
Previous interval ended
Current interval starts
1
30
4%
2000
2001
4
D
C
B
A
70
6%
3000
300
5
Formula for cumulative values
D3 = (B3-B2) x C2 + D2
(1000-0) x 1% + 0 = 10
To perform this calculation in Excel, a lookup database will have to be created and will consist of the following columns:
First column => Lookup range (lookup value). Indicate where the current interval starts
Second column => Indicate where the previous interval stopped
Third column => The commission % (the information you require to do the calculation)
Fourth column => The total (cumulative) commission amount for all the previous intervals
If the lookup vallue is entered in D7:R2500, the formula to calculate the commission would be:
Cumulative value = Vlookup(D7,A2:D5,4,true)
Commission % + Vlookup (D7,A2:D5,3,true)
Remaining portion * (D7 - Vlookup(D7,A2:D5,2,true)
Suppose a dealer pays commission to his sales representatives according to the following terms: (percentage calculated on total sales)
For the first R1000 sales, they receive 1%
For the next R1000 sales, they receive 2%
For the next R1000 sales, they receive 4%
For any further sales, they receive 6%
The dealer will receive a commission of R50 if he sold goods to the value of R2500
For the first R1000 @ 1% = 10
Second R1000 @ 2% = 20
R500 @ 4% = 20
Total commission = 50
Cumulative commission = First and second R1000 = 10 + 20 = 30
Fixed scale - Example
Suppose a dealer pays commission to his sales representatives according to the following terms: (percentage calculated on sales)
1% on sales up to and including R1000
2% on sales between R1000 and (including) R2000
3% on sales between R2000 and (including) R3000
4% for sales exceeding R3000
The dealer will receive commission of R75 if he sold goods to the value of R2500 => R2500 x 3% = R75
To perform this calculation in Excel, a lookup database will have to be created and will consist of the lookup range (lookup value) and Commission % (the information you require to do the calculation).
Excel table
1
Minimum value
Commission
2
0
1%
0 - 1000
A
B
3
1001
2%
1001 - 2000
4
2001
3%
2001 - 3000
5
3001
4%
bigger than or equal to 3000
If the lookup value is entered in D4:R2500 the formula to calculate the commission would be:
=Vlookup(D4,A2:B5,2,true)*D4
Sliding scales
Vlookup can also be used to create sliding scales. Sliding scales are used in calculations where the lookup value lies within a range and the result is the cumulative value for the current and previous range.