Progressive Pricing Formula

[pauljleonard]

Hope you can help!!

I have a progressive pricing formula I need help with, been tearing my hair out

Each band gets individual earnings in steps apart from <10 that gets nothing.

In the below example customer sells 35 baskets, so will reach the 4th band.

Band 1 - Zero
Band 2 - 10 * £1.15 = £11.50
Band 3 - 10 * £2.30 = £23.00
Band 4 - 10 * £3.15 = £31.50
As he only has 5 baskets in Band 4, he only gets 5 * £3.15 = £15.75

Total earnings therefore is £81.75

It may be better to have a formula individually in each row, and sum the row for total earnings?

[TABLE="width: 500"]
<tbody>[TR]
[TD]band[/TD]
[TD]baskets[/TD]
[TD]rate[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10[/TD]
[TD]$1.15[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]20[/TD]
[TD]$2.30[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]30[/TD]
[TD]$3.15[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]40[/TD]
[TD]$4.45[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

You're welcome. It's a very common formula for commissions, invented (or at least popularized) by John McGimpsey: http://www.mcgimpsey.com/excel/variablerate.html

 

[pauljleonard]

I actually looked at that website but you translated it for me!
Thanks again