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]

1

t1

date

units used

tarriiff

50

t1

01/08/2018

570

t5

300

t2

15/08/2018

280

t1

350

t3

20/08/2018

320

t2

400

t4

25/08/2018

180

t1

450

t5

02/09/2018

49

t1

9999

t5

is this approach close to what you want ?

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

 

[Peter_SSs]

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

Could you show us the table again with the values you want, instead of the values your incorrect formulas are returning?
Also try to explain again, differently, how you manually work out the required results.

Also, there seems to be a problem with the formulas you reported, or at least how you reported them
- You have quoted a formula for cell F2, but that formula refers to cell F2
- You have quoted a formula for cell H2, but that formula refers to cell H2

Can you clarify where/what the formulas actually are?

 

[TenTwenty]

Could you show us the table again with the values you want, instead of the values your incorrect formulas are returning?
Also try to explain again, differently, how you manually work out the required results.

Also, there seems to be a problem with the formulas you reported, or at least how you reported them
- You have quoted a formula for cell F2, but that formula refers to cell F2
- You have quoted a formula for cell H2, but that formula refers to cell H2

Can you clarify where/what the formulas actually are?

Hi,

Columns in red is just for me to quickly check if it my results is correct, they will be deleted afterwards.
For the other, I see a circular reference guess I paste the code while working on it. I got it going up to column E but that is that.

In theory, if I enter a reading in column C, then the formulas in columns E, H K and M must calculate the values. Ex. If I enter 500, then column E can not be more than the value in column F (50) (that is the cap or ceiling). The value that is more will now be passed to column H, thus 450, The ceiling for column H is set by column I which is now 250. H will now only have a value of 250 and the remainder will spill over to column K

The crux is now that if there is another entry for the same month on the next line then the process should continue where the previous entry on the previous line stopped. I hope this help.

 

[Peter_SSs]

The crux is now that if there is another entry for the same month on the next line then the process should continue where the previous entry on the previous line stopped. I hope this help.

Not really, which is why I would like to see exactly what you have and what you expect, particularly where there are multiple rows for the same month.

 

[TenTwenty]

Could you show us the table again with the values you want, instead of the values your incorrect formulas are returning?
Also try to explain again, differently, how you manually work out the required results.

Also, there seems to be a problem with the formulas you reported, or at least how you reported them
- You have quoted a formula for cell F2, but that formula refers to cell F2
- You have quoted a formula for cell H2, but that formula refers to cell H2

Can you clarify where/what the formulas actually are?

Here is a more clean table.

	A	B	C	D	E	F	G	H	I	J	K
1	Date	Month	Reading	Tariff1	Cap1	Tariff2	Cap2	Tariff3	Cap3	Tariff4	Cap4
2	1/7/18	Jul	550	50	50	250	250	250	300	0	9999
3	1/8/18	Aug	570	50	50	250	250	270	300	0	9999
4	15/8/18	Aug	280	0	50	0	250	30	300	250	9999
5	20/8/18	Aug	320	0	50	0	250	0	300	320	9999
6	25/8/18	Aug	180	0	50	0	250	0	300	180	9999
7	1/9/18	Sep	1200	50	50	250	250	300	300	600	9999

<tbody>
</tbody>

 

[TenTwenty]

1

t1

date

units used

tarriiff

50

t1

01/08/2018

570

t5

300

t2

15/08/2018

280

t1

350

t3

20/08/2018

320

t2

400

t4

25/08/2018

180

t1

450

t5

02/09/2018

49

t1

9999

t5

is this approach close to what you want ?

<tbody>
</tbody>

.... not realy .....

 

[oldbrewer]

does a customer pay say 1 dollar for first 50 units, 2 dollars for 51 to 250 units etc

 

[TenTwenty]

@oldbrewer

Yes, I would like it to calculate electricity and costs thereoff that I buy from my utility. I would like to do pivots on usage , costs, high and lows, seasons, etc.

 

[oldbrewer]

are you billed monthly or quarterly ?