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.