Dynamic formula to calculate automatically progressive differences between same periods of two years

[vladimiratanasiu]

Hello!

I have the table Utilities ( Utilities.xlsx ), that shows the energy consumption of a small company. The table includes also monthly and annual values (cubic meters and KWh) of the natural gas. They are fix and progressive differences at the end of each sub-table, comparing power consumption (+/-%) between two consecutive years. Fix differences address medium consumption of total quantity from a moment of the year, distributed equally / 12 months; whereas the progressive ones aim at medium consumption of the same quantity, but distributed equally over all months, from the first to current / last registered one only (e.g. Jan-Sep 2022, Jan-Jul 2023 etc.). I need a formula to calculate automatically (+/-%) the progressive difference between same periods of two consecutive years (e.g. cells C79, C99, C119), comparing them dynamically when new monthly values are added in the most recent year.

Thank you!

 

[Leo Skywalker]

In C39:

=-(1-(SUM(C24:C35)/SUM(C4:OFFSET(C4,0,0,COUNT(C24:C35)))))

And then copy to all the progressive annual differences.

Another option, avoiding the use of OFFSET, which is volatile:

=-(1-(SUM(C24:C35)/SUM(C4:INDEX(C4:C15,COUNT(C24:C35)))))

This question seems to be related to the thread:

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

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

www.mrexcel.com

Duplicate question?

 

[vladimiratanasiu]

=-(1-(SUM(C24:C35)/SUM(C4:INDEX(C4:C15,COUNT(C24:C35)))))

Thank you very much, it fits exactly with what I need!

 

[vladimiratanasiu]

In C39:

=-(1-(SUM(C24:C35)/SUM(C4:OFFSET(C4,0,0,COUNT(C24:C35)))))

And then copy to all the progressive annual differences.

Another option, avoiding the use of OFFSET, which is volatile:

=-(1-(SUM(C24:C35)/SUM(C4:INDEX(C4:C15,COUNT(C24:C35)))))

This question seems to be related to the thread:

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

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

www.mrexcel.com

Duplicate question?

No. It is one of my previous message with the same topic, but the structure of its attached table is totally different. So, the requested solution was a different one, too.