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)
more
more here:
http://excelexperts.com/Excel-Tips-Moving-Averages