Why external data expands analysis in Google Sheets - Challenge Your Understanding

Challenge - 5 Problems

🎯 Scenario

intermediate

Using External Data to Calculate Average Sales

You have sales data in your Google Sheet for your store. You want to include data from an external sheet that tracks online sales to get a full picture. Which formula correctly imports the external data and calculates the average total sales?

A =AVERAGE({A2:A10; IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "OnlineSales!B2:B10")})

B =AVERAGE(A2:A10) + IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "OnlineSales!B2:B10")

C =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "OnlineSales!B2:B10") + AVERAGE(A2:A10)

D =SUM(A2:A10, IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "OnlineSales!B2:B10"))/2

💡 Hint

Think about how to combine ranges from your sheet and an external sheet before averaging.

Option A correctly combines the local and external ranges into one array using curly braces and semicolon, then calculates the average of all values together. Options B and C try to add a number to a range, which causes errors. Option A sums two ranges incorrectly and divides by 2, which does not calculate the average of all values.

📊 Formula Result

intermediate

Result of Combining Local and External Data

You have the formula =SUM(A1:A3) + SUM(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B1:B3")). If the local range A1:A3 contains {2, 4, 6} and the external range B1:B3 contains {1, 3, 5}, what is the result?

A 20

B 11

C 21

D Error: IMPORTRANGE not authorized

💡 Hint

Sum each range separately, then add the sums.

SUM(A1:A3) = 2 + 4 + 6 = 12. SUM of external range = 1 + 3 + 5 = 9. Total = 12 + 9 = 21. Option C could happen if IMPORTRANGE is not authorized, but assuming authorization, answer is 21.

❓ Function Choice

advanced

Best Function to Import and Filter External Data

You want to import data from an external Google Sheet and only keep rows where the sales amount is greater than 100. Which formula correctly does this?

A =FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Sales!A2:C100"), IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Sales!C2:C100") > 100)

B =SORT(IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Sales!A2:C100"), 3, TRUE)

C =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Sales!A2:C100") > 100

D =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Sales!A2:C100"), "select * where Col3 > 100", 0)

💡 Hint

Think about which function can both import and filter data in one step.

QUERY with IMPORTRANGE allows filtering rows where the third column (Col3) is greater than 100. FILTER requires repeating IMPORTRANGE twice, which is inefficient and may cause errors. SORT only orders data but does not filter. Option D is a comparison that returns TRUE/FALSE array, not filtered data.

❓ data_analysis

advanced

Analyzing Combined Data from Multiple Sheets

You combined local sales data and external online sales data using =ARRAYFORMULA({A2:A10; IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "OnlineSales!B2:B10")}). How many total sales entries will this combined range have if both ranges have 9 rows each?

A 10

B 18

C 9

D Error: Mismatched array sizes

💡 Hint

Think about how many rows each range has and how they combine vertically.

The formula stacks the two ranges vertically using curly braces and semicolon. Each range has 9 rows, so combined total is 9 + 9 = 18 rows. No error occurs because array sizes do not need to match when stacking vertically.

🧠 Conceptual

expert

Why Use External Data in Analysis?

Which of the following is the best reason to include external data in your spreadsheet analysis?

A To get a more complete view by combining data from multiple sources

B To avoid using built-in spreadsheet functions

C To make formulas more complex without improving insights

D To increase the file size and slow down calculations

💡 Hint

Think about the benefit of combining data from different places.

Including external data helps you see the full picture by combining information from different sources. This leads to better insights and decisions. The other options are drawbacks or incorrect reasons.