How to calculate the running average over the last x days?
Hello,
I have a spreadsheet with a header row and two columns - date (A:A) and measurement (B:B), see example below. The rows are sorted by date but not always sequential - on some dates there is no measure and hence no corresponding row. I use the formula =SUM(B$2:B3)/(A3-A$2) to calculate the running average of measurements in cell C2 and drag the fill handle down to populate the remaining cells in the column. How would I calculate the running average over the last x days?
Date | Measurement | Running average | Running average over last x days? |
2022-09-01 | 6 |
|
|
2022-09-02 | 1 | =SUM(B$2:B3)/(A3-A$2) |
|
2022-09-04 | 8 | =SUM(B$2:B4)/(A4-A$2) |
|
2022-09-05 | 1 | =SUM(B$2:B5)/(A5-A$2) |
|
2022-09-06 | 3 | =SUM(B$2:B6)/(A6-A$2) |
|
... |
|
|
|