Step based commission formula

Hi

Assuming you have the figure in A1, you could use a LOOKUP function.

=LOOKUP(A1,{0,10000},{0.6,0.7})

Assuming total in A1 you can use this formula to give total commission

=A1*70%-MIN(N(A1),10000)*(70%-60%)

HaHa don't you love this site. Double posts again!!!!

Since you said you wanted it in the same cell...

If your cell with the total commissions is a SUM from other cells, you could try this:

=your_sum_formula*LOOKUP(your_sum_formula,{0,10001},{0.6,0.7})

So, if your commissions formula was =SUM(A1:A10), the new formula would be:
=SUM(A1:A10)*LOOKUP(SUM(A1:A10),{0,10001},{0.6,0.7})

- Moo

Originally Posted by Kevin UK

HaHa don't you love this site. Double posts again!!!!

I deleted one.....

I don't think a LOOKUP approach will work - If I understand correctly then commsion is 60% of the first 10,000 and then 70% of everything after, so if total sales is 20,000 then you don't pay commision at 70% on the whole amount - you pay 10,000 @ 60% and 10,000 @ 70% hence an answer of 13,000........that's what my suggested formula does

Originally Posted by daddylonglegs

...you pay 10,000 @ 60% and 10,000 @ 70% hence an answer of 13,000

Ahh... that makes sense.