Google Sheets ArrayFormula - Avarage values of predecessor rows

Question

I have a question about using ArrayFormula again. I have a list of 2 columns (datetime and currency). I need to find the average of the currency-column for the last 50 rows. I can't use formulas in every row because the length of the array can vary.

I already tried with a query (knowing it wouldn't work with ArrayFormula):

=ArrayFormula(query(U2:V; "select AVG(V) where (U > date '"&TEXT(WORKDAY($U139:U;-50); "YYYY-MM-DD")&"' AND U < date '"&TEXT($U139:U; "YYYY-MM-DD")&"') label AVG(V) ''"))

My next step was to trick it with indirect- adressing but this also doesn't work:

=ArrayFormula(IF($U2:$U>=$C$32;AVERAGE(INDIRECT("$V"&ROW()):INDIRECT("$V"&ROW()-50));""))

Here is an example sheet with both formulas: Avarage 50 example

Any ideas how to solve this issues?

this works for 1 row. I need this in every row for the 50 predecessor rows without copying the formula... — Me.MyBase
There is not a good way to do this for more than about 2500 rows. I can show you the answer for that many if you're interested, but if you'll have longer time series than that, you may not be interested? — MattKing
Although my solution is not that elegant listing all 50 pairs of ranges, it will work as a single cell ARRAYFORMULA. Where the start date is 24/06/1998 17:30:00, it will average 50 days rolling, working on 5790 rows of data. — Aresvik

MattKing MattKing · Accepted Answer · 2021-05-03T17:22:57

MMULT()'s can be used to create conditional averages or sums like this, but they are limited to datsets under 2500 or so rows as they will virtually create a matrix that is N rows long, by N columns wide, where N is the length of your dataset.

N^2 has to be less than about 5 million as that's the "cell limit" in google sheets.

You'll find this formula in cell F2 on a new tab called MK.Help:

=ARRAYFORMULA(IF((E2:E="")+(ROW(D2:D)<50+1);;MMULT((ROW(D2:D)<=TRANSPOSE(ROW(D2:D)+50))*(ROW(D2:D)>=TRANSPOSE(ROW(D2:D)));N(E2:E))/50))

Google Sheets ArrayFormula - Avarage values of predecessor rows

2 Answers