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?

band	baskets	rate
1	0	$0.00
2	10	$1.15
3	20	$2.30
4	30	$3.15
5	40	$4.45

<tbody>
</tbody>

 

[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.

band	baskets	rate
1	0	$0.00
2	10	$1.15
3	20	$2.30
4	30	$3.15
5	40	$4.45

<tbody>
</tbody>

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)

Band	From	to	Rate	Earnings
1	0	49	£0
2	50	99	£1
3	100	149	£2
4	150	199	£3
5	200	249	£4
			Total	£

<tbody>
</tbody>

 

[shg]

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

225.

	A​	B​	C​	D​
1​	Qty​	Rate​	Delta​	​
2​	0​	0​	0​	C2: =B2-N(B1)
3​	50​	1​	1​
4​	100​	2​	1​
5​	150​	3​	1​
6​	200​	4​	1​
7​
8​
9​	Qty​	Earnings​
10​	50​	0​		B10: =SUMPRODUCT((A10 > $A$2:$A$6) * (A10 - $A$2:$A$6) * $C$2:$C$6)
11​	75​	25​
12​	100​	50​
13​	125​	100​
14​	150​	150​
15​	175​	225​
16​	200​	300​
17​	225​	400​
18​	250​	500​

 

[pauljleonard]

Wow!!!!

I learned

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

Thanks for the help and speedy response.

Very impressed.