Gesyca_is_joy
Board Regular
- Joined
- Apr 24, 2014
- Messages
- 90
- Office Version
- 365
- Platform
- Windows
I have a table in which I constantly add rows. I have a column in which i want to dynamically calculate the average of the past XX months.
Right now I am using the following formula in Column C for a 5 month average.
=Average(Offset(A1,COUNT(A2:A11),0,-5)
However it's not quite what I want because what i am looking for is a formula that calcuates the average for the previous 5 rows based on each cell...so row 6 would average rows 1-5, and row 7 would average rows 2-6 and so on.
<tbody>
</tbody>
Right now I am using the following formula in Column C for a 5 month average.
=Average(Offset(A1,COUNT(A2:A11),0,-5)
However it's not quite what I want because what i am looking for is a formula that calcuates the average for the previous 5 rows based on each cell...so row 6 would average rows 1-5, and row 7 would average rows 2-6 and so on.
A | B | C |
Month | Number | Average |
1 | 15 | |
2 | 19 | |
3 | 18 | |
4 | 17 | |
5 | 16 | |
6 | 15 | |
7 | 18 | |
8 | 19 | |
9 | 21 | |
10 | 22 |
<tbody>
</tbody>