Formula issues to find variations when zero is one reference value

[vladimiratanasiu]

Hello!

I have a multiannual table, with monthly consumption (column Q) and cost of the electric power (column R). In columns W and X are calculated variations (+/-) of these indicators for that specific month, vs. the same period of the previous year. In addition, the columns W and X includes at the end of each year the monthly average of consumption and cost (W34:X34; W51:X51 etc.), counted as a fix value (sum of all monthly values/12 months). Since 2022, it’s calculated also as a progressive value (sum of all available monthly values at one moment / number of those months – W68:X68; W87:X87 etc.). For monthly variations I used a formula that works satisfactory, to divide similar periods from two consecutive years and find the variations. However, when it meets a in one month a positive value and no value in the similar month from previous year (e.g. Q79 vs. Q60; Q84 vs. Q65 etc.), it results the zero value (see W79, W84) but real variation is different (see Y79 - + 18800%; Y84 - +4100%). My question is what formula could be used in order to calculate and show these variances correctly, taking into account also the case when is met the zero value? Thank you!

Book1.xlsx
	A	B															Q	R					W	X	Y	Z
1
2	2019
3	Period																Kwh	Cost					Kwh	Cost
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
20	2020
21	Jan-20																69.79	0.00					-10.86%	-19.68%
22	Feb-20																65.29	103.61					-7.68%
23	Mar-20																56.51	0.00					-20.94%	-32.77%
24	Apr-20																54.27	98.02					-15.76%
25	May-20																56.08	0.00					-15.76%	-22.39%
26	Jun-20																54.27	60.51					-15.76%
27	Jul-20																56.08	0.00					-15.76%	-24.07%
28	Aug-20																56.08	72.24					-15.76%
29	Sep-20																55.07	0.00					-16.93%	0.00%
30	Oct-20																57.84	71.41					-17.12%
31	Nov-20																55.97	0.00					-17.13%	-40.94%
32	Dec-20																13.06	79.71					-81.29%
33	Total annual																650.31	485.50
34	Average power consumption / month																54.19	40.46					-20.93%	-16.71%
37	2021
38	Jan-21																67.82	0.00					-2.82%	0.87%
39	Feb-21																61.25	104.51					-6.19%
40	Mar-21																59.19	0.00					4.74%	2.63%
41	Apr-21																48.36	100.60					-10.89%
42	May-21																49.97	0.00					-10.90%	31.76%
43	Jun-21																48.36	79.73					-10.89%
44	Jul-21																49.97	0.00					-10.90%	10.87%
45	Aug-21																16.12	80.09					-71.26%
46	Sep-21																67.00	0.00					21.66%	-10.70%
47	Oct-21																11.00	63.77					-80.98%
48	Nov-21																44.00	35.97					-21.39%	-54.87%
49	Dec-21																0.00	0.00					-100.00%
50	Total annual																523.04	464.67
51	Average power consumption / month																43.59	38.72					-19.57%	-4.29%
54	2022
55	Jan-22																54.00	36.57					-20.38%	-22.24%
56	Feb-22																66.00	44.70					7.76%
57	Mar-22																78.00	53.04					31.78%	-1.30%
58	Apr-22																68.00	46.25					40.61%
59	May-22																-10.00	-7.12					-120.01%	-108.93%
60	Jun-22																0.00	0.00					-100.00%
61	Jul-22																0.00	0.00					-100.00%	-58.40%
62	Aug-22																49.00	33.32					203.97%
63	Sep-22																27.00	18.13					-59.70%	-17.28%
64	Oct-22																51.00	34.62					363.64%
65	Nov-22																0.00	0.00					-100.00%	-100.00%
66	Dec-22																0.00	0.00					0.00%
67	Total annual																383.00	259.51
68	Monthly average	Progressive															31.92	21.63					-26.77%	-44.15%
69		Fix															31.92	21.63					-26.77%	-44.15%
73	2023
74	Jan-23																0.00	0.00					-100.00%	-100.00%
75	Feb-23																0.00	0.00					-100.00%	-100.00%
76	Mar-23																106.00	64.98					35.90%	22.51%
77	Apr-23																96.00	64.67					41.18%	39.83%
78	May-23																46.00	31.72					-560.00%	-545.51%
79	Jun-23																188.00	143.53					0.00%	0.00%	18800.00%	14353.00%
80	Jul-23																0.00	0.00					0.00%	0.00%
81	Aug-23																139.00	94.51					183.67%	183.64%
82	Sep-23																52.00	35.37					92.59%	95.09%
83	Oct-23																0.00	0.00					-100.00%	-100.00%
84	Nov-23																41.00	27.90					0.00%	0.00%	4100.00%	2790.00%
85	Dec-23																0.00	0.00					0.00%	0.00%
86	Total annual																668.00	462.68
87	Monthly average	Progressive															55.67	38.56					74.41%	78.29%
88		Fix															55.67	38.56					74.41%	78.29%
92	2024
93	Jan-24																62.00	42.17					0.00%	0.00%	6200.00%	4217.00%
94	Feb-24																						0.00%	0.00%
95	Mar-24																						-100.00%	-100.00%
96	Apr-24																						-100.00%	-100.00%
97	May-24																						-100.00%	-100.00%
98	Jun-24																						-100.00%	-100.00%
99	Jul-24																						0.00%	0.00%
100	Aug-24																						-100.00%	-100.00%
101	Sep-24																						-100.00%	-100.00%
102	Oct-24																						0.00%	0.00%
103	Nov-24																						-100.00%	-100.00%
104	Dec-24																						0.00%	0.00%
105	Total annual																62.00	42.17
106	Monthly average	Progressive															62.00	42.17					#DIV/0!	#DIV/0!
107		Fix															5.17	3.51					-90.72%	-90.89%
Sheet2

Cell Formulas
Range	Formula
Q16:R16,Q105:R105,Q86:R86,Q67:R67,Q50:R50,Q33:R33	Q16	=SUM(Q4:Q15)
Q17:R17,Q51:R51,Q34:R34	Q17	=Q16/COUNTA(Q4:Q15)
W21:W32,W55:W66,W51:X51,W38:W49,W34:X34	W21	=IFERROR(-(1-Q21/Q4),0)
X21,X23,X25,X27,X29,X31,X55,X57,X59,X61,X63,X65,X38,X40,X42,X44,X46,X48	X21	=IFERROR(-(1-(R21+R22)/(R4+R5)),0)
Q68:R68	Q68	=AVERAGE(--Q55:INDEX(Q1:Q66,LOOKUP(99^99,Q1:Q66,ROW(Q1:Q66))))
Q69:R69,Q107:R107,Q88:R88	Q69	=Q67/12
W68:X68	W68	=-(1-(SUM(Q55:Q66)/SUM(Q38:OFFSET(Q38,SUMPRODUCT(MATCH(1,0/(Q55:Q66<>"")))-1,0))))
W69:X69	W69	=IFERROR(-(1-Q69/Q51),0)
W74:W78,W107:X107,W94:W104,W88:X88,W85,W80:W83	W74	=IFERROR(-(1-Q74/Q55),0)
X74:X78,X94:X104,X85,X80:X83	X74	=IFERROR(-(1-(R74/R55)),0)
R79	R79	=109.53+34
Q87,Q106	Q87	=AVERAGE(--Q74:INDEX($Q$1:Q85,LOOKUP(99^99,$Q$1:Q85,ROW($Q$1:Q85))))
R87	R87	=AVERAGE(--R74:INDEX(R1:R85,LOOKUP(99^99,R1:R85,ROW(R1:R85))))
W87:X87,W106:X106	W87	=-(1-(SUM(Q74:Q85)/SUM(Q55:OFFSET(Q55,SUMPRODUCT(MATCH(1,0/(Q74:Q85<>"")))-1,0))))
R106	R106	=AVERAGE(--R93:INDEX(R1:R104,LOOKUP(99^99,R1:R104,ROW(R1:R104))))