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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Range[/TD]
[TD]Cost #[/TD]
[TD]Lower Limit[/TD]
[TD]Upper Limit[/TD]
[/TR]
[TR]
[TD]0-10[/TD]
[TD].5[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]11-20[/TD]
[TD].4[/TD]
[TD]11[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]21-30[/TD]
[TD].3[/TD]
[TD]21[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]31-40[/TD]
[TD].2[/TD]
[TD]31[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]41-50[/TD]
[TD].1[/TD]
[TD]41[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

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]

[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"]

Unit Price​

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

Delta​

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

2​

[/td][td]

0​

[/td][td]

0.5​

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

0.5​

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

3​

[/td][td]

10​

[/td][td]

0.4​

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

-0.1​

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

4​

[/td][td]

20​

[/td][td]

0.3​

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

-0.1​

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

5​

[/td][td]

30​

[/td][td]

0.2​

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

-0.1​

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

6​

[/td][td]

40​

[/td][td]

0.1​

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

-0.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="bgcolor:#F3F3F3"]

Sale​

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

Price​

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

9​

[/td][td]

9​

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

4.5​

[/td][td][/td][td="bgcolor:#CCFFFF"]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)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

2​

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

0.9​

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

11​

[/td][td]

2​

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

0.8​

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

12​

[/td][td]

4​

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

1.6​

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

13​

[/td][td]

6​

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

2.1​

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

14​

[/td][td]

6​

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

1.8​

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

15​

[/td][td]

2​

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

0.5​

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

16​

[/td][td]

3​

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

0.6​

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

17​

[/td][td]

2​

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

0.4​

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