Find Rolling Average of Last 6 values in a row

user_12090346 0
Viewed 90

I have data in googlesheet that I need to find the average of the last 6 values in a row. Every week I add another data point so I would need it to calculate the rolling average. I tried using the following formula to calculate it:

=iferror(average(offset(CT3,0,max(0,count(CT3:DM3)-6),1,6)),0)

When I do that with the data set below the average calculates out to 7. But the actual average should be 7.67. Can anyone help?

The data is in the sheet as follows:

ARI 16 0 18 5 8 12 11 1 12 3 8 - 11 - - - - - - -
rolling-average google-sheets-formula moving-average google-sheets average
2 Answers

try:

=INDEX(AVERAGE(ARRAY_CONSTRAIN(SORT(INDIRECT("A1:"&
  ADDRESS(MAX(ISNUMBER(A:A)*ROW(A:A)), 1, )),
 SEQUENCE(MAX(ISNUMBER(A:A)*ROW(A:A))), 0), 7, 1)))

enter code here

update:

=INDEX(AVERAGE(ARRAY_CONSTRAIN(SORT(FLATTEN(INDIRECT("A1:"&
 ADDRESS(1, MAX(ISNUMBER(1:1)*COLUMN(1:1))))), 
   SEQUENCE(MAX(ISNUMBER(1:1)*COLUMN(1:1))), 0), 7, 1)))

enter image description here

user_56326290

I was wondering if there was a way of doing this without sorting. For the example in the comment:

=(sum(E3:X3)-sum(array_constrain(filter(E3:X3,isnumber(E3:X3)),1,count(E3:X3)-4)))/4

enter image description here

user_38949170
Related