Find Rolling Average of Last 6 values in a row-CodePudding

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 - - - - - - -

CodePudding user response：

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

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