Tiered Cost

[elberdo]

Hello, Looking for a formula. If For instance in a given period, total Sales went from 9 to 11. I need Sale #10 to be at 0.5 and sale #11 to be at 0.4. Last month and this month Sales are in M2 and N2 respectively

If for instance, if sales went from 35-42. I need 5 sales at 0.2 and 2 sales at 0.1. But I also want it to return 0's on column E rows 1-3 (since sales 1-34 have already been paid out)

I've tinkered with IF(AND looking at the ranges but no luck.

Range	Cost #	Lower Limit	Upper Limit
0-10	.5	0	10
11-20	.4	11	20
21-30	.3	21	30
31-40	.2	31	40
41-50	.1	41	50

<tbody>
</tbody>

Thanks in advance, very appreciated

 

[Dave Patton]

I posted a formula to calculate monthly results with cumulative totals and tiered rates.
If that is what you require, I could post the example again and you could adapt it.
There is also a YouTube video on the concept.

 

[shg]

	A​	B​	C​	D​
1​	Qty​	Unit Price​	Delta​
2​	0​	0.5​	0.5​	C2: =B2-N(B1)
3​	10​	0.4​	-0.1​
4​	20​	0.3​	-0.1​
5​	30​	0.2​	-0.1​
6​	40​	0.1​	-0.1​
7​
8​	Sale​	Price​
9​	9​	4.5​		B9: =SUMPRODUCT((SUM(A$8:A9) > $A$2:$A$6) * (SUM(A$8:A9) - $A$2:$A$6) * $C$2:$C$6) - SUM(B$8:B8)
10​	2​	0.9​
11​	2​	0.8​
12​	4​	1.6​
13​	6​	2.1​
14​	6​	1.8​
15​	2​	0.5​
16​	3​	0.6​
17​	2​	0.4​