12-Month Rolling Average with Criteria

[Mix1325]

Hello,

I would like to calculate the 12-month rolling average for each product (Column B) based on the Current Month and Current Year (displayed in cells B1:B2) going backwards - see excel file attached.
For example, if the current month is Jun-20 and I want to calculate the 12-month rolling average for the product Blue, I would calculate the average for the Value assigned to the product Blue only (i.e., exclude the other product in the list) for the past 12 months starting with Jun-20 and going backwards to Jun-19.

Thank you in advance for all your help and support in this matter.

 

[vletm]

Mix1325
It would be ... if You would use dates as dates ... because texts are not dates.
Here one sample.

 

[bosco_yip]

Maybe,

In P7, formula copied down :

=AVERAGE(N(OFFSET(C$5,(MATCH(N7,A$6:A$45,0)+(O7="Blue"))-{0,2,4,6,8,10,12,14,16,18,20,22,24},0)))

Regards

 

[Mix1325]

Mix1325
It would be ... if You would use dates as dates ... because texts are not dates.
Here one sample.

Thank you for the reply!

 

[Mix1325]

Maybe,

In P7, formula copied down :

=AVERAGE(N(OFFSET(C$5,(MATCH(N7,A$6:A$45,0)+(O7="Blue"))-{0,2,4,6,8,10,12,14,16,18,20,22,24},0)))

Regards

Thank you for the reply!