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

 

[vladimiratanasiu]

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.

I mentioned that the issue pointed the progressive average, since 2023 (see in my last table cells B87 and C87). I started to use it in 2022 (cells B68 and C68). The result is calculated as mean value of the whole period between the first and the last month with non-zero energy, considering also the non-value / zero value cells from that range as counting cells. In my last table they are ranges B55:B64 / C55:C64 (year 2022) - 10 valid months, and B74:B76 / C74:C76 (year 2023) - 3 valid months. If they are added other new values in the next cells, the average should automatically cover them, too.

 

[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.View attachment 82846

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

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.View attachment 82846

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

I posted this message two weeks ago, but didn't get a proper solution until now. Could you see the answers given and tell me how to overcome this issue? Thank you!

 

[awoohaw]

Well, I don't really understand the requirement as you have written them.
This is my impression:
1 You want a yearly average and sum of energy consumption.
2. The average is to exclude months with no consumption reported.

What other requirement(s) am I missing?

 

[vladimiratanasiu]

Well, I don't really understand the requirement as you have written them.
This is my impression:
1 You want a yearly average and sum of energy consumption.
2. The average is to exclude months with no consumption reported.

What other requirement(s) am I missing?

1. I need an average formula, to take into consideration years starting with 2023. There is already a previous sollution of this issue, available for the year 2022 (see in the last table, cells P68 and Q68), working fine only in that year. When I adjusted it in 2023 (when is no specific consumption values recorded yet), it generates errors (see in the same table, the results from cells P87 and Q87). 2. I wrote to you in a previous answer 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]

Vladimir, i have already written that I did not understand your requirment the way you have repeated.
But, here is another attempt:

WorkBook1.xlsx
	P	Q	R	S
89
90	Vladimir's Requested Average: Looks fr Row4 to Row85.	63.97105263	60.67929
91
Sheet1

Cell Formulas
Range	Formula
Q90	Q90	=SUM(IFERROR(((--ISNUMBER($P$4:$P$85)*($Q$4:$Q$85))>0)*($Q$4:$Q$85),0)) / SUM(--(IFERROR(--ISNUMBER($P$4:$P$85)*($Q$4:$Q$85),0)>0))
R90	R90	=SUM(IFERROR(((--ISNUMBER($P$4:$P$85)*($R$4:$R$85))>0)*($R$4:$R$85),0)) / SUM(--(IFERROR(--ISNUMBER($P$4:$P$85)*($R$4:$R$85),0)>0))