Google Sheets AVERAGE function in ARRAYFORMULA

0
votes

I want to use the ARRAYFORMULA version of =IFERROR(AVERAGE(B29:H29),""). This is to calculate a 7 column rolling average that will automatically drag across using the array formula. I am aware the AVERAGE cannot be used with ARRAYFORMULA but I have struggled to find an alternative.

1
google-sheetsgoogle-sheets-formulaarray-formulas
so the first cell would be like average(b29:h29) and the next would be like average(c29:i29) but without the manual drag across ??? - Gary's Student
Yes that's exactly what I am aiming for - Sam Moloney
see: stackoverflow.com/a/59120993/5632629 - player0
How do I change the above so it works to average 7 columns rather than rows? - Sam Moloney

1 Answers

2
votes

As discussed in the comments you can modify the previous answer.

Another approach to get the running average of the current number and the next 6 numbers is to take the difference of two running sums and divide by the count like this:

=ArrayFormula(if(C1:1="","",
   (sumif(column(C1:1),"<"&column(C1:1)+7,C1:1)-sumif(column(C1:1),"<"&column(C1:1),C1:1))/
   countifs(column(C1:1),"<"&column(C1:1)+7,column(C1:1),">="&column(C1:1),C1:1,"<>")))

(you can't use AVERAGEIFS or SUMIFS because they don't work with array formulas).

For completeness, the more usual running average starting with the first number, then the average of the first two numbers etc. would be given by:

=ArrayFormula(if(C1:1="","",
    (sumif(column(C1:1),"<="&column(C1:1),C1:1)-sumif(column(C1:1),"<="&column(C1:1)-7,C1:1))/
    countifs(column(C1:1),"<="&column(C1:1),column(C1:1),">"&column(C1:1)-7,C1:1,"<>")))

enter image description here