Calculating averages using QUERY and INDIRECT

Question

I have the below sheets:

I'm trying to calculate the averages of the values from 2015-2019 for each date in the following sheet:

This is straightforward using AVERAGE. But I'm trying to calculate the averages for all the years based on the titles B1,C1 (and the corresponding sheets).

I tried using INDIRECT to point B1 to tomato sheet, but I'm not sure how to incorporate it with QUERY to calculate all the averages of the years from 2015-2019.

Here's the desired result using AVERAGE:

How do I achieve the above but with QUERY and based on the sheet names corresponding to data in row 1 (B1, C1 so on).

Here's the sheet

player0 player0 · Accepted Answer · 2019-11-04T18:27:14

paste in B2 and drag to the right:

=ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(INDIRECT(B1&"!B2:Z")), 
 "select "&TEXTJOIN(",", 1, IF(LEN($A2:$A), 
 "avg(Col"&ROW($A2:$A)-ROW($A2)+1&")", ))&"")), "select Col2"))

Calculating averages using QUERY and INDIRECT

1 Answers