Google sheets/Excel: Rolling average for the past three months on different sheets [closed]

Question

I was tasked with creating a rolling average column which compares one row to it's exact matches from the last three months (each month has it's own sheet).

Currently, my (beginners) work looks like this:

AD3 AD4 and AD5 are the name of the tabs, e.g. MAY 2022

Row 7 happens to be the row that is looking to be matched on the other sheets.

=AVERAGE(
IFERROR(AVERAGEIFS(
INDIRECT($AD$3&"!$K$3:$K$500"),
INDIRECT($AD$3&"!$B$3:$B$500"),$B7,
INDIRECT($AD$3&"!$D$3:$D$500"),$D7,
INDIRECT($AD$3&"!$E$3:$E$500"),$E7,
INDIRECT($AD$3&"!$F$3:$F$500"),$F7),0),
IFERROR(AVERAGEIFS(
INDIRECT($AD$4&"!$K$3:$K$500"),
INDIRECT($AD$4&"!$B$3:$B$500"),$B7,
INDIRECT($AD$4&"!$D$3:$D$500"),$D7,
INDIRECT($AD$4&"!$E$3:$E$500"),$E7,
INDIRECT($AD$4&"!$F$3:$F$500"),$F7),0),
IFERROR(AVERAGEIFS(
INDIRECT($AD$5&"!$K$3:$K$500"),
INDIRECT($AD$5&"!$B$3:$B$500"),$B7,
INDIRECT($AD$5&"!$D$3:$D$500"),$D7,
INDIRECT($AD$5&"!$E$3:$E$500"),$E7,
INDIRECT($AD$5&"!$F$3:$F$500"),$F7),0)
)

This works, but if one value is 0, I'd like it to be ignored for the average, which doesn't seem possible with the AVERAGE and AVERAGEIFS functions I'm using. So my question is twofold, is there a simpler way I could be doing this, and how can ignore 0 so it doesn't bring down my average?

Thanks.

Answer 1

Choose Insert > Sheet, name the new sheet Helper, and put this { array expression } in cell A1 of the new sheet:

={ 
  indirect('My report sheet'!AD3 & "!A3:K"); 
  indirect('My report sheet'!AD4 & "!A3:K"); 
  indirect('My report sheet'!AD5 & "!A3:K") 
}

Then use this formula to get the report:

=average( 
  iferror( 
    filter( 
      Helper!K$3:K, 
      Helper!K$3:K <> 0, 
      Helper!B$3:B = B7, 
      Helper!D$3:D = D7, 
      Helper!E$3:E = E7, 
      Helper!F$3:F = F7 
    ) 
  ) 
)

You can hide the Helper sheet if it gets in the way.