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))))

 

[awoohaw]

Okay, the title says youre asking for an average formula. But here is a way to count blank or zero value cells in a range of numbers where the start and end points are varied. But it uses XMATCH and I do not know if that is in Excel 2019.

Mr Excel Questions.xlsx
	A	B	C
3		18
4		25
5		15
6
7		2
8		10
9		0
10
11		4
12		19
13
14		22
15
16	Count Zero and Blank in the range:	4
17	(Non Numbers)
Counting Zero and Blank Cells

Cell Formulas
Range	Formula
B16	B16	=COUNT(INDEX(B3:B14,XMATCH(0,(--(ISNUMBER(B3:B14))),0)):INDEX(B3:B14,XMATCH(0,(--(ISNUMBER(B3:B14))),0,-1))) -COUNTIFS(INDEX(B3:B14,XMATCH(0,(--(ISNUMBER(B3:B14))),0)):INDEX(B3:B14,XMATCH(0,(--(ISNUMBER(B3:B14))),0,-1)),0)

 

[vladimiratanasiu]

Okay, the title says youre asking for an average formula. But here is a way to count blank or zero value cells in a range of numbers where the start and end points are varied. But it uses XMATCH and I do not know if that is in Excel 2019.

Mr Excel Questions.xlsx
	A	B	C
3		18
4		25
5		15
6
7		2
8		10
9		0
10
11		4
12		19
13
14		22
15
16	Count Zero and Blank in the range:	4
17	(Non Numbers)
Counting Zero and Blank Cells

Cell Formulas
Range	Formula
B16	B16	=COUNT(INDEX(B3:B14,XMATCH(0,(--(ISNUMBER(B3:B14))),0)):INDEX(B3:B14,XMATCH(0,(--(ISNUMBER(B3:B14))),0,-1))) -COUNTIFS(INDEX(B3:B14,XMATCH(0,(--(ISNUMBER(B3:B14))),0)):INDEX(B3:B14,XMATCH(0,(--(ISNUMBER(B3:B14))),0,-1)),0)

Indeed, I can't use your formula because of XMATCH function and it doesn't view the average. On the other hand, it points all zero / blank cells. However, it should count in my case not 4, but only 3 cells, from the range B3:B14. And I need to be taken into consideration the value ranges of year 2023 from my table (B74:B85, C74:C85), solving the mentioned issue.

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	5.32	3.60
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	=AVERAGE(--B74:INDEX(B$1:$B85,LOOKUP(99^99,B$1:$C85,ROW(B$1:$C85))))
C87	C87	=AVERAGE(--C74:INDEX($C$1:C85,LOOKUP(99^99,$C$1:C85,ROW($C$1:C85))))

 

[awoohaw]

3? Rows 6,10,13 are blank and Row 9 is zero, why is 4 incorrect?

regarding the calculations for years and such. I really am still confused with your request. You write averages, then counts. My solution provides a count, and if you want to exclude the zeros, just take the last half out of the formula I provided. As far as the year summaries... the formula above would be repeated after each year in whatever calculation that it is used in. You can get the range of values in that formula to do averages and sums.

 

[James006]

Sorry for not explaining clearly the row element ...
The row element you are already using is the Lookup function ...
So in cell B87, you can test
=AVERAGE(--B74:INDEX(B1:B86,SUMPRODUCT(MAX((ROW(A1:A86))*(A1:A86<=TODAY())))))

 

[vladimiratanasiu]

The respective formula is incorrect, because I mentioned as accountable the range between the first and the last non-zero values. In the example of awoohaw is available only the range B3:B14, the blank cell B15 is outside it.
I applied your formula in the cells B87 and C87, changing also the PC time. In these conditions, it works very well. However, when I tried to use it in the cells B68 and C68, it gives the wrong values 31,92 and 21,63, instead of 38,3 and 25,95 as they are in my previous table.

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	31.92	21.63
69		31.92	21.63
70
71
72
73
74	Jan-23	55.00	50.00
75	Feb-23
76	Mar-23	50.00	45.00
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	105.00	95.00
87	Average power consumption /month	35.00
88		8.75	7.92
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	B68	=AVERAGE(--B55:INDEX(B1:B67,SUMPRODUCT(MAX((ROW(A1:A67))*(A1:A67<=TODAY())))))
C68	C68	=AVERAGE(--C55:INDEX(C1:C67,SUMPRODUCT(MAX((ROW(A1:A67))*(A1:A67<=TODAY())))))
B69:C69,B88:C88	B69	=B67/12
B87	B87	=AVERAGE(--B74:INDEX(B1:B86,SUMPRODUCT(MAX((ROW(A1:A86))*(A1:A86<=TODAY())))))

 

