How to calculate the running average over the last x days?

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)

...