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.
Asked
Active
Viewed 2,082 times
1
-
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 Jun 21 '20 at 12:54
-
Yes that's exactly what I am aiming for – Sam Moloney Jun 21 '20 at 13:07
-
see: https://stackoverflow.com/a/59120993/5632629 – player0 Jun 21 '20 at 14:37
-
How do I change the above so it works to average 7 columns rather than rows? – Sam Moloney Jun 21 '20 at 16:29
1 Answers
2
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,"<>")))
Tom Sharpe
- 30,727
- 5
- 24
- 37