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]

 

[TenTwenty]

@oldbrewer,
I'm not billed. Go to the store and buy the electricity. Hope that what you buy at the start of the month will be enough to cover the month.... . If you don't buy enough or their was exceptional usage, then you will have two entries for the same month.

 

[oldbrewer]

in the UK that is the most expensive way to pay for electricity - however - if you read your meter at say 9 pm everyday you can get some idea of how much you are using by calculating rolling averages, but are there predictable future times when you expect to use more - will knock up a spreadsheet in the next 5 minutes

 

[oldbrewer]

[TABLE="width: 891"]
<colgroup><col><col><col><col><col><col span="7"></colgroup><tbody>[TR]
[TD]date[/TD]
[TD]meterreading[/TD]
[TD]unitsused[/TD]
[TD]7dayrollave[/TD]
[TD]avedailyconsumption[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28/06/2018[/TD]
[TD="align: right"]3049[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29/06/2018[/TD]
[TD="align: right"]3053[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]4.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/06/2018[/TD]
[TD="align: right"]3054[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]2.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2018[/TD]
[TD="align: right"]3067[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD="align: right"]6.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2018[/TD]
[TD="align: right"]3070[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]5.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/07/2018[/TD]
[TD="align: right"]3082[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD="align: right"]6.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04/07/2018[/TD]
[TD="align: right"]3098[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD="align: right"]8.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/07/2018[/TD]
[TD="align: right"]3101[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7.4[/TD]
[TD="align: right"]7.4[/TD]
[TD][/TD]
[TD="colspan: 5"]in this example you could use as much as 18.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06/07/2018[/TD]
[TD="align: right"]3114[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]8.7[/TD]
[TD="align: right"]8.1[/TD]
[TD][/TD]
[TD="colspan: 2"]or as little as zero[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07/07/2018[/TD]
[TD="align: right"]3122[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9.7[/TD]
[TD="align: right"]8.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08/07/2018[/TD]
[TD="align: right"]3125[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8.3[/TD]
[TD="align: right"]7.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/07/2018[/TD]
[TD="align: right"]3144[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]10.6[/TD]
[TD="align: right"]8.6[/TD]
[TD][/TD]
[TD="colspan: 5"]it is clear that your ave daily consumption is around 8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/07/2018[/TD]
[TD="align: right"]3151[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9.9[/TD]
[TD="align: right"]8.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11/07/2018[/TD]
[TD="align: right"]3151[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.6[/TD]
[TD="align: right"]7.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12/07/2018[/TD]
[TD="align: right"]3161[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]8.6[/TD]
[TD="align: right"]8.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13/07/2018[/TD]
[TD="align: right"]3170[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8.0[/TD]
[TD="align: right"]8.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2018[/TD]
[TD="align: right"]3181[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]8.4[/TD]
[TD="align: right"]8.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15/07/2018[/TD]
[TD="align: right"]3185[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8.6[/TD]
[TD="align: right"]8.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2018[/TD]
[TD="align: right"]3188[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6.3[/TD]
[TD="align: right"]7.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17/07/2018[/TD]
[TD="align: right"]3205[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]7.7[/TD]
[TD="align: right"]8.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18/07/2018[/TD]
[TD="align: right"]3223[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]10.3[/TD]
[TD="align: right"]8.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19/07/2018[/TD]
[TD="align: right"]3235[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10.6[/TD]
[TD="align: right"]8.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20/07/2018[/TD]
[TD="align: right"]3235[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9.3[/TD]
[TD="align: right"]8.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21/07/2018[/TD]
[TD="align: right"]3241[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8.6[/TD]
[TD="align: right"]8.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22/07/2018[/TD]
[TD="align: right"]3243[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8.3[/TD]
[TD="align: right"]8.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23/07/2018[/TD]
[TD="align: right"]3248[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8.6[/TD]
[TD="align: right"]8.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24/07/2018[/TD]
[TD="align: right"]3251[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6.6[/TD]
[TD="align: right"]7.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25/07/2018[/TD]
[TD="align: right"]3253[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4.3[/TD]
[TD="align: right"]7.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26/07/2018[/TD]
[TD="align: right"]3266[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]4.4[/TD]
[TD="align: right"]7.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27/07/2018[/TD]
[TD="align: right"]3281[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]6.6[/TD]
[TD="align: right"]8.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28/07/2018[/TD]
[TD="align: right"]3292[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]7.3[/TD]
[TD="align: right"]8.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29/07/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/07/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/07/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[TenTwenty]

@oldbrewer,
Jip, I know (everything to screw us over in the good SA)

I guess I know that during holidays I can already see the spike as well as during winter. That is nice and it sort of reflect as it should. I have to enter it by hand though and would like to automate it. Sometimes I miss it by three or some days and other months there is an excess of three or more days. Overall I'm fine, I guess it is just the part of doing everything by hand. It absolutely kills me.

 

[oldbrewer]

if you miss a day's reading put in the average daily consumption it will auto correct when you put in a true reading

I am signing out for now will restart tomorrow

 

[TenTwenty]

@oldbrewer

Thanks. I really do appreciate this.

 

[shg]

Are you trying to calculate your electric bill based on tiered pricing? And if so, can you explain the rate plan?

 

[TenTwenty]

@shg

Hi there,
Yes, I am.

Here are the rules
Resets at the start of each month.
50 units @ 0.9743
300 units @ 1.2312
250 units @ 1.8500
100 units @ 2.1818
Rest @ 2.2500

Ex. 700 units bought on 1/8/18
50 to the first tier (650 left)
300 to the second tier (350 left)
250 to the third tier (100 left)
100 to the fourth tier (0 left)

Buy another 300 units on the 20th of the same month
0 to the first tier (300 left) - already maxed out at the first purchase
0 to the second tier (300 left) - already maxed out at the first purchase
0 to the third tier (300 left) - already maxed out at the first purchase
300 to the fourth tier (0 left)

I try to buy on a monthly basis. Not that rough to calculate. Problem is when I have two or more entries for the same month.

Hope this help.

 

[shg]

Does any unused amount roll over to the following month?

 

[TenTwenty]

@shg
No rollover to the next month. It starts afresh each month.

PS. Sorry for the delay