formula help

[Guest]

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.

 

[Guest]

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

 

[Guest]

Take a look here:

http://www.mcgimpsey.com/excel/variablerate.html

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

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)

 

[Guest]

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?

 

[Guest]

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