Calculate values with a maximum ceiling, spilling whats left to the next

[TenTwenty]

I have the following table. Problem is I really am stuck with this one. Columns in red are the ones I'm testing against. The values entered in columns E,H, K and N will be tested against the values in columns F, H, K and N. The latter columns in a ceiling and if the calculated value in E,H, K and N exceed the ceiling then it will spill over to the next. That is working sort of but the problem is when the month are the same for two or more rows.

Below is the table values with the formulas that I use.


[TABLE="width: 1003"]
<tbody>[TR]
[TD]Excel 2016
[TABLE="class: grid, width: 20, align: right"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Month[/TD]
[TD]Reading[/TD]
[TD]Test1[/TD]
[TD]Tarif1[/TD]
[TD]Cap1[/TD]
[TD]Test2[/TD]
[TD]Tarif2[/TD]
[TD]Cap2[/TD]
[TD]Test3[/TD]
[TD]Tarif3[/TD]
[TD]Cap3[/TD]
[TD]Test4[/TD]
[TD]Tarif4[/TD]
[TD]Cap4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/7/18[/TD]
[TD]July[/TD]
[TD]550[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]250[/TD]
[TD]250[/TD]
[TD]250[/TD]
[TD][/TD]
[TD][/TD]
[TD]300[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/8/18[/TD]
[TD]August[/TD]
[TD]570[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]250[/TD]
[TD]270[/TD]
[TD]250[/TD]
[TD][/TD]
[TD][/TD]
[TD]300[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]15/8/18[/TD]
[TD]August[/TD]
[TD]280[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]250[/TD]
[TD][/TD]
[TD][/TD]
[TD]300[/TD]
[TD]250[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]20/8/18[/TD]
[TD]August[/TD]
[TD]320[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]250[/TD]
[TD][/TD]
[TD][/TD]
[TD]300[/TD]
[TD]320[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]25/8/18[/TD]
[TD]August[/TD]
[TD]180[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]250[/TD]
[TD][/TD]
[TD][/TD]
[TD]300[/TD]
[TD]180[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2/9/18[/TD]
[TD]Sept[/TD]
[TD]1200[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]250[/TD]
[TD]900[/TD]
[TD]250[/TD]
[TD][/TD]
[TD][/TD]
[TD]300[/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Formulas
[TABLE="class: grid, width: 40"]
<tbody>[TR]
[TD]B2[/TD]
[TD]=TEXT(A2,"mmmm")[/TD]
[/TR]
[TR]
[TD]F2[/TD]
[TD]=IF(C2>F2,IF(B2=B1,0,F2),C2)[/TD]
[/TR]
[TR]
[TD]H2[/TD]
[TD]=IF(C2>F2+I2,IF(B2=B1,0,IF(280-0-250>I2,IF(B2=B1,0,I2),C2-E2-I2)),IF(H2>0,C2-F2,C2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

so the tarriff is a monthly tarriff and any unused units on your token are used to pay next month ?

 

[TenTwenty]

@oldbrewer
Jip, Those not used rollover to the next month, thankfully. That's why I would like to buy the optimum number of units every month if possible. If you buy to much then you are actually sponsoring the next month with expensive units that you bought the previous month.

 

[oldbrewer]

but this month you will not need to buy so many units....

 

[TenTwenty]

@oldbrewer

..... had to buy yesterday. Bad planning.

I there a way that I can PM to someone?

 

[oldbrewer]

not me - I have had PM removed....

 

[Peter_SSs]

I there a way that I can PM to someone?

No, please refer to #4 of the Forum Rules