AVERAGEIF and AVERAGEIFS in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate average sales above a threshold

You have a list of sales amounts in cells A2:A10. Which formula correctly calculates the average of sales greater than 100?

A =AVERAGEIF(A2:A10, ">100")

B =AVERAGEIF(A2:A10, "<100")

C =AVERAGEIF(A2:A10, ">100", A2:A10)

D =AVERAGE(A2:A10)

💡 Hint

Remember AVERAGEIF needs the range to average if different from the criteria range.

Options A and C correctly use AVERAGEIF(range, criteria, average_range); A omits average_range (defaults to criteria_range) and C specifies it explicitly where both range and average_range are the same.

Option B uses the wrong criteria <100.

Option D averages all values without any condition.

📊 Formula Result

intermediate

Average with multiple conditions

You have sales data in column A and regions in column B. Which formula calculates the average sales in region "East" where sales are above 200?

A =AVERAGEIFS(B2:B20, A2:A20, ">200", B2:B20, "East")

B =AVERAGEIF(B2:B20, "East", A2:A20) + AVERAGEIF(A2:A20, ">200", A2:A20)

C =AVERAGEIFS(A2:A20, B2:B20, "East", A2:A20, ">200")

D =AVERAGEIF(A2:A20, ">200", B2:B20)

💡 Hint

AVERAGEIFS uses average_range first, then pairs of criteria_range and criteria.

Option C correctly uses AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2).

Option C adds two averages separately, which is incorrect.

Option C swaps average_range and criteria_range, causing wrong output.

Option C uses AVERAGEIF with mismatched ranges.

❓ Function Choice

advanced

Choosing the right function for conditional average

You want to calculate the average of values in C2:C15 where column A equals "Yes" and column B is greater than 50. Which function should you use?

A AVERAGEIFS

B COUNTIF

C SUMIF

D AVERAGEIF

💡 Hint

Think about how many conditions you have.

AVERAGEIFS supports multiple conditions, so option A is correct.

AVERAGEIF supports only one condition.

SUMIF and COUNTIF calculate sums and counts, not averages.

🎯 Scenario

advanced

Troubleshooting average with text values

You use =AVERAGEIF(A2:A10, ">50", B2:B10) but the result seems wrong. Column B contains numbers and some text like "N/A". What is the likely cause?

A The criteria range and average range must be the same for AVERAGEIF.

B AVERAGEIF cannot handle criteria with ">50".

C The formula should use AVERAGE instead of AVERAGEIF.

D Text values in B2:B10 cause AVERAGEIF to ignore those cells in average calculation.

💡 Hint

Think about how text values affect average calculations.

Text values in the average_range are ignored in average calculations, which can skew results if many are present.

Option D is false; ">50" is valid criteria.

Option D ignores the condition.

Option D is incorrect; ranges can differ.

❓ data_analysis

expert

Analyzing average with overlapping criteria

Given data in columns A (Category), B (Score), and C (Status), you want the average Score for Category "X" and Status not equal to "Complete". Which formula produces the correct result?

A =AVERAGEIFS(B2:B30, A2:A30, "X", C2:C30, "<>Complete")

B =AVERAGEIFS(B2:B30, A2:A30, "X", C2:C30, "Complete")

C =AVERAGEIF(B2:B30, "X", C2:C30)

D =AVERAGEIF(A2:A30, "X", B2:B30) - AVERAGEIF(C2:C30, "Complete", B2:B30)

💡 Hint

Use AVERAGEIFS with multiple criteria including not equal condition.

Option A correctly uses "<>Complete" to exclude "Complete" status.

Option A includes only "Complete" status, wrong condition.

Option A subtracts averages incorrectly.

Option A uses wrong ranges and criteria.