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.

Practice

1. What does the AVERAGEIF function do in Google Sheets?

easy

A. Finds the maximum number in a range.

B. Calculates the sum of all numbers in a range.

C. Counts how many cells contain numbers.

D. Calculates the average of numbers that meet one specific condition.

Solution

1. Step 1: Understand the purpose of AVERAGEIF

   The AVERAGEIF function calculates the average of numbers that satisfy a single condition.

2. Step 2: Compare with other options

   Options B, C, and D describe different functions: SUM, COUNT, and MAX, which do not average with conditions.

3. Final Answer:

   Calculates the average of numbers that meet one specific condition. -> Option D

4. Quick Check:

   AVERAGEIF = average with one condition [OK]

Hint: AVERAGEIF averages numbers with one condition only [OK]

Common Mistakes:

• Confusing AVERAGEIF with SUM or COUNT functions
• Thinking it works with multiple conditions
• Assuming it counts cells instead of averaging

2. Which of the following is the correct syntax for AVERAGEIFS in Google Sheets?

easy

A. AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

B. AVERAGEIFS(criteria_range, average_range, criterion)

C. AVERAGEIFS(range, condition)

D. AVERAGEIFS(average_range, criterion1, criteria_range1)

Solution

1. Step 1: Recall AVERAGEIFS syntax

   The correct syntax is AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) where average_range is first.

2. Step 2: Check each option

   AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) matches the correct syntax. Options A, B, and C have wrong argument order or missing parts.

3. Final Answer:

   AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) -> Option A

4. Quick Check:

   AVERAGEIFS syntax = average_range first [OK]

Hint: Remember: average_range is always first in AVERAGEIFS [OK]

Common Mistakes:

• Swapping average_range and criteria_range
• Using only one criteria without brackets
• Missing criteria pairs for multiple conditions

3. Given the data:
A1:A5 = {10, 20, 30, 40, 50}
B1:B5 = {"Yes", "No", "Yes", "No", "Yes"}
What is the result of =AVERAGEIF(B1:B5, "Yes", A1:A5)?

medium

A. 30

B. 20

C. 40

D. 50

Solution

1. Step 1: Identify values matching the condition

   Condition is "Yes" in B1:B5. Matching rows are 1, 3, and 5 with values 10, 30, and 50 in A1:A5.

2. Step 2: Calculate average of matching values

   Average = (10 + 30 + 50) / 3 = 90 / 3 = 30.

3. Final Answer:

   30 -> Option A

4. Quick Check:

   Average of 10, 30, 50 = 30 [OK]

Hint: Average only values where condition matches [OK]

Common Mistakes:

• Averaging all values ignoring condition
• Using wrong range for criteria
• Confusing AVERAGEIF with COUNTIF

4. You wrote this formula:
=AVERAGEIFS(A1:A5, B1:B5, ">20", C1:C5, "Yes")
But it returns an error. What is the likely problem?

medium

A. The criteria ">20" should be in quotes, but it is missing.

B. The ranges A1:A5, B1:B5, and C1:C5 must be the same size.

C. AVERAGEIFS does not support multiple criteria.

D. AVERAGEIFS requires only one criteria range.

Solution

1. Step 1: Check criteria syntax

   The criteria ">20" is correctly in quotes, so that's not the problem.

2. Step 2: Verify range sizes

   AVERAGEIFS requires all ranges (average_range and criteria_ranges) to be the same size. If they differ, it causes an error.

3. Final Answer:

   The ranges A1:A5, B1:B5, and C1:C5 must be the same size. -> Option B

4. Quick Check:

   Range sizes must match in AVERAGEIFS [OK]

Hint: Check all ranges have same number of rows [OK]

Common Mistakes:

• Using different sized ranges
• Forgetting quotes around criteria
• Thinking AVERAGEIFS supports only one condition

5. You have sales data:
Sales (A2:A7): {100, 200, 150, 300, 250, 400}
Region (B2:B7): {"East", "West", "East", "West", "East", "West"}
Status (C2:C7): {"Complete", "Complete", "Pending", "Complete", "Pending", "Complete"}
Which formula calculates the average sales for the East region with status Complete?

hard

A. =AVERAGEIF(A2:A7, "East", B2:B7) * AVERAGEIF(A2:A7, "Complete", C2:C7)

B. =AVERAGEIF(B2:B7, "East", A2:A7) + AVERAGEIF(C2:C7, "Complete", A2:A7)

C. =AVERAGEIFS(A2:A7, B2:B7, "East", C2:C7, "Complete")

D. =AVERAGEIFS(B2:B7, A2:A7, "East", C2:C7, "Complete")

Solution

1. Step 1: Understand the requirement

   We want average of sales (A2:A7) where Region is "East" and Status is "Complete".

2. Step 2: Check each formula

   =AVERAGEIFS(A2:A7, B2:B7, "East", C2:C7, "Complete") correctly uses AVERAGEIFS with sales as average_range and two criteria ranges with conditions. Options B, C, and D misuse ranges or functions.

3. Final Answer:

   =AVERAGEIFS(A2:A7, B2:B7, "East", C2:C7, "Complete") -> Option C

4. Quick Check:

   Use AVERAGEIFS for multiple conditions [OK]

Hint: Use AVERAGEIFS with average_range first, then criteria pairs [OK]

Common Mistakes:

• Adding averages instead of using AVERAGEIFS
• Mixing up average_range and criteria_range
• Using AVERAGEIF for multiple conditions