1

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
2

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. – DwightM Nov 19 '14 at 16:37
  • Can you explain how the code works? I am trying to solve a similar problem. – Annan Feb 8 at 6:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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