This is why it “works” in E5 and E6.
When the current row number is less than 3, the formula returns #N/A.
Otherwise, the formula returns a moving average as before.
The general form is:
where n is the number of periods to include in each average.
As above, OFFSET returns a range that is passed into the AVERAGE function.
Below you’re able to see this formula in action, wherenis thenamed rangeE2.
Starting at cell C5, OFFSET constructs a range that extends back to previous rows.
This is accomplished by using a height equal to negativen.
One solution is to “cap” the size of the range to the number of data points available.
A nice feature of the OFFSET option is that n can be easily changed.
The attached spreadsheet has workaround formulas for Google Sheets.
OFFSET is handy in formulas that require a dynamic range.
AVERAGE Function
The Excel AVERAGE function calculates the average (arithmetic mean) of supplied numbers.
MIN Function
The Excel MIN function returns the smallest numeric value in the data provided.
The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values.
