# Google Sheets array formula containing average function to calculate moving average

I have a number of columns which calculate a series of moving averages with formulas such as `=average(E2:E4)` (for a 3 day moving average). I'd like to use array formulas instead because I'm constantly adding data to the list. The problem is that the array I'm using for the average `E1:E3` is in the same direction as the array formula I'd like to create, where the values would be, for example, `E2:E` and `E4:E`.

Example with a 3-day moving average

edit: I'm sorry. I was trying to be too succinct with my question. I would like a more generic formula, because I'm doing not just a 3-day moving average, but a 7 day, 14 day, and a 30 day each in separate columns. I don't really want to have to sum 30 items in a denominator.

Thank you for the responses so far.

Then, if you are in an international locale that uses commas as parameter delineators, place the following formula in F2:

`=ArrayFormula(IF(E2:E="",,(SUMIF(ROW(E2:E),"<="&ROW(E2:E)+1,E2:E)-SUMIF(ROW(E2:E),"<"&ROW(E2:E)-1,E2:E))/ARRAY_CONSTRAIN(ISNUMBER(E:E)+ISNUMBER(E2:E)+ISNUMBER(E3:E),ROWS(E2:E),1)))`

If you are in an international locale that uses semicolons as parameter delineators, use this version in F2 instead:

`=ArrayFormula(IF(E2:E="";;(SUMIF(ROW(E2:E);"<="&ROW(E2:E)+1;E2:E)-SUMIF(ROW(E2:E);"<"&ROW(E2:E)-1;E2:E))/ARRAY_CONSTRAIN(ISNUMBER(E:E)+ISNUMBER(E2:E)+ISNUMBER(E3:E);ROWS(E2:E);1)))`

Essentially, this formula says, "Sum the numbers between one row back and one row forward and then divide by however many of those cells held real numbers (e.g., not text, Boolean or null values)."

@un-programmeur seems from an International locale. Wherever the syntax differs from North America I have provided both.

# Formula

North America (NA)

``````=BYROW(E2:E, LAMBDA(r, IFERROR(AVERAGE(
1/(1/r), OFFSET(r,-1,0), OFFSET(r,1,0)))))
``````

International (INT)

``````=BYROW(E2:E; LAMBDA(r; IFERROR(AVERAGE(
1/(1/r); OFFSET(r;-1;0); OFFSET(r;1;0)))))
``````

# Summary

1. Your 3-day moving average (MA) is calculated by averaging the "data" column for the current, previous, and next rows. The OFFSET function is well well-suited to the task:
NA  `OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])`
INT `OFFSET(cell_reference; offset_rows; offset_columns; [height]; [width])`
2. The BYROW function passes the `data` column row-by-row into a LAMBDA function where the current day's row is stored in the arbitrarily named variable `r`.
3. For each `r`, the LAMBDA's formula averages the data from the
1. Current day: `1/(1/r)` *
* Equivalent to `r` if it's a non-zero number, else triggers an error (handled by IFERROR in #4).
2. Previous day: NA `OFFSET(r,-1,0)`  INT `OFFSET(r;-1;0)`
3. Next day: NA `OFFSET(r,1,0)`  INT `OFFSET(r;1;0)`
4. IFERROR returns an empty value if AVERAGE returns an error.