Average formula issue

[vladimiratanasiu]

Hello!

I posted this message two weeks ago, but didn't get yet a proper solution. That is why, I write it again, hoping this time in an answer.
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 (accounting also the zero consumption months from that range, when dividing) . In our example (cell Q68 - 38,3 kW), progressive average is given by the period January 2022 – October 2022, resulting a total of 383 kW/10 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). I applied a solution given before, but it works properly until 2022 only. In 2023, even if I didn't add yet new values, the cells Q87 and R87 show non-zero results (5,32; 3,6). Which is the right solution in this formula to result 0 in the cells Q87 and R87, when there is not added yet new value in them, and how should I adjust it when add a new similar table in the future (2024, 2025 etc.)?
Thank you!

Book5.xlsx
	P	Q	R
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
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]

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

 

[Fluff]

Duplicate to: Adjust average formula properly when the table is extended

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.