[vladimiratanasiu]

3? Rows 6,10,13 are blank and Row 9 is zero, why is 4 incorrect?

regarding the calculations for years and such. I really am still confused with your request. You write averages, then counts. My solution provides a count, and if you want to exclude the zeros, just take the last half out of the formula I provided. As far as the year summaries... the formula above would be repeated after each year in whatever calculation that it is used in. You can get the range of values in that formula to do averages and sums.

I posted by mystake a part of the answer for you in that for James 006. The respective formula is incorrect, because I mentioned as accountable the range between the first and the last non-zero values. In your example is available only the range B3:B14, the blank cell B15 is outside it. So, the formula must point only 3 blank / zero cells. Regarding the count, I didn't mention it as the final result / formula, but as a condition to be taken in consideration, necessary to identify the number of eligible months when structuring the average. And the average must generate the final desired result.

 

[vladimiratanasiu]

Sorry for not explaining clearly the row element ...
The row element you are already using is the Lookup function ...
So in cell B87, you can test
=AVERAGE(--B74:INDEX(B1:B86,SUMPRODUCT(MAX((ROW(A1:A86))*(A1:A86<=TODAY())))))

Sorry, but I posted by mistake in the body of the last answer for you a part of the message addressed to another member. Please consider only its content that points your formula. Thank you!

 

[awoohaw]

I posted by mystake a part of the answer for you in that for James 006. The respective formula is incorrect, because I mentioned as accountable the range between the first and the last non-zero values. In your example is available only the range B3:B14, the blank cell B15 is outside it. So, the formula must point only 3 blank / zero cells. Regarding the count, I didn't mention it as the final result / formula, but as a condition to be taken in consideration, necessary to identify the number of eligible months when structuring the average. And the average must generate the final desired result.

I dont understand why you are considering my row 15 in these discussions. I did not use that in any of my formulas. I did however find another error in my work when the DEC value is zero or blank. I will be doing my debugging with your data. Please tell me in plain language if you want an average for the year for the months that have values. Or do you always want the average to be divided by 12.

 

[vladimiratanasiu]

I dont understand why you are considering my row 15 in these discussions. I did not use that in any of my formulas. I did however find another error in my work when the DEC value is zero or blank. I will be doing my debugging with your data. Please tell me in plain language if you want an average for the year for the months that have values. Or do you always want the average to be divided by 12.

1. "3? Rows 6,10,13 are blank and Row 9 is zero, why is 4 incorrect?" I answered specifically to this question, where you mentioned clearly the fourth non-zero row / cell. 2. As you see, I wrote specifically in more of my previous messages that the average formula should address the range between the first non-zero to the last non-zero cells, covering them and those blank / with zero values too, from that range. This fact was pointed also in the results of my initial formulas (383/10 months - they are counted also those without values = 38,3). If you notice, in my original table there are the cells B69 and C69. They have formulas, shown under the table, that divide the total to 12 months with clear results, but are totally distinct from the yellow ones that I am interested for (B87, C87 vs. B68, C68).

 

[awoohaw]

1. "3? Rows 6,10,13 are blank and Row 9 is zero, why is 4 incorrect?" I answered specifically to this question, where you mentioned clearly the fourth non-zero row / cell. 2. As you see, I wrote specifically in more of my previous messages that the average formula should address the range between the first non-zero to the last non-zero cells, covering them and those blank / with zero values too, from that range. This fact was pointed also in the results of my initial formulas (383/10 months - they are counted also those without values = 38,3). If you notice, in my original table there are the cells B69 and C69. They have formulas, shown under the table, that divide the total to 12 months with clear results, but are totally distinct from the yellow ones that I am interested for (B87, C87 vs. B68, C68).

I'm sorry, I do not understand what your requirement is.

I was under the impression you wanted averages or counts for the 12 months in each yearly section.
If that is the case the only thing I want to know is if you want to include months with blank/zero amounts in the denominator.