Need Help with Rolling 3 month average

Hello everyone... I am a novice Excel user, but do to my work I am trying to teach myself some new tricks.

I have built a spreadsheet that tracks my reps "scores" on specific metrics each month. I need a formula that will give me a rolling average for the last 3 months of the scores and that will preferably update each month I add a new score for the latest 3 months. The trick is that my months go from left to right. So the cells I'm trying to average out are from D4 (January) to O4 (December).

I hope that makes sense, please help if you can. The formula I'm stuck with so far is:

=AVERAGE(OFFSET(D4,,COUNTA(D4:O4)-8,1,-3))

This gives me the last 3 months, but it doesn't automatically update when I add the new month.

Index function?

Hi,

Try something like this:
=AVERAGE(INDEX(4:4;COUNT(4:4));INDEX(4:4;COUNT(4:4)-1);INDEX(4:4;COUNT(4:4)-2))

It will give you an average of three values from cells given by INDEX formula.

Short explanation:
ROW 4:4 - there are your scores
COUNT(4:4) - it counts the number of non-empty cells in 4. row and it gives you the number of the last column which contains a value
INDEX(row,column) - it returns a value of the specified cell

Please notice that you can't have any empty cells between monthly scores; in that case the formula will not work.

I hope it helps :o)

Nick's picture

more