Various rates applied to various cumulative balances

[KeywestScott]

I am looking to apply different rates to different balances within a running
total. For example, let's say I want to pay incentives at a rate of .5 for
the first $10, from $10.01 to $49.99 to receive a rate of .55 and anything
above $50 to receive .6. The total for the sales is $100. So, the
calculation would be ($10*.5)+(39.99*.55)+($50*.6). How would I do this in
Excel so that as entries were made, the incentives would be automatically
calculated based on the cumulative total?

 

[Ron Coderre]

Try something like this:

With a value in A1

Commission is
B1: =SUM((A1>{0,10,49.99})*(A1-{0,10,49.99})*{0.5,0.05,0.05})

The way that works is this....
ALL values above zero receive 50%
Values over $10.00, but not larger than $49.99 receive an additional 5%
Values that are $50.00 or larger receive an additional 5%

Using $100
$100*50%=5.0000
$39.99*5%=21.9945
$50.01*5%=30.006
Total: 57.005

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP