Continues average of last active 2 year (or any time period)

[ahmetk]

Dear Excel Gurus,

I have a bunch of sales figures for the last 10 year for different product groups. In order to forecast the following years stock and/or sales, we tend to take the average of each product group's last 2 years sales units and volumes. However from time to time we adjust our forecast based on the trend changes.

The answer for my question can easily done manually and that's how we are already doing it however I want to write a formula that continuesly calculates the average for each product group for specific a preriod of time, but counting back from today.

Example; We are in september 2022, the formula shall calculate the average of sale units (in this example for 2 year) between september 2020 and september 2022. When we come to October 2022, the formula shall automatically shift one month and present the value for average sales units between october 2020 and october 2022 and shall continue to do so indefinitly.

Thank you for your valubable time and consideration.

Kind regards.

Ahmet K.

 

[ETAF]

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

Also what version of excel are you using

look at Averageifs() and averageif()
where you could use
> = Date(year(today())-2,month(today()),day(today())) to get back exactly 2 years , but that can be modified depending on the dates in the data
Or > = Date(year(today())-2,month(today()),day(1))
but need to see the data in a sample

 

[ahmetk]

Dear Etaf hi,

Thank you for your kind reply. At the attachment you may find the data sheet that I am working on. I have erased most of the data and just left one coloum due to confidentiallity.

As seen. we have data all the way back to 2014 and the average formula simple averages values above 0, by moving the data range for the last two year we can get the last 2 years average but I want to write a formula that does it automatically every month as explained in my above post.

Kind regards.

Ahmet K.

 

[ETAF]

how about
=AVERAGEIFS(B22:B300,B22:B300,">0",$A$22:$A$300,">="&DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))

i have extended the range to row 300 - so as you add data you wont need to extend the formula

But that goes back exactly 2 years to the day
as you have 1st of the month

you could change to
=AVERAGEIFS(B22:B300,B22:B300,">0",$A$22:$A$300,">="&DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(1)))

your title says any time period - so you could add a cell with a date or 2 dates to then average between those dates
=AVERAGEIFS(B22:B300,B22:B300,">0",$A$22:$A$300,">="&Cell with start date,$A$22:$A$300,"<="&Cell with end date) )

 

[ahmetk]

Dear Etaf hi,

Thank you very much for your kind reply and help.

Best regards

Ahmet K.

 

[ETAF]

you are welcome