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]

I think you have an error.

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

He gets 10 @ 3.15, and then another 5 at the same rate?

 

[pauljleonard]

Yes, he gets 5 @ band 4 because it's in between 30 to 40 i.e. 10 + 10 + 10 + 5

10 £1.15 +
10 £2.30 +
10 £3.15 +
5 £3.15

 

[pauljleonard]

I guess it's both cumulative and progressive?

 

[shg]

10 £1.15 +
10 £2.30 +
10 £3.15 +
5 £3.15

Right. Why are the two bands paid at the same rate?

 

[pauljleonard]

The total baskets is only 35, so they haven't reached band 5 to reach band 5 they'd need to purchase 40 baskets. The remaining 5 baskets is inbetween bands. i.e. 30 to 40 so they only get 5 baskets and that's still in band 4 which is £3.15.

[TABLE="class: cms_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]

If they purchased for example 15 baskets, they'd get £1.15 for the first 10, and for the next 5 they'd also get £1.15. If they purchased 21 baskets, they'd get 10 @ £1.15, and 1 at £2.30 as the remaining amount 1 is in between bands.

Hope that makes sense!

 

[shg]

It doesn't make sense to me, sorry. Maybe it will to someone else.

 

[pauljleonard]

Sorry, my fault, not making myself clear. Lets try it another way.

Table
Band 1 is 0 - 49 (rate of zero)
Band 2 is 50 - 99 (rate of £1.00)
Band 3 is 100 - 149 (rate of £2.00)
Band 4 is 150 - 200 (rate of £3.00)

They purchase 175 baskets. Therefore they achieve the 4th band. Earnings broken down below;

Band 1 50 kegs @ £0.00 per keg = £0
Band 2 50 kegs @ £1.00 per keg = £50
Band 3 50 kegs @ £2.00 per keg = £100
Band 4 25 kegs @ £3.00 per keg = £75
Total 175 kegs

Total earnings is £275 (£0 + £50 + £100 + £75)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Band[/TD]
[TD]From[/TD]
[TD]to[/TD]
[TD]Rate[/TD]
[TD]Earnings[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]49[/TD]
[TD]£0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]50[/TD]
[TD]99[/TD]
[TD]£1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]100[/TD]
[TD]149[/TD]
[TD]£2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]150[/TD]
[TD]199[/TD]
[TD]£3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]200[/TD]
[TD]249[/TD]
[TD]£4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]£[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

Total earnings is £275 (£0 + £50 + £100 + £75)

225.

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Qty​

[/td][td="bgcolor:#F3F3F3"]

Rate​

[/td][td="bgcolor:#F3F3F3"]

Delta​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

0​

[/td][td]

0​

[/td][td="bgcolor:#CCFFCC"]

0​

[/td][td="bgcolor:#CCFFCC"]C2: =B2-N(B1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

50​

[/td][td]

1​

[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

100​

[/td][td]

2​

[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

150​

[/td][td]

3​

[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

200​

[/td][td]

4​

[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td="bgcolor:#F3F3F3"]

Qty​

[/td][td="bgcolor:#F3F3F3"]

Earnings​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

50​

[/td][td="bgcolor:#CCFFFF"]

0​

[/td][td][/td][td="bgcolor:#CCFFFF"]B10: =SUMPRODUCT((A10 > $A$2:$A$6) * (A10 - $A$2:$A$6) * $C$2:$C$6)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

75​

[/td][td="bgcolor:#CCFFFF"]

25​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

100​

[/td][td="bgcolor:#CCFFFF"]

50​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

125​

[/td][td="bgcolor:#CCFFFF"]

100​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

150​

[/td][td="bgcolor:#CCFFFF"]

150​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

175​

[/td][td="bgcolor:#CCFFFF"]

225​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

200​

[/td][td="bgcolor:#CCFFFF"]

300​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

225​

[/td][td="bgcolor:#CCFFFF"]

400​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

250​

[/td][td="bgcolor:#CCFFFF"]

500​

[/td][td][/td][td][/td][/tr]
[/table]

 

[pauljleonard]

Wow!!!!

I learned

1. I cannot count
2. You are a genius!

Thanks for the help and speedy response.

Very impressed.