Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
figuring commissions based on gross sales
I want to be able to plug in a gross sales number and have a formula spit out
the amount of commission to be paid to that sales person. However, their production is tiered and they get paid a different commision based on the level they are at, for example the plan is as follows: 0-5000 in sales = 20% 5001-10000 in sales = 30% 10001-15000 in sales = 40% 15000 in sales = 50% If a sales person closes 18000 in gross sales they get paid 20% on the first 5000 in sales, 30% on the next 5000 in sales, 40% on the next 5000 in sales and the remaining 3000 in sales is paid at 50%. In this example the sales person will earn $6000. I want to be able to plug in the gross sales figure and have the commission figured for me. thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
figuring commissions based on gross sales
Try something like this:
With a sales amount in A1 This formula returns the commission amount: B1: =SUM((A1{0,5000,10000,15000})*(A1-{0,5000,10000,15000})*({0.2,0.1,0.1,0.1})) If you want a table driven solution: Enter these values in E1:F4.... 0 20% 5000 10% 10000 10% 15000 10% B1: =SUMPRODUCT((A1$E$1:$E$4)*(A1-$E$1:$E$4)*($F$1:$F$4)) Note: Those formulas start with 20% as the base commission and calculate the incremental commissions at each step. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "rjhocker" wrote: I want to be able to plug in a gross sales number and have a formula spit out the amount of commission to be paid to that sales person. However, their production is tiered and they get paid a different commision based on the level they are at, for example the plan is as follows: 0-5000 in sales = 20% 5001-10000 in sales = 30% 10001-15000 in sales = 40% 15000 in sales = 50% If a sales person closes 18000 in gross sales they get paid 20% on the first 5000 in sales, 30% on the next 5000 in sales, 40% on the next 5000 in sales and the remaining 3000 in sales is paid at 50%. In this example the sales person will earn $6000. I want to be able to plug in the gross sales figure and have the commission figured for me. thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to calculate sales tax from total sales | Excel Worksheet Functions | |||
IF Stmt. for cumulative commissions. | Excel Discussion (Misc queries) | |||
I need a function to update sales/expenses based on previous tota. | Excel Worksheet Functions | |||
conditional formating - Highlighting text cells based on sales res | Excel Discussion (Misc queries) | |||
Help with Commission forumlas | Excel Worksheet Functions |