Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula help
I am trying to figure out a list of formulas and have it all figures except;
I need to be able to add and calculate units by thier commission. Say if someone sells 10 units they have $50 commision on each And if someone sells 11-15 units they have $50 commision on the first 10 units and $75 commision on units 11-15 And if someone sells 16-20 units they have $50 commision on the first 10 units and $75 commision on units 11-15, and $100 commission on unit 16-20 And so on, I need to figure what the commission is on each unit and add them together. An example is 10 units is $500, 15 units is $875, 20 units is $1375. Any help is greatly appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula help
I think of these tiered comp plans a little differently. Instead of saying
$50 each on the first 10, then $75 each for units 11-15, then $100 for units 16-20, I think of it as: $50 per unit, PLUS $25 for every unit above 10, PLUS another $25 for every unit above 15, ... So if the number of units is in A1, I'd calculate the commission as =50*a1 + 25*max(0,a1-10) + 25*max(0,a1-15) + .... The second component, for example, is the $25 that applies for each unit above 10. The (a1-10) calculates the number of units to which this piece applies and the max(0,...) ensures that you never take away commission for not reaching the threshold number of units. HTH. --Bruce "needformulahelp" wrote: I am trying to figure out a list of formulas and have it all figures except; I need to be able to add and calculate units by thier commission. Say if someone sells 10 units they have $50 commision on each And if someone sells 11-15 units they have $50 commision on the first 10 units and $75 commision on units 11-15 And if someone sells 16-20 units they have $50 commision on the first 10 units and $75 commision on units 11-15, and $100 commission on unit 16-20 And so on, I need to figure what the commission is on each unit and add them together. An example is 10 units is $500, 15 units is $875, 20 units is $1375. Any help is greatly appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula help
Take a look he
http://www.mcgimpsey.com/excel/variablerate.html "needformulahelp" wrote: I am trying to figure out a list of formulas and have it all figures except; I need to be able to add and calculate units by thier commission. Say if someone sells 10 units they have $50 commision on each And if someone sells 11-15 units they have $50 commision on the first 10 units and $75 commision on units 11-15 And if someone sells 16-20 units they have $50 commision on the first 10 units and $75 commision on units 11-15, and $100 commission on unit 16-20 And so on, I need to figure what the commission is on each unit and add them together. An example is 10 units is $500, 15 units is $875, 20 units is $1375. Any help is greatly appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula help
Try something like this:
For a unit total in A1 Commission: B1: =SUM((A1{0,10,15,20,25,30,35})*(A1-{0,10,15,20,25,30,35})*({50,25,25,25,25,25,25})) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "needformulahelp" wrote: I am trying to figure out a list of formulas and have it all figures except; I need to be able to add and calculate units by thier commission. Say if someone sells 10 units they have $50 commision on each And if someone sells 11-15 units they have $50 commision on the first 10 units and $75 commision on units 11-15 And if someone sells 16-20 units they have $50 commision on the first 10 units and $75 commision on units 11-15, and $100 commission on unit 16-20 And so on, I need to figure what the commission is on each unit and add them together. An example is 10 units is $500, 15 units is $875, 20 units is $1375. Any help is greatly appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula help
I used this, changed it alittle, and have it working. Now I am being asked to
take the results and filter it by another column. If the results in cell a1 are $1000 and cell a2 is "yes" then column a3 is $1000. If column a2 is not yes then cell a3 should be $500 Any ideas? "bpeltzer" wrote: I think of these tiered comp plans a little differently. Instead of saying $50 each on the first 10, then $75 each for units 11-15, then $100 for units 16-20, I think of it as: $50 per unit, PLUS $25 for every unit above 10, PLUS another $25 for every unit above 15, ... So if the number of units is in A1, I'd calculate the commission as =50*a1 + 25*max(0,a1-10) + 25*max(0,a1-15) + .... The second component, for example, is the $25 that applies for each unit above 10. The (a1-10) calculates the number of units to which this piece applies and the max(0,...) ensures that you never take away commission for not reaching the threshold number of units. HTH. --Bruce "needformulahelp" wrote: I am trying to figure out a list of formulas and have it all figures except; I need to be able to add and calculate units by thier commission. Say if someone sells 10 units they have $50 commision on each And if someone sells 11-15 units they have $50 commision on the first 10 units and $75 commision on units 11-15 And if someone sells 16-20 units they have $50 commision on the first 10 units and $75 commision on units 11-15, and $100 commission on unit 16-20 And so on, I need to figure what the commission is on each unit and add them together. An example is 10 units is $500, 15 units is $875, 20 units is $1375. Any help is greatly appreciated. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula help
I'm not clear how this ties in with the prior question (and I suspect the new
issue is more general than the specific instance you asked about), but this sounds like you just need to use the AND function inside an IF in cell A3: =if(and(a1=1000,a2="yes"),1000,500) "needformulahelp" wrote: I used this, changed it alittle, and have it working. Now I am being asked to take the results and filter it by another column. If the results in cell a1 are $1000 and cell a2 is "yes" then column a3 is $1000. If column a2 is not yes then cell a3 should be $500 Any ideas? "bpeltzer" wrote: I think of these tiered comp plans a little differently. Instead of saying $50 each on the first 10, then $75 each for units 11-15, then $100 for units 16-20, I think of it as: $50 per unit, PLUS $25 for every unit above 10, PLUS another $25 for every unit above 15, ... So if the number of units is in A1, I'd calculate the commission as =50*a1 + 25*max(0,a1-10) + 25*max(0,a1-15) + .... The second component, for example, is the $25 that applies for each unit above 10. The (a1-10) calculates the number of units to which this piece applies and the max(0,...) ensures that you never take away commission for not reaching the threshold number of units. HTH. --Bruce "needformulahelp" wrote: I am trying to figure out a list of formulas and have it all figures except; I need to be able to add and calculate units by thier commission. Say if someone sells 10 units they have $50 commision on each And if someone sells 11-15 units they have $50 commision on the first 10 units and $75 commision on units 11-15 And if someone sells 16-20 units they have $50 commision on the first 10 units and $75 commision on units 11-15, and $100 commission on unit 16-20 And so on, I need to figure what the commission is on each unit and add them together. An example is 10 units is $500, 15 units is $875, 20 units is $1375. Any help is greatly appreciated. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula help
What I have are 2 different cells I would rather have as 1 cell. I have cell
F2 =IF(D2+(E2/2)=10,50*(D2+(E2/2)))+25*MAX(0,(D2+(E2/2)-10))+25*MAX(0,(D2+(E2/2)-15))+25*MAX(0,(D2+(E2/2)-20)) and I have cell J2 =IF(H2="YES",F2,(F2/2)) How can I combine them into 1 cell? "bpeltzer" wrote: I'm not clear how this ties in with the prior question (and I suspect the new issue is more general than the specific instance you asked about), but this sounds like you just need to use the AND function inside an IF in cell A3: =if(and(a1=1000,a2="yes"),1000,500) "needformulahelp" wrote: I used this, changed it alittle, and have it working. Now I am being asked to take the results and filter it by another column. If the results in cell a1 are $1000 and cell a2 is "yes" then column a3 is $1000. If column a2 is not yes then cell a3 should be $500 Any ideas? "bpeltzer" wrote: I think of these tiered comp plans a little differently. Instead of saying $50 each on the first 10, then $75 each for units 11-15, then $100 for units 16-20, I think of it as: $50 per unit, PLUS $25 for every unit above 10, PLUS another $25 for every unit above 15, ... So if the number of units is in A1, I'd calculate the commission as =50*a1 + 25*max(0,a1-10) + 25*max(0,a1-15) + .... The second component, for example, is the $25 that applies for each unit above 10. The (a1-10) calculates the number of units to which this piece applies and the max(0,...) ensures that you never take away commission for not reaching the threshold number of units. HTH. --Bruce "needformulahelp" wrote: I am trying to figure out a list of formulas and have it all figures except; I need to be able to add and calculate units by thier commission. Say if someone sells 10 units they have $50 commision on each And if someone sells 11-15 units they have $50 commision on the first 10 units and $75 commision on units 11-15 And if someone sells 16-20 units they have $50 commision on the first 10 units and $75 commision on units 11-15, and $100 commission on unit 16-20 And so on, I need to figure what the commission is on each unit and add them together. An example is 10 units is $500, 15 units is $875, 20 units is $1375. Any help is greatly appreciated. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula help
In F2:
=IF(D2+(E2/2)=10,50*(D2+(E2/2)))+25*MAX(0,(D2+(E2/2)-10))+25*MAX(0,(D2+(E2/2)-15))+25*MAX(0,(D2+(E2/2)-20)) * (IF(H2="YES",1,1/2)) I just added the multiplication bit at the end to what's already in cell F2. --Bruce "needformulahelp" wrote: What I have are 2 different cells I would rather have as 1 cell. I have cell F2 =IF(D2+(E2/2)=10,50*(D2+(E2/2)))+25*MAX(0,(D2+(E2/2)-10))+25*MAX(0,(D2+(E2/2)-15))+25*MAX(0,(D2+(E2/2)-20)) and I have cell J2 =IF(H2="YES",F2,(F2/2)) How can I combine them into 1 cell? "bpeltzer" wrote: I'm not clear how this ties in with the prior question (and I suspect the new issue is more general than the specific instance you asked about), but this sounds like you just need to use the AND function inside an IF in cell A3: =if(and(a1=1000,a2="yes"),1000,500) "needformulahelp" wrote: I used this, changed it alittle, and have it working. Now I am being asked to take the results and filter it by another column. If the results in cell a1 are $1000 and cell a2 is "yes" then column a3 is $1000. If column a2 is not yes then cell a3 should be $500 Any ideas? "bpeltzer" wrote: I think of these tiered comp plans a little differently. Instead of saying $50 each on the first 10, then $75 each for units 11-15, then $100 for units 16-20, I think of it as: $50 per unit, PLUS $25 for every unit above 10, PLUS another $25 for every unit above 15, ... So if the number of units is in A1, I'd calculate the commission as =50*a1 + 25*max(0,a1-10) + 25*max(0,a1-15) + .... The second component, for example, is the $25 that applies for each unit above 10. The (a1-10) calculates the number of units to which this piece applies and the max(0,...) ensures that you never take away commission for not reaching the threshold number of units. HTH. --Bruce "needformulahelp" wrote: I am trying to figure out a list of formulas and have it all figures except; I need to be able to add and calculate units by thier commission. Say if someone sells 10 units they have $50 commision on each And if someone sells 11-15 units they have $50 commision on the first 10 units and $75 commision on units 11-15 And if someone sells 16-20 units they have $50 commision on the first 10 units and $75 commision on units 11-15, and $100 commission on unit 16-20 And so on, I need to figure what the commission is on each unit and add them together. An example is 10 units is $500, 15 units is $875, 20 units is $1375. Any help is greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |