Commission Rate Calculator

We will create a chart to calculate the commission earned on the value of a sale

If all the sales were values that appeared on the list then the formula would be simple

C10 is our sales value and C12 through E23 is our table of values

=VLOOKUP(C10,$C$12:$E$23,2)

Finding C10 in the sales values would return a commission (column 2)

This is not nomally the case.

If we entered $45,000 in the sales value, we will first calculate the commission on the amount above our standard number.

=(C10-VLOOKUP(C10, $C$12:$E$23,1))*VLOOKUP(C10, C12:E23, 3)

(We take the value of C10 subtract the sales value lookup rate, column 1)multiplied by commission rate (column 3) $112.50

Adding in the orignial formula

=VLOOKUP(C10, $C$12:$E$23, 2)+(C10-VLOOKUP(C10, $C$12:$E$23,1))*VLOOKUP(C10, $C$12:$E$23,3)

The final step is to allow for sales values less than 5000.

=if(C10<5000, C10*E12, VLOOKUP(C10, $C$12:$E$23, 2)+(C10-VLOOKUP(C10, $C$12:$E$23,1))*VLOOKUP(C10, $C$12:$E$23,3))

Instructions for sliding rate Commissions

MY videos are all created on Camtasia.
TechSmith Camtasia Click Here