Dynamic formula to calculate progressive difference between values from the same period of two years

[vladimiratanasiu]

Hello!

I have a large table with the consumption of electricity in 50 points over many years. I need a dynamic solution to calculate automatically the annual difference (e.g. 5 Kwh, -155 Kwh) between a period of one year (e.g. January-June 2021; January-August 2022) with the same period of the previous year (January-June 2020; January-August 2021). It should take into consideration the last numerical value added progressively, excluding only the non-value cells. How could I do it?

Thank you in advance!

Book1.xlsx
	B	C	D	E	F	G	H	I
2	Period	Electricity consumption (Kwh)	Period	Electricity consumption (Kwh)	Period	Electricity consumption (Kwh)	Period	Electricity consumption (Kwh)
3	Jan-20	180	Jan-21	185	Jan-21	185	Jan-22	160
4	Feb-20	170	Feb-21	175	Feb-21	175	Feb-22	155
5	Mar-20	120	Mar-21	130	Mar-21	130	Mar-22	127
6	Apr-20	100	Apr-21	90	Apr-21	90	Apr-22	92
7	May-20	80	May-21	80	May-21	80	May-22	61
8	Jun-20	60	Jun-21	55	Jun-21	55	Jun-22	0
9	Jul-20	50	Jul-21	34	Jul-21	34	Jul-22	0
10	Aug-20	40	Aug-21	42	Aug-21	42	Aug-22	41
11	Sep-20	60	Sep-21	55	Sep-21	55	Sep-22
12	Oct-20	80	Oct-21	76	Oct-21	76	Oct-22
13	Nov-20	100	Nov-21	109	Nov-21	109	Nov-22
14	Dec-20	140	Dec-21	134	Dec-21	134	Dec-22
15			Annual difference (+/-)	5	Annual difference (+/-)			-155
Sheet1

Cell Formulas
Range	Formula
E15	E15	=SUM(E3:E8)-SUM(C3:C8)
I15	I15	=SUM(I3:I10)-SUM(G3:G10)

 

[ExceLoki]

from how i took your request, i believe this is could work for what you're looking for. please let me know if so, or what changes may be needed.
---------------------------

Book1
	A	B	C	D	E	F	G	H	I
1
2		Period	Electricity consumption (Kwh)	Period	Electricity consumption (Kwh)	Period	Electricity consumption (Kwh)	Period	Electricity consumption (Kwh)
3		1/1/2020	180	1/1/2021	185	1/1/2021	185	1/1/2022	160
4		2/1/2020	170	2/1/2021	175	2/1/2021	175	2/1/2022	155
5		3/1/2020	120	3/1/2021	130	3/1/2021	130	3/1/2022	127
6		4/1/2020	100	4/1/2021	90	4/1/2021	90	4/1/2022	92
7		5/1/2020	80	5/1/2021	80	5/1/2021	80	5/1/2022	61
8		6/1/2020	60	6/1/2021	55	6/1/2021	55	6/1/2022	0
9		7/1/2020	50	7/1/2021	34	7/1/2021	34	7/1/2022	0
10		8/1/2020	40	8/1/2021	42	8/1/2021	42	8/1/2022	41
11		9/1/2020	60	9/1/2021	55	9/1/2021	55	9/1/2022
12		10/1/2020	80	10/1/2021	76	10/1/2021	76	10/1/2022
13		11/1/2020	100	11/1/2021	109	11/1/2021	109	11/1/2022
14		12/1/2020	140	12/1/2021	134	12/1/2021	134	12/1/2022
15				Annual difference (+/-)	5	Annual difference (+/-)			-155
Sheet1

Cell Formulas
Range	Formula
E15,I15	E15	=IF(YEAR(D14)=YEAR(TODAY()),SUM(OFFSET(E3,0,0,COUNTA(INDIRECT(ADDRESS(3,MATCH(160,3:3,0))&":"&ADDRESS(3+11,MATCH(160,3:3,0))))))-SUM(OFFSET(C3,0,0,COUNTA(INDIRECT(ADDRESS(3,MATCH(160,3:3,0))&":"&ADDRESS(3+11,MATCH(160,3:3,0)))))),SUM(OFFSET(E3,0,0,COUNTIF(INDIRECT(ADDRESS(3,MATCH(160,3:3,0))&":"&ADDRESS(3+11,MATCH(160,3:3,0))),">0")))-SUM(OFFSET(C3,0,0,COUNTIF(INDIRECT(ADDRESS(3,MATCH(160,3:3,0))&":"&ADDRESS(3+11,MATCH(160,3:3,0))),">0"))))

 

[vladimiratanasiu]

from how i took your request, i believe this is could work for what you're looking for. please let me know if so, or what changes may be needed.
---------------------------

Book1
	A	B	C	D	E	F	G	H	I
1
2		Period	Electricity consumption (Kwh)	Period	Electricity consumption (Kwh)	Period	Electricity consumption (Kwh)	Period	Electricity consumption (Kwh)
3		1/1/2020	180	1/1/2021	185	1/1/2021	185	1/1/2022	160
4		2/1/2020	170	2/1/2021	175	2/1/2021	175	2/1/2022	155
5		3/1/2020	120	3/1/2021	130	3/1/2021	130	3/1/2022	127
6		4/1/2020	100	4/1/2021	90	4/1/2021	90	4/1/2022	92
7		5/1/2020	80	5/1/2021	80	5/1/2021	80	5/1/2022	61
8		6/1/2020	60	6/1/2021	55	6/1/2021	55	6/1/2022	0
9		7/1/2020	50	7/1/2021	34	7/1/2021	34	7/1/2022	0
10		8/1/2020	40	8/1/2021	42	8/1/2021	42	8/1/2022	41
11		9/1/2020	60	9/1/2021	55	9/1/2021	55	9/1/2022
12		10/1/2020	80	10/1/2021	76	10/1/2021	76	10/1/2022
13		11/1/2020	100	11/1/2021	109	11/1/2021	109	11/1/2022
14		12/1/2020	140	12/1/2021	134	12/1/2021	134	12/1/2022
15				Annual difference (+/-)	5	Annual difference (+/-)			-155
Sheet1

Cell Formulas
Range	Formula
E15,I15	E15	=IF(YEAR(D14)=YEAR(TODAY()),SUM(OFFSET(E3,0,0,COUNTA(INDIRECT(ADDRESS(3,MATCH(160,3:3,0))&":"&ADDRESS(3+11,MATCH(160,3:3,0))))))-SUM(OFFSET(C3,0,0,COUNTA(INDIRECT(ADDRESS(3,MATCH(160,3:3,0))&":"&ADDRESS(3+11,MATCH(160,3:3,0)))))),SUM(OFFSET(E3,0,0,COUNTIF(INDIRECT(ADDRESS(3,MATCH(160,3:3,0))&":"&ADDRESS(3+11,MATCH(160,3:3,0))),">0")))-SUM(OFFSET(C3,0,0,COUNTIF(INDIRECT(ADDRESS(3,MATCH(160,3:3,0))&":"&ADDRESS(3+11,MATCH(160,3:3,0))),">0"))))

Thank you for the quick answer! I noticed your formula uses many times the value 160, referring to the electricity consumption from January 2022. Unfortunately, it works properly using only that value, otherwise it generates errors / false results as cells colored in yellow from my last attached table. Anyway, considering the formula could be ok and taking into account the numerical data are extended continuously, I should change manually that value many times. Could you make all necessary changes, so that the existing errors are removed and avoid manual changes when adding some new years? Thank you!

Book3
	B	C	D	E	F	G	H	I
2	Period	Electricity consumption (Kwh)	Period	Electricity consumption (Kwh)	Period	Electricity consumption (Kwh)	Period	Electricity consumption (Kwh)
3	Jan-20	180	Jan-21	185	Jan-21	185	Jan-22	170
4	Feb-20	170	Feb-21	175	Feb-21	175	Feb-22	155
5	Mar-20	120	Mar-21	130	Mar-21	130	Mar-22	127
6	Apr-20	100	Apr-21	90	Apr-21	90	Apr-22	92
7	May-20	80	May-21	80	May-21	80	May-22	61
8	Jun-20	60	Jun-21	55	Jun-21	55	Jun-22	0
9	Jul-20	50	Jul-21	34	Jul-21	34	Jul-22	0
10	Aug-20	40	Aug-21	42	Aug-21	42	Aug-22	41
11	Sep-20	60	Sep-21	55	Sep-21	55	Sep-22
12	Oct-20	80	Oct-21	76	Oct-21	76	Oct-22
13	Nov-20	100	Nov-21	109	Nov-21	109	Nov-22
14	Dec-20	140	Dec-21	134	Dec-21	134	Dec-22
15			Annual difference (+/-)	#N/A	Annual difference (+/-)			-15
Sheet1

Cell Formulas
Range	Formula
E15,I15	E15	=IF(YEAR(D14)=YEAR(TODAY()),SUM(OFFSET(E3,0,0,COUNTA(INDIRECT(ADDRESS(3,MATCH(160,3:3,0))&":"&ADDRESS(3+11,MATCH(160,3:3,0))))))-SUM(OFFSET(C3,0,0,COUNTA(INDIRECT(ADDRESS(3,MATCH(160,3:3,0))&":"&ADDRESS(3+11,MATCH(160,3:3,0)))))),SUM(OFFSET(E3,0,0,COUNTIF(INDIRECT(ADDRESS(3,MATCH(160,3:3,0))&":"&ADDRESS(3+11,MATCH(160,3:3,0))),">0")))-SUM(OFFSET(C3,0,0,COUNTIF(INDIRECT(ADDRESS(3,MATCH(160,3:3,0))&":"&ADDRESS(3+11,MATCH(160,3:3,0))),">0"))))

 

[ExceLoki]

oops, i had that while working on it, but thought i had changed it. give me a min...

 

[ExceLoki]

try this:

Dynamic formula to calculate progressive difference between values from the same period of two years.xlsx
	A	B	C	D	E	F	G	H	I
1
2		Period	Electricity consumption (Kwh)	Period	Electricity consumption (Kwh)	Period	Electricity consumption (Kwh)	Period	Electricity consumption (Kwh)
3		1/1/2020	180	1/1/2021	185	1/1/2021	185	1/1/2022	160
4		2/1/2020	170	2/1/2021	175	2/1/2021	175	2/1/2022	155
5		3/1/2020	120	3/1/2021	130	3/1/2021	130	3/1/2022	127
6		4/1/2020	100	4/1/2021	90	4/1/2021	90	4/1/2022	92
7		5/1/2020	80	5/1/2021	80	5/1/2021	80	5/1/2022	61
8		6/1/2020	60	6/1/2021	55	6/1/2021	55	6/1/2022	0
9		7/1/2020	50	7/1/2021	34	7/1/2021	34	7/1/2022	0
10		8/1/2020	40	8/1/2021	42	8/1/2021	42	8/1/2022	41
11		9/1/2020	60	9/1/2021	55	9/1/2021	55	9/1/2022
12		10/1/2020	80	10/1/2021	76	10/1/2021	76	10/1/2022
13		11/1/2020	100	11/1/2021	109	11/1/2021	109	11/1/2022
14		12/1/2020	140	12/1/2021	134	12/1/2021	134	12/1/2022
15				Annual difference (+/-)	5	Annual difference (+/-)			-155
Sheet1

Cell Formulas
Range	Formula
E15,I15	E15	=IF(YEAR(D14)=YEAR(TODAY()),SUM(OFFSET(E3,0,0,COUNTA(INDIRECT(ADDRESS(3,MATCH((LOOKUP(2,1/(3:3<>""),3:3)),3:3,0))&":"&ADDRESS(3+11,MATCH((LOOKUP(2,1/(3:3<>""),3:3)),3:3,0))))))-SUM(OFFSET(C3,0,0,COUNTA(INDIRECT(ADDRESS(3,MATCH((LOOKUP(2,1/(3:3<>""),3:3)),3:3,0))&":"&ADDRESS(3+11,MATCH((LOOKUP(2,1/(3:3<>""),3:3)),3:3,0)))))),SUM(OFFSET(E3,0,0,COUNTIF(INDIRECT(ADDRESS(3,MATCH((LOOKUP(2,1/(3:3<>""),3:3)),3:3,0))&":"&ADDRESS(3+11,MATCH((LOOKUP(2,1/(3:3<>""),3:3)),3:3,0))),">0")))-SUM(OFFSET(C3,0,0,COUNTIF(INDIRECT(ADDRESS(3,MATCH((LOOKUP(2,1/(3:3<>""),3:3)),3:3,0))&":"&ADDRESS(3+11,MATCH((LOOKUP(2,1/(3:3<>""),3:3)),3:3,0))),">0"))))

 

[Leo Skywalker]

Try this:

E15

=SUM(E3:E14)-SUM(C3:INDEX(C3:C14,COUNT(E3:E14)))

Copy to I15

In E15, this will calculate the difference until December 2021, since all months are already populated.

 

[vladimiratanasiu]

Try this:

E15

=SUM(E3:E14)-SUM(C3:INDEX(C3:C14,COUNT(E3:E14)))

Copy to I15

In E15, this will calculate the difference until December 2021, since all months are already populated.

Thank you very much, it's amaizing!

 

[vladimiratanasiu]

Try this:

E15

=SUM(E3:E14)-SUM(C3:INDEX(C3:C14,COUNT(E3:E14)))

Copy to I15

In E15, this will calculate the difference until December 2021, since all months are already populated.

I have a similar issue (Divide the sum of values from the same periods of two consecutive years), but it approaches the proportion between the total values of two consecutive years. The tables are structured vertically, including within the columns also blank and numerical value cells . Could you see this topic and, if possible, find a proper formula to solve the problem? Thank you!