Calculate a rolling average over columns

[OmegaSea]

Please see the attached example. Each row represents weekly unit sold for a specific SKU we sell (not in the example). I need to get a rolling 12 week average every time a new week is not 0.

In this example =AVERAGE(J4:U4) results in 932 which is great but when column V is updated with a values > 0 then we need the average to roll to columns K thru V for each row. This needs to occur for each of the 52 weeks.


Tried incorporating OFFSET in the AVERAGE calculation but can't get this to work. HELP!!!!

[daffodil11]

=AVERAGE(OFFSET(A4:L4,0,MAX(IF(($U$4:$AZ$16<>0),COLUMN($U$4:$AZ$16)))-11))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Tsjallie]

This should do the trick

Please Login or Register  to view this content.

Confirm with [ctrl][shift][enter]

[JohnTopley]

I believe previous formula should be:

=AVERAGE(OFFSET(A4:L4,0,MAX(IF(($U$4:$AZ$16<>0),COLUMN($U$4:$AZ$16)))-12))

This gives same results as your sample.

[daffodil11]

You know, I tried 12 and didn't get the result I was aiming for and changed it to 11.

Maybe I'm just crazies today. I blame the stones.

Also, change the U4's to L4's.

[OmegaSea]

Never ever would I have figured this out! Works GREAT. Thank you so much!!!!