Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Commission formula
I have a bit of a quandry with this one. I have a spreadsheet I need to
calculate commissions with. First the salespeople enter in how many sales they get each day which then totals at the bottom. The way it works for most of these sales is if they get between 1-4 sales, they will get $2 for each sale. Above and beyond 4, they get $5 for each sale. I have the forumla setup like this: =IF(B28<4,B28*2,6+(B28-3)*5) where B28 is the total number of units sold. The problem is I have one product where the commissions are on three tiers: 1-5 units= $7 6-10 units= $10 11+ units= $13 How would I calculate this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Commission formula
http://www.mcgimpsey.com/excel/variablerate.html
-- Kind regards, Niek Otten "brodiemac" wrote in message ... I have a bit of a quandry with this one. I have a spreadsheet I need to calculate commissions with. First the salespeople enter in how many sales they get each day which then totals at the bottom. The way it works for most of these sales is if they get between 1-4 sales, they will get $2 for each sale. Above and beyond 4, they get $5 for each sale. I have the forumla setup like this: =IF(B28<4,B28*2,6+(B28-3)*5) where B28 is the total number of units sold. The problem is I have one product where the commissions are on three tiers: 1-5 units= $7 6-10 units= $10 11+ units= $13 How would I calculate this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Commission formula
I like to restructure the calculation as $7 per unit, PLUS $3 per unit ($10 -
$7) above 5, PLUS $3 per unit ($13 - $10) above 10. Then the formula is =7*b28 + max(0,3*(b28-5)) + max(0,3*(b28-10)). Each MAX ensures that there's no penalty for not reaching that threshold. --Bruce "brodiemac" wrote: I have a bit of a quandry with this one. I have a spreadsheet I need to calculate commissions with. First the salespeople enter in how many sales they get each day which then totals at the bottom. The way it works for most of these sales is if they get between 1-4 sales, they will get $2 for each sale. Above and beyond 4, they get $5 for each sale. I have the forumla setup like this: =IF(B28<4,B28*2,6+(B28-3)*5) where B28 is the total number of units sold. The problem is I have one product where the commissions are on three tiers: 1-5 units= $7 6-10 units= $10 11+ units= $13 How would I calculate this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Commission formula
Hi
One way =A1*7+(MAX(0,A1-5)*3+(MAX(0,(A1-10)*3 Your first formula expressed in the same way would be =A1*2+MAX(0,A1-4)*3 The formula pays the base amount on all sales, then the incremental value on the sales above each incremental step. -- Regards Roger Govier "brodiemac" wrote in message ... I have a bit of a quandry with this one. I have a spreadsheet I need to calculate commissions with. First the salespeople enter in how many sales they get each day which then totals at the bottom. The way it works for most of these sales is if they get between 1-4 sales, they will get $2 for each sale. Above and beyond 4, they get $5 for each sale. I have the forumla setup like this: =IF(B28<4,B28*2,6+(B28-3)*5) where B28 is the total number of units sold. The problem is I have one product where the commissions are on three tiers: 1-5 units= $7 6-10 units= $10 11+ units= $13 How would I calculate this? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Commission formula
Another option:
=MAX(7*A1,10*A1-15,13*A1-45) HTH :) -- Dana DeLouis Win XP & Office 2003 "brodiemac" wrote in message ... I have a bit of a quandry with this one. I have a spreadsheet I need to calculate commissions with. First the salespeople enter in how many sales they get each day which then totals at the bottom. The way it works for most of these sales is if they get between 1-4 sales, they will get $2 for each sale. Above and beyond 4, they get $5 for each sale. I have the forumla setup like this: =IF(B28<4,B28*2,6+(B28-3)*5) where B28 is the total number of units sold. The problem is I have one product where the commissions are on three tiers: 1-5 units= $7 6-10 units= $10 11+ units= $13 How would I calculate this? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Commission formula
So many answers so far. You are all great.
I used the answer that Roger Govier gave since it worked the best for me. Thanks all!!! "Roger Govier" wrote: Hi One way =A1*7+(MAX(0,A1-5)*3+(MAX(0,(A1-10)*3 Your first formula expressed in the same way would be =A1*2+MAX(0,A1-4)*3 The formula pays the base amount on all sales, then the incremental value on the sales above each incremental step. -- Regards Roger Govier "brodiemac" wrote in message ... I have a bit of a quandry with this one. I have a spreadsheet I need to calculate commissions with. First the salespeople enter in how many sales they get each day which then totals at the bottom. The way it works for most of these sales is if they get between 1-4 sales, they will get $2 for each sale. Above and beyond 4, they get $5 for each sale. I have the forumla setup like this: =IF(B28<4,B28*2,6+(B28-3)*5) where B28 is the total number of units sold. The problem is I have one product where the commissions are on three tiers: 1-5 units= $7 6-10 units= $10 11+ units= $13 How would I calculate this? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Commission formula
Hi
Thanks for the feedback. My solution had some extraneous brackets included, and would have resulted in an error if used as posted. It should have read either =A1*7+(MAX(0,A1-5)*3)+(MAX(0,A1-10)*3) or more simply =A1*7+MAX(0,A1-5)*3+MAX(0,A1-10)*3 Bruce's solution was identical to mine, but with the correct number of brackets. Dana also gave the correct result, and requires the fewest typing of brackets, which as you can see, I sometimes get wrong<bg -- Regards Roger Govier "Roger Govier" wrote in message ... Hi One way =A1*7+(MAX(0,A1-5)*3+(MAX(0,(A1-10)*3 Your first formula expressed in the same way would be =A1*2+MAX(0,A1-4)*3 The formula pays the base amount on all sales, then the incremental value on the sales above each incremental step. -- Regards Roger Govier "brodiemac" wrote in message ... I have a bit of a quandry with this one. I have a spreadsheet I need to calculate commissions with. First the salespeople enter in how many sales they get each day which then totals at the bottom. The way it works for most of these sales is if they get between 1-4 sales, they will get $2 for each sale. Above and beyond 4, they get $5 for each sale. I have the forumla setup like this: =IF(B28<4,B28*2,6+(B28-3)*5) where B28 is the total number of units sold. The problem is I have one product where the commissions are on three tiers: 1-5 units= $7 6-10 units= $10 11+ units= $13 How would I calculate this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible? | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
adding row to forumla | Excel Discussion (Misc queries) | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |