Search code examples
google-sheetsgoogle-sheets-formulagoogle-sheets-query

Is there a way to average from filtering specific data from an importrange?

I'm working on a different Google Sheets spreadsheet to input data (film details such as English Title, Original Title, Release Date, Rating, Country of Origin and a Link), while on the one I'm analyzing the data I managed to use importrange successfully.

Here is the code I used successfully in order to get the average rating of a country's list of movies:

=AVERAGE(IMPORTRANGE("LINK_TO_INPUT_DATA_GOOGLE_SHEETS", CONCAT(A1:A, "!D1:D")))

This average is outputted to column C, while the name of the Country (which is also the name of the sheet for importrange) is in Column A.

I want to create a similar query but for movies that have the Country of Origin matching the Country from Column A (Any movie that has multiple countries of origin are inputted with the first one in the spreadsheet and copied over in all the other countries of origin's respective sheets).

I tried using the QUERY from Google Sheets to make my resultset, but in the best case scenario, it gives the same result as the previous average, while in the worst case scenario it just gives out errors. Here is my latest attempt at the query:

=AVERAGE(QUERY (IMPORTRANGE("LINK_TO_INPUT_DATA_GOOGLE_SHEETS", A1:A), "SELECT Col4 WHERE Col5="&A1&""))

As far as I can tell, this should work, but at the moment it says it cannot find the range or sheet for the imported range.

Any help is deeply appreciated!

EDIT:

Here's a link of the input sheet: https://docs.google.com/spreadsheets/d/1bopmJu7Av71sCh8iUoG20WubGL9ssx09dOnBZnys4Ko/edit?usp=sharing

Here's a link of the analysis spreadsheet (the query should be in the MOVIES sheet): https://docs.google.com/spreadsheets/d/1-hfQdqvDWXXtGR2fmTy-lZEOtp9sdxkvoget4toi1W4/edit?usp=sharing

Solution

  • I am not sure If I got you right: - you want: import the average of the ratings (column 4) by movie title (column 1) where the country matches your current column A?

    If so it can simply done with queries, especially if you include the average in the query as well. But you need to include all columns you use in the importrange:

    =QUERY(IMPORTRANGE("https://...", "Syria!A1:E"),"SELECT AVG(Col4) WHERE Col5='"&A2&"' LABEL AVG(Col4) ''")

    Explanation: group by will aggregate all columns by the column you declared as being used as average.