offset formula for 12 month moving average?

[jw01]

Hi all

Having an issue with a formula for a 12 month moving average. THe average should move based on cell selection in D5 (month's).

I tried the following formula in cell B18:

Please Login or Register  to view this content.

basically, the answer I am getting with above formula is 114. The 12 month average based on Dec 2017, should be 122 (average column J:U).

If i change the cell in D5 to Nov, I want the formula to average column I:T etc. When I get data for Jan/2018, I would like for it to average column K:V for each row etc.

Can someone help?

[samba_ravi]

B18=average(offset($d18,,match($b$3&$b$5,index($d$16:$aa$16&$d$17:$aa$17,0),0)-1,,-min(match($b$3&$b$5,index($d$16:$aa$16&$d$17:$aa$17,0),0),12)))
try this and copy towards down

[jw01]

works perfect...thank you