Adjust average formula properly when the table is extended

[vladimiratanasiu]

Hello!

I have a table that shows the electric power data (period, consumption etc.) of a client. At the end of each year, it is calculated the monthly average of consumption and costs. Since 2022, giving the variation of months with / without consumption, it is included also a progressive average, calculated as mean value of the whole period between the first and the last month with non-zero energy. In our example (cell Q68), progressive average is given by the period January 2022 – October 2022, resulting a total of 383 kW/10 months (they are accounted also the zero consumption months). The issue appeared when I added the year 2023 and tried to adjust in the cells Q87 and R87 formulas from cells Q68 and R68, considering the new references (Q74 and R74). Even if I didn’t add yet new values of year 2023, the cells Q87 and R87 show non-zero results. Which is the right solution in this case and how should I adjust it when add a new similar table in the future (2024, 2025 etc.)?

Thank you!

P.S. The XLBB didn't picked up completly data from my table, keeping blank the rows with number of the years. I attach a photo of one section, if necessary to.

Book1
																P	Q	R
1																Electric power
2
3																Period	Kwh	Value (including VAT)
4																Jan-19	78.29	129.00
5																Feb-19	70.72	0.00
6																Mar-19	71.48	125.40
7																Apr-19	64.42	20.40
8																May-19	66.57	0.00
9																Jun-19	64.42	77.97
10																Jul-19	66.57	0.00
11																Aug-19	66.57	95.14
12																Sep-19	66.29	0.00
13																Oct-19	69.79	0.00
14																Nov-19	67.54	73.30
15																Dec-19	69.79	61.67
16																Annual total	822.45	582.88
17																Monthly average	68.54	48.57
20
33																Annual total	650.31	485.50
34																Monthly average	54.19	40.46
37
50																Annual total	523.04	464.67
51																Monthly average	43.59	38.72
54
55																Jan-22	54.00	36.57
56																Feb-22	66.00	44.70
57																Mar-22	78.00	53.04
58																Apr-22	68.00	46.25
59																May-22	-10.00	-7.12
60																Jun-22
61																Jul-22
62																Aug-22	49.00	33.32
63																Sep-22	27.00	18.13
64																Oct-22	51.00	34.62
65																Nov-22
66																Dec-22
67																Annual total	383.00	259.51
68																Monthly average	38.30	25.95
69																	31.92	21.63
73
74																Jan-23
75																Feb-23
76																Mar-23
77																Apr-23
78																May-23
79																Jun-23
80																Jul-23
81																Aug-23
82																Sep-23
83																Oct-23
84																Nov-23
85																Dec-23
86																Annual total	0.00	0.00
87																Monthly average	5.32	3.60
88																	0.00	0.00
Sheet1

Cell Formulas
Range	Formula
Q16:R16,Q86:R86,Q67:R67,Q50:R50,Q33:R33	Q16	=SUM(Q4:Q15)
Q17:R17,Q51:R51,Q34:R34	Q17	=Q16/COUNTA(Q4:Q15)
Q68:R68	Q68	=AVERAGE(--Q55:INDEX(Q1:Q66,LOOKUP(99^99,Q1:Q66,ROW(Q1:Q66))))
Q69:R69,Q88:R88	Q69	=Q67/12
Q87	Q87	=AVERAGE(--Q74:INDEX($Q$1:Q85,LOOKUP(99^99,$Q$1:Q85,ROW($Q$1:Q85))))
R87	R87	=AVERAGE(--R74:INDEX($R$1:R85,LOOKUP(99^99,$R$1:R85,ROW($R$1:R85))))

 

[jdellasala]

If you put your data into individual tables next to each other, the job would be much easier.

Book1
	A	B	C	D	E	F	G
1
2	Period	Kwh	Value (including VAT)		Period	Kwh	Value (including VAT)
3	01/01/2019	78.29	129.00		01/01/2022	54.00	36.57
4	02/01/2019	70.72	0.00		02/01/2022	66.00	44.70
5	03/01/2019	71.48	125.40		03/01/2022	78.00	53.04
6	04/01/2019	64.42	20.40		04/01/2022	68.00	46.25
7	05/01/2019	66.57	0.00		05/01/2022	-10.00	-7.12
8	06/01/2019	64.42	77.97		06/01/2022
9	07/01/2019	66.57	0.00		07/01/2022
10	08/01/2019	66.57	95.14		08/01/2022	49.00	33.32
11	09/01/2019	66.29	0.00		09/01/2022	27.00	18.13
12	10/01/2019	69.79	0.00		10/01/2022	51.00	34.62
13	11/01/2019	67.54	73.30		11/01/2022
14	12/01/2019	69.79	61.67		12/01/2022
15	Annual total	822.45	582.88		Annual total	383.00	259.51
16	Monthly average	68.5375	48.57		Monthly average	47.88	32.44
Sheet1

Cell Formulas
Range	Formula
B15,F15	B15	=SUBTOTAL(109,[Kwh])
C15	C15	=ROUND(SUBTOTAL(109,[Value (including VAT)]),2)
B16	B16	=AVERAGE(Table1[Kwh])
C16	C16	=ROUND(AVERAGE(Table1[Value (including VAT)]),2)
G15	G15	=SUBTOTAL(109,[Value (including VAT)])
F16	F16	=AVERAGE(Table2[Kwh])
G16	G16	=ROUND(AVERAGE(Table2[Value (including VAT)]),2)

I added ROUND to some of the formulas because the column indicated a currency value with VAT in the header, and you'd want that to be accurate.

 

[vladimiratanasiu]

If you put your data into individual tables next to each other, the job would be much easier.

Book1
	A	B	C	D	E	F	G
1
2	Period	Kwh	Value (including VAT)		Period	Kwh	Value (including VAT)
3	01/01/2019	78.29	129.00		01/01/2022	54.00	36.57
4	02/01/2019	70.72	0.00		02/01/2022	66.00	44.70
5	03/01/2019	71.48	125.40		03/01/2022	78.00	53.04
6	04/01/2019	64.42	20.40		04/01/2022	68.00	46.25
7	05/01/2019	66.57	0.00		05/01/2022	-10.00	-7.12
8	06/01/2019	64.42	77.97		06/01/2022
9	07/01/2019	66.57	0.00		07/01/2022
10	08/01/2019	66.57	95.14		08/01/2022	49.00	33.32
11	09/01/2019	66.29	0.00		09/01/2022	27.00	18.13
12	10/01/2019	69.79	0.00		10/01/2022	51.00	34.62
13	11/01/2019	67.54	73.30		11/01/2022
14	12/01/2019	69.79	61.67		12/01/2022
15	Annual total	822.45	582.88		Annual total	383.00	259.51
16	Monthly average	68.5375	48.57		Monthly average	47.88	32.44
Sheet1

Cell Formulas
Range	Formula
B15,F15	B15	=SUBTOTAL(109,[Kwh])
C15	C15	=ROUND(SUBTOTAL(109,[Value (including VAT)]),2)
B16	B16	=AVERAGE(Table1[Kwh])
C16	C16	=ROUND(AVERAGE(Table1[Value (including VAT)]),2)
G15	G15	=SUBTOTAL(109,[Value (including VAT)])
F16	F16	=AVERAGE(Table2[Kwh])
G16	G16	=ROUND(AVERAGE(Table2[Value (including VAT)]),2)

I added ROUND to some of the formulas because the column indicated a currency value with VAT in the header, and you'd want that to be accurate.

If you put your data into individual tables next to each other, the job would be much easier.

Book1
	A	B	C	D	E	F	G
1
2	Period	Kwh	Value (including VAT)		Period	Kwh	Value (including VAT)
3	01/01/2019	78.29	129.00		01/01/2022	54.00	36.57
4	02/01/2019	70.72	0.00		02/01/2022	66.00	44.70
5	03/01/2019	71.48	125.40		03/01/2022	78.00	53.04
6	04/01/2019	64.42	20.40		04/01/2022	68.00	46.25
7	05/01/2019	66.57	0.00		05/01/2022	-10.00	-7.12
8	06/01/2019	64.42	77.97		06/01/2022
9	07/01/2019	66.57	0.00		07/01/2022
10	08/01/2019	66.57	95.14		08/01/2022	49.00	33.32
11	09/01/2019	66.29	0.00		09/01/2022	27.00	18.13
12	10/01/2019	69.79	0.00		10/01/2022	51.00	34.62
13	11/01/2019	67.54	73.30		11/01/2022
14	12/01/2019	69.79	61.67		12/01/2022
15	Annual total	822.45	582.88		Annual total	383.00	259.51
16	Monthly average	68.5375	48.57		Monthly average	47.88	32.44
Sheet1

Cell Formulas
Range	Formula
B15,F15	B15	=SUBTOTAL(109,[Kwh])
C15	C15	=ROUND(SUBTOTAL(109,[Value (including VAT)]),2)
B16	B16	=AVERAGE(Table1[Kwh])
C16	C16	=ROUND(AVERAGE(Table1[Value (including VAT)]),2)
G15	G15	=SUBTOTAL(109,[Value (including VAT)])
F16	F16	=AVERAGE(Table2[Kwh])
G16	G16	=ROUND(AVERAGE(Table2[Value (including VAT)]),2)

I added ROUND to some of the formulas because the column indicated a currency value with VAT in the header, and you'd want that to be accurate.

Thank you for the answer. Unfortunately, the above ranges are parts of a larger table, structured and linked by formulas vertically. So, they can't be splitted and managed separately.

 

[jdellasala]

Bet they can. Pull them into Power Query, manipulate them as need, and then load them to tables. You could even do the Totals and Averages in it, although that does get tricky...

 

[vladimiratanasiu]

Thank you for the answer. Unfortunately, the above ranges are parts of a larger table, structured and linked by formulas vertically. So, they can't be splitted and managed separately.

I belive you, but this is not available in my case. My whole table is larger and structured more complicated, so that Power Query becomes not a very efficient solution.

 

[awoohaw]

I was under the impresson power query is best when dealing with large data groups. But, I have no practical experience with large data and conversions of it.

 

[awoohaw]

are you asking for the months with zero to be excluded in your average calculations?

 

[James006]

Hi,
For your row number, you can test:
SUMPRODUCT(MAX((ROW(A1:A86))*(A1:A86<=TODAY())))

 

[vladimiratanasiu]

Hi,
For your row number, you can test:
SUMPRODUCT(MAX((ROW(A1:A86))*(A1:A86<=TODAY())))

Thank you for the answer! However, I need an average formula and can't understand how to integrate your solution in it to solve my issue. The final result should be zero and it is different in my case.

Book5.xlsx
	A	B	C
1	Energy consumption
2
3	Period	Kwh	Value (including VAT)
4	Jan-19	78.29	129.00
5	Feb-19	70.72	0.00
6	Mar-19	71.48	125.40
7	Apr-19	64.42	20.40
8	May-19	66.57	0.00
9	Jun-19	64.42	77.97
10	Jul-19	66.57	0.00
11	Aug-19	66.57	95.14
12	Sep-19	66.29	0.00
13	Oct-19	69.79	0.00
14	Nov-19	67.54	73.30
15	Dec-19	69.79	61.67
16	Total annual	822.45	582.88
17	Average power consumption /month	68.54	48.57
18
19
20
21	Jan-20	69.79	0.00
22	Feb-20	65.29	103.61
23	Mar-20	56.51	0.00
24	Apr-20	54.27	98.02
25	May-20	56.08	0.00
26	Jun-20	54.27	60.51
27	Jul-20	56.08	0.00
28	Aug-20	56.08	72.24
29	Sep-20	55.07	0.00
30	Oct-20	57.84	71.41
31	Nov-20	55.97	0.00
32	Dec-20	13.06	79.71
33	Total annual	650.31	485.50
34	Average power consumption /month	54.19	40.46
35
36
37
38	Jan-21	67.82	0.00
39	Feb-21	61.25	104.51
40	Mar-21	59.19	0.00
41	Apr-21	48.36	100.60
42	May-21	49.97	0.00
43	Jun-21	48.36	79.73
44	Jul-21	49.97	0.00
45	Aug-21	16.12	80.09
46	Sep-21	67.00	0.00
47	Oct-21	11.00	63.77
48	Nov-21	44.00	35.97
49	Dec-21	0.00	0.00
50	Total annual	523.04	464.67
51	Average power consumption /month	43.59	38.72
52
53
54
55	Jan-22	54.00	36.57
56	Feb-22	66.00	44.70
57	Mar-22	78.00	53.04
58	Apr-22	68.00	46.25
59	May-22	-10.00	-7.12
60	Jun-22
61	Jul-22
62	Aug-22	49.00	33.32
63	Sep-22	27.00	18.13
64	Oct-22	51.00	34.62
65	Nov-22
66	Dec-22
67	Total annual	383.00	259.51
68	Average power consumption /month	38.30	25.95
69		31.92	21.63
70
71
72
73
74	Jan-23
75	Feb-23
76	Mar-23
77	Apr-23
78	May-23
79	Jun-23
80	Jul-23
81	Aug-23
82	Sep-23
83	Oct-23
84	Nov-23
85	Dec-23
86	Total annual	0.00	0.00
87	Average power consumption /month	74.00
88		0.00	0.00
Sheet1

Cell Formulas
Range	Formula
B16:C16,B86:C86,B67:C67,B50:C50,B33:C33	B16	=SUM(B4:B15)
B17:C17,B51:C51,B34:C34	B17	=B16/COUNTA(B4:B15)
B68:C68	B68	=AVERAGE(--B55:INDEX(B1:B66,LOOKUP(99^99,B1:B66,ROW(B1:B66))))
B69:C69,B88:C88	B69	=B67/12
B87	B87	=SUMPRODUCT(MAX((ROW(A1:A86))*(A1:A86<=TODAY())))

 

[vladimiratanasiu]

are you asking for the months with zero to be excluded in your average calculations?

Thank you for response! As I wrote, I need only the zero values / blank cells between first and last non-zero values to be counted.