Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating mult band sales commission formula
Hi everyone,
I am trying to setup a excel sheet to calculate sales reps commissions, the more they sell the higher the banding becomes. <6500 profit = 5% commission between 6501 & 8000 = 7% between 8001 & 10'000 = 10 10k to 12.5k = 12% 12.5k to 15k = 15% over 15k = 20% Example - if they earn £10'000 - they get 5% first £6500 (£325), 7% of the next £1500 (£134) etc Can anyone help me with a formula that will do this for me? Thanks Richard |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating mult band sales commission formula
John McGimpsey has a web page on this subject:
http://www.mcgimpsey.com/excel/variablerate.html -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- " m wrote in message ... Hi everyone, I am trying to setup a excel sheet to calculate sales reps commissions, the more they sell the higher the banding becomes. <6500 profit = 5% commission between 6501 & 8000 = 7% between 8001 & 10'000 = 10 10k to 12.5k = 12% 12.5k to 15k = 15% over 15k = 20% Example - if they earn £10'000 - they get 5% first £6500 (£325), 7% of the next £1500 (£134) etc Can anyone help me with a formula that will do this for me? Thanks Richard |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating mult band sales commission formula
Hi,
Create the following table by enter the data in cells A1:B6. 0 5% 6500 7% 8000 10% 10000 12% 12500 15% 15000 20% Suppose you want to calculate the rate for 9000 in cell D1, enter the formula =VLOOKUP(D1,$A$1:$B$6,2) in cell E1 or whereever. Note that your example is inconsistant. You show <6500 and 6501 - what happens with 6500? no commission. Since I am not exactly sure which way you want to go you may need to modify the table a litte. For example you many need to change 6500 to 6501 and so on. -- Thanks, Shane Devenshire " wrote: Hi everyone, I am trying to setup a excel sheet to calculate sales reps commissions, the more they sell the higher the banding becomes. <6500 profit = 5% commission between 6501 & 8000 = 7% between 8001 & 10'000 = 10 10k to 12.5k = 12% 12.5k to 15k = 15% over 15k = 20% Example - if they earn £10'000 - they get 5% first £6500 (£325), 7% of the next £1500 (£134) etc Can anyone help me with a formula that will do this for me? Thanks Richard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating sales commission that changes based on a sales volume | Excel Worksheet Functions | |||
Formula for determining sales commission | Excel Worksheet Functions | |||
Sales V commission | Excel Worksheet Functions | |||
Sales Commission calculations | Excel Worksheet Functions | |||
sales commission template | Excel Discussion (Misc queries) |