Google Sheets AVERAGE function in ARRAYFORMULA

Question

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.

Answer 1

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