AVERAGEIF and AVERAGEIFS in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to calculate the average of numbers in column B where the value in column A is "Apple".

Google Sheets

=AVERAGEIF(A:A, [1], B:B)

Drag options to blanks, or click blank then click option'

A"Apple"

BApple

CA1

D"A:A"

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to put quotes around text criteria.

Using a cell reference instead of the text string.

The criterion in AVERAGEIF must be a text string in quotes, so use "Apple".

2fill in blank

medium

Complete the formula to calculate the average of sales in column C where the region in column B is "East".

Google Sheets

=AVERAGEIF(B:B, [1], C:C)

Drag options to blanks, or click blank then click option'

A"C:C"

BEast

CB1

D"East"

Attempts:

3 left

💡 Hint

Common Mistakes

Using text without quotes causes errors.

Referencing the wrong column for criteria.

The criterion must be a text string in quotes, so use "East".

3fill in blank

hard

Fix the error in the formula to average values in D:D where A:A is greater than 100.

Google Sheets

=AVERAGEIF(A:A, [1], D:D)

Drag options to blanks, or click blank then click option'

A>=100

B100

C">100"

DA1>100

Attempts:

3 left

💡 Hint

Common Mistakes

Using just 100 without operator.

Writing the condition without quotes.

The criterion must be a string with the operator and number, so use ">100" in quotes.

4fill in blank

hard

Fill both blanks to calculate the average in column D where column A is "East" and column B is greater than 50.

Google Sheets

=AVERAGEIFS(D:D, A:A, [1], B:B, [2])

Drag options to blanks, or click blank then click option'

A"East"

B">50"

C50

D"B:B"

Attempts:

3 left

💡 Hint

Common Mistakes

Not quoting text criteria.

Using number 50 without operator for condition.

Text criteria need quotes, so "East" for column A. For numbers with conditions, use ">50" as a string.

5fill in blank

hard

Fill all three blanks to average values in E:E where column A is "West", column B is less than 100, and column C equals "Completed".

Google Sheets

=AVERAGEIFS(E:E, A:A, [1], B:B, [2], C:C, [3])

Drag options to blanks, or click blank then click option'

A"West"

B"<100"

C"Completed"

D100

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting quotes around text criteria.

Using number 100 without operator for condition.

Text criteria must be in quotes: "West" and "Completed". Number condition with operator must be a string: "<100".

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