Hi Bob

With Value in A1, enter in B1
=MIN(50000,A1)*50%+MAX(0,A1-50000)*40%+MAX(0,A1-100000)*30%

Regards

Roger Govier


Bob Bedford wrote:
I've a range table for calculating commissions:

0-50000 50%
500001-100000 40%
+100000 30%

Now, I'd like to create a table with automatic calculation like

5000 2500 (as it's 50%)
50000 25000 (also 50%)
75000 35000 (50000 at 50% and 25000 at 40%)
and so on.

How to create this formula ? given an amount, how to calculate the
commission ?

Thanks for help.