Combine Google Sheets Query, IMPORTRANGE, and AVERAGE functions

1
votes

I'm trying to aggregate an average value using Query and IMPORTRANGE so I can compare the index numbers of the two tables.

=QUERY(IMPORTRANGE("Table ","Sheet!B:C"),"Select avg(Col2) where Col1='"&A2&"'",0)

File 1:

Table 1 Index Above formula
1 =QUERY(IMPORTRANGE("URL","Sheet!I:J"),"Select avg(Col2) where Col1='"&A2&"'",0)

File 2:

Table 2 Index Table 1 Index Match Data
1 1 2.0
2 1 10.0

But it returns empty. When I remove avg(), then I get an error that the dataset is empty. So somewhere I made a mistake, and it's not comparing File 2 Column B against File 1 Column A properly. I have the IMPORTRANGE() selecting columns B and C, so that means I'm trying to average Col2 and comparing Col1 to A2, but I can't see the error there.

2
google-sheetsgoogle-query-language
I don't know what happened to the formatting, the table looks all janked... I'll try to fix it. - Tony White
Can you provide at least a screenshot of your desired output? - Iamblichus

2 Answers

1
votes

try:

=QUERY(IMPORTRANGE("ID", "Sheet!B:C"), 
 "select Col1,avg(Col2) 
  where Col1 is not null 
  group by Col1
  label avg(Col2)''", 0)
0
votes

I did a workaround and incorporated a hidden "Meta Data" tab.

I used IMPORTRANGE() to get all of the Table 1 indices that could exist into Sheet 2 Meta Data tab, did an AVERAGEIF() down column 2 that would aggregate Table 2's data into an average if it matches the imported range in column 1. Then just used a VLOOKUP(IMPORTRANGE()) combo in Sheet 1 Table 1 to bring the aggregation back.