0

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.

2 Answers 2

1

Delete everything from F2:F, leaving only your header.

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)."

1

@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.

 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.