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.

Excel 2016 A B C D E F G H I J K L M N O 1 Date Month Reading Test1 Tarif1 Cap1 Test2 Tarif2 Cap2 Test3 Tarif3 Cap3 Test4 Tarif4 Cap4 2 1/7/18 July 550 50 50 50 250 250 250 300 0 3 1/8/18 August 570 50 50 50 250 270 250 300 0 4 15/8/18 August 280 0 0 50 0 0 250 300 250 5 20/8/18 August 320 0 0 50 0 0 250 300 320 6 25/8/18 August 180 0 0 50 0 0 250 300 180 7 2/9/18 Sept 1200 50 50 50 250 900 250 300 600 <tbody> </tbody>

Formulas B2 =TEXT(A2,"mmmm") F2 =IF(C2>F2,IF(B2=B1,0,F2),C2) H2 =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)) <tbody> </tbody>

<tbody>
</tbody>

 

[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