1

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 - - - - - - -
player0
  • 124,011
  • 12
  • 67
  • 124
Perri Elizabeth
  • 47
  • 4

2 Answers2

0

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • What is the data is across in row 1 instead of down? – Perri Elizabeth Dec 08 '21 at 21:54
  • @PerriElizabeth answer updated... https://i.stack.imgur.com/t2sss.png – player0 Dec 08 '21 at 22:10
  • How would you apply this in the following googlesheet link? If I wanted to get the average of the last 4 values beginning in e3:x3 how would that work? @player0 https://docs.google.com/spreadsheets/d/1R4GSBDiSBZWofMSYEm0Q0tvySvYcjLmbY-Qbgkr_Xgs/edit?usp=sharing – Perri Elizabeth Dec 08 '21 at 22:16
0

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

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37