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>

 

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

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

<colgroup><col><col><col><col><col><col span="7"></colgroup><tbody>
</tbody>

 

[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