# ArrayFormula to compute running average for groups of rows

Is it possible to write an array formula that calculates the running average of the `Amount` column for each of the `Name` groups in the following sheet?

The rows are sorted by `Name`. In Column C, I have an array formula:

``````(=ArrayFormula(IF(LEN(B2:B),SUMIF(ROW(B2:B),"<="&ROW(B2:B),B2:B)/COUNTIF(ROW(B2:B),"<="&ROW(B2:B)),)))
``````

which calculates a running average of the entire `Amount` column regardless of `Name` group but I would like a formula which would restart the running average each time the `Name` changes. The results of my formula (many thanks to prior answers from AdamL) are in column C and the desired result is shown in column D:

``````NAME    AMOUNT   RUN AVE   DESIRED
Tom          3         3         3
Tom          7         5         5
Tom          8         6         6
Tom          2         5         5
Bill        10         6        10
Bill         0         5         5
``````

For a conditional running average, assuming all entries in A2:A are grouped:

`=ArrayFormula(IFERROR((SUMIF(ROW(A2:A),"<="&ROW(A2:A),B2:B)-HLOOKUP(0,SUMIF(ROW(A2:A),"<"&ROW(A2:A),B2:B),MATCH(A2:A,A2:A,0),0))/(ROW(A2:A)-MATCH(A2:A,A2:A,0)-ROW(A2)+2)))`

Before the update to the newest version of Sheets a number of months ago, it would have generally been advised to use MMULT for these sort of "conditional running total" problems:

`=ArrayFormula(IF(LEN(A2:A),MMULT((ROW(A2:A)>=TRANSPOSE(ROW(A2:A)))*(A2:A=TRANSPOSE(A2:A)),--B2:B)/MMULT((ROW(A2:A)>=TRANSPOSE(ROW(A2:A)))*(A2:A=TRANSPOSE(A2:A)),SIGN(ROW(A2:A))),))`

This solution also has the added benefit that the A2:A column needn't be grouped, nor sorted. However, in the newest version, the MMULT solution will break when the referenced range reaches 3163 rows. It appears to be because the 2D array formed by MMULT will tip over 10 million elements (square root of 10 million = 3162.278...).

The first solution shouldn't suffer this limitation, however it will probably still get very slow when referencing a few thousand rows.

• Wow, that's magic. Thanks so much AdamL...it works like a charm. Nov 19 '14 at 16:37
• Can you explain how the code works? I am trying to solve a similar problem. Feb 8 '19 at 6:28