Search code examples
google-sheetsaveragegoogle-sheets-formulaarray-formulasgoogle-sheets-query

Calculating averages using QUERY and INDIRECT

I have the below sheets:

enter image description here

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

enter image description here

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:

enter image description here

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

Solution

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

    0