Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I combine multiple IF statements to come up with 1 value?
I am trying to create an automated commission calculation sheet for my sales
team, however I can't find how to create the formula. What I'd like to achieve is the following: revenue: 0 - 10 == no commission 10.1 - 20 == 2.5% commission of the amount over and above 10 20.1 - 30 == 5% commission of the amount over and above 20 etc. The formula I created now double counts the amount of revenue and that is not the intention. Thank you for your help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I combine multiple IF statements to come up with 1 value?
=IF(A1<=10,0,IF(A1<=20,2.5,IF(A1<=30,5,....
Regards Trevor "Johanna" wrote in message ... I am trying to create an automated commission calculation sheet for my sales team, however I can't find how to create the formula. What I'd like to achieve is the following: revenue: 0 - 10 == no commission 10.1 - 20 == 2.5% commission of the amount over and above 10 20.1 - 30 == 5% commission of the amount over and above 20 etc. The formula I created now double counts the amount of revenue and that is not the intention. Thank you for your help! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I combine multiple IF statements to come up with 1 value?
Set up a table
treshold marginal rate differential rate 10000 0.025 0.025 20000 0.05 0.025 30000 0.075 0.025 40000 0.1 0.025 50000 0.125 0.025 use the formula =SUMPRODUCT(--(D5$A$4:$A$8), (D5-$A$4:$A$8), $C$4:$C$8) where D5 holds the amount sold A4 to A8 is the tresholds C4 to C8 are the incremental %'ges you can change the borders and or the rates in the table without having to change formula's for a detailed explanation go to http://www.mcgimpsey.com/excel/variablerate.html -- Greetings from New Zealand "Johanna" wrote in message ... I am trying to create an automated commission calculation sheet for my sales team, however I can't find how to create the formula. What I'd like to achieve is the following: revenue: 0 - 10 == no commission 10.1 - 20 == 2.5% commission of the amount over and above 10 20.1 - 30 == 5% commission of the amount over and above 20 etc. The formula I created now double counts the amount of revenue and that is not the intention. Thank you for your help! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I combine multiple IF statements to come up with 1 value?
Assuming the amount to calculate commission on is in A1:
=IF(A120,0.05 * (A1-20),IF(A110,0.025*(A1-10),0)) You don't say what to do for over 30? I presume anything above 20 is calculated at the 5% of amout over 20. I guess the thing to notice is that the tests should run to test the largest value first, then work down to a no-commission value. The first true condition will be the one that the result is based on. "Johanna" wrote: I am trying to create an automated commission calculation sheet for my sales team, however I can't find how to create the formula. What I'd like to achieve is the following: revenue: 0 - 10 == no commission 10.1 - 20 == 2.5% commission of the amount over and above 10 20.1 - 30 == 5% commission of the amount over and above 20 etc. The formula I created now double counts the amount of revenue and that is not the intention. Thank you for your help! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I combine multiple IF statements to come up with 1 valu
Just a word of warning on the table approach it will recalc all the
historical data linked to it if you change the table it may be better to use a macro that pastes the value in there. Give me your thaughts. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Bill Kuunders" wrote: Set up a table treshold marginal rate differential rate 10000 0.025 0.025 20000 0.05 0.025 30000 0.075 0.025 40000 0.1 0.025 50000 0.125 0.025 use the formula =SUMPRODUCT(--(D5$A$4:$A$8), (D5-$A$4:$A$8), $C$4:$C$8) where D5 holds the amount sold A4 to A8 is the tresholds C4 to C8 are the incremental %'ges you can change the borders and or the rates in the table without having to change formula's for a detailed explanation go to http://www.mcgimpsey.com/excel/variablerate.html -- Greetings from New Zealand "Johanna" wrote in message ... I am trying to create an automated commission calculation sheet for my sales team, however I can't find how to create the formula. What I'd like to achieve is the following: revenue: 0 - 10 == no commission 10.1 - 20 == 2.5% commission of the amount over and above 10 20.1 - 30 == 5% commission of the amount over and above 20 etc. The formula I created now double counts the amount of revenue and that is not the intention. Thank you for your help! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I combine multiple IF statements to come up with 1 value?
=LOOKUP(A1,{0,10.1,20.1},{0,2.5%,5%})
"Johanna" wrote: I am trying to create an automated commission calculation sheet for my sales team, however I can't find how to create the formula. What I'd like to achieve is the following: revenue: 0 - 10 == no commission 10.1 - 20 == 2.5% commission of the amount over and above 10 20.1 - 30 == 5% commission of the amount over and above 20 etc. The formula I created now double counts the amount of revenue and that is not the intention. Thank you for your help! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I combine multiple IF statements to come up with 1 value?
0 - 10 == no commission
10.1 - 20 == 2.5% commission of the amount over and above 10 20.1 - 30 == 5% commission of the amount over and above 20 Hi. Another way... =MAX(0,(A1-10)/40,(A1-15)/20) -- HTH :) Dana DeLouis Windows XP & Office 2007 "Johanna" wrote in message ... I am trying to create an automated commission calculation sheet for my sales team, however I can't find how to create the formula. What I'd like to achieve is the following: revenue: 0 - 10 == no commission 10.1 - 20 == 2.5% commission of the amount over and above 10 20.1 - 30 == 5% commission of the amount over and above 20 etc. The formula I created now double counts the amount of revenue and that is not the intention. Thank you for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine Intersect Range in If statements | Excel Discussion (Misc queries) | |||
Multiple if statements with multiple conditions | Excel Discussion (Misc queries) | |||
Combine multiple workbooks into 1 workbook w/ multiple worksheets | Excel Discussion (Misc queries) | |||
Combine multiple workbooks into 1 workbook w/ multiple worksheets | Excel Discussion (Misc queries) | |||
multiple if statements | Excel Worksheet Functions |