AVERAGEIF and AVERAGEIFS in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate average with AVERAGEIF for sales above 100

Given the sales data in cells A2:A6 as {90, 110, 130, 80, 120}, what is the result of the formula =AVERAGEIF(A2:A6, ">100")?

A 120

B 110

C 130

D 115

💡 Hint

Only numbers greater than 100 are included in the average.

The numbers greater than 100 are 110, 130, and 120. Their average is (110 + 130 + 120) / 3 = 120.

📊 Formula Result

intermediate

Using AVERAGEIFS with multiple criteria

You have sales data in column A (A2:A7) and region data in column B (B2:B7). Sales: {200, 150, 300, 250, 100, 400}, Regions: {"East", "West", "East", "West", "East", "West"}. What is the result of =AVERAGEIFS(A2:A7, B2:B7, "East", A2:A7, ">150")?

A 200

B 275

C 250

D 300

💡 Hint

Filter sales where region is East and sales are greater than 150.

East region sales are 200, 300, and 100. Among these, sales > 150 are 200 and 300. Average is (200 + 300) / 2 = 250.

📊 Formula Result

advanced

AVERAGEIFS with text and numeric criteria

Given data: Scores in C2:C8 = {85, 90, 75, 88, 92, 70, 95}, Status in D2:D8 = {"Pass", "Pass", "Fail", "Pass", "Pass", "Fail", "Pass"}. What is the result of =AVERAGEIFS(C2:C8, D2:D8, "Pass", C2:C8, ">80")?

A 90

B 88

C 87.5

D 89.5

💡 Hint

Average scores where status is Pass and score is greater than 80.

Pass scores > 80 are 85, 90, 88, 92, 95. Average is (85+90+88+92+95)/5 = 450/5 = 90.

🎯 Scenario

advanced

Identify the error in AVERAGEIF formula

You want to average values in E2:E10 where corresponding values in F2:F10 are "Completed". Which formula will cause an error?

A =AVERAGEIF(F2:F10, "=Completed", E2:E10)

B =AVERAGEIF(F2:F10, "Completed", E2:E10)

C =AVERAGEIF(F2:F10, "Completed")

D =AVERAGEIF(E2:E10, "Completed", F2:F10)

💡 Hint

Check the order of arguments in AVERAGEIF.

AVERAGEIF syntax is AVERAGEIF(range, criteria, [average_range]). Option D uses E2:E10 as range but criteria is text "Completed" which is in F2:F10, so it causes wrong output or error.

❓ data_analysis

expert

Analyze average sales with multiple criteria using AVERAGEIFS

You have a sales table with columns: Sales (G2:G12), Region (H2:H12), and Product (I2:I12). Sales: {100, 200, 150, 300, 250, 400, 350, 100, 450, 500, 600}, Region: {"North", "South", "North", "South", "North", "South", "North", "South", "North", "South", "North"}, Product: {"A", "A", "B", "B", "A", "B", "A", "B", "A", "B", "A"}. What is the result of =AVERAGEIFS(G2:G12, H2:H12, "North", I2:I12, "A", G2:G12, ">200")?

A 450

B 412.5

C 350

D 400

💡 Hint

Filter sales where Region is North, Product is A, and Sales > 200.

North & Product A sales > 200: 250, 350, 450, 600. Average is (250 + 350 + 450 + 600) / 4 = 1650 / 4 = 412.5.

Practice

1. What does the AVERAGEIF function do in Excel?

easy

A. Finds the maximum value in a range.

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

C. Counts the number of cells that meet multiple conditions.

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

Solution

1. Step 1: Understand the purpose of AVERAGEIF

   AVERAGEIF calculates the average of cells that satisfy a single condition.

2. Step 2: Compare with other functions

   Unlike SUM or COUNT, it focuses on averaging with one condition.

3. Final Answer:

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

4. Quick Check:

   AVERAGEIF = average with one condition [OK]

Hint: AVERAGEIF averages cells matching one condition only [OK]

Common Mistakes:

• Confusing AVERAGEIF with SUM or COUNT functions
• Thinking it handles multiple conditions
• Assuming it finds max or min values

2. Which of the following is the correct syntax for the AVERAGEIFS function?

easy

A. AVERAGEIFS(criteria_range, average_range, criteria1, criteria2)

B. AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

C. AVERAGEIFS(average_range, criteria1, criteria_range1, criteria2)

D. AVERAGEIFS(average_range, criteria_range1, criteria1)

Solution

1. Step 1: Recall AVERAGEIFS syntax

   The correct syntax starts with the range to average, then pairs of criteria ranges and criteria.

2. Step 2: Check option formats

   AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) matches the correct order: average_range, criteria_range1, criteria1, etc.

3. Final Answer:

   AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) -> Option B

4. Quick Check:

   AVERAGEIFS syntax = average_range first [OK]

Hint: AVERAGEIFS starts with average range, then criteria pairs [OK]

Common Mistakes:

• Swapping average_range and criteria_range
• Omitting criteria pairs
• Using AVERAGEIF syntax for AVERAGEIFS

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 cells matching criteria

   Cells in B1:B5 with "Yes" are B1, B3, B5 corresponding to A1=10, A3=30, A5=50.

2. Step 2: Calculate average of matching A cells

   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 criteria matches [OK]

Common Mistakes:

• Averaging all values ignoring criteria
• Using wrong criteria text
• Calculating sum instead of average

4. You wrote the formula =AVERAGEIFS(A1:A5, B1:B5, ">20", C1:C5, "Yes") but it returns #DIV/0!. What is the likely cause?

medium

A. No cells meet both criteria, so division by zero occurs.

B. Syntax error in the formula due to missing commas.

C. AVERAGEIFS cannot handle numeric criteria with ">".

D. The average range and criteria ranges have different sizes.

Solution

1. Step 1: Understand #DIV/0! error in AVERAGEIFS

   This error happens when no cells meet all criteria, so average divides by zero.

2. Step 2: Check formula logic

   If no values in B1:B5 are >20 and C1:C5 equal "Yes" simultaneously, no data to average.

3. Final Answer:

   No cells meet both criteria, so division by zero occurs. -> Option A

4. Quick Check:

   #DIV/0! means no matching data [OK]

Hint: Check if any data meets all criteria before averaging [OK]

Common Mistakes:

• Assuming syntax error causes #DIV/0!
• Thinking AVERAGEIFS can't use > or <
• Ignoring range size mismatch possibility

5. You have sales data in A2:A10, regions in B2:B10, and sales reps in C2:C10. How do you calculate the average sales for region "East" and sales rep "John"?

hard

A. =AVERAGEIF(A2:A10, "East", B2:B10, "John")

B. =AVERAGEIFS(B2:B10, A2:A10, "East", C2:C10, "John")

C. =AVERAGEIFS(A2:A10, B2:B10, "East", C2:C10, "John")

D. =AVERAGEIF(B2:B10, "East") + AVERAGEIF(C2:C10, "John")

Solution

1. Step 1: Identify correct function for multiple conditions

   Use AVERAGEIFS to average with more than one condition.

2. Step 2: Match ranges and criteria correctly

   Average range is sales (A2:A10), criteria ranges are region (B2:B10) and rep (C2:C10) with criteria "East" and "John".

3. Step 3: Verify formula syntax

   =AVERAGEIFS(A2:A10, B2:B10, "East", C2:C10, "John") matches correct syntax and logic.

4. Final Answer:

   =AVERAGEIFS(A2:A10, B2:B10, "East", C2:C10, "John") -> Option C

5. Quick Check:

   AVERAGEIFS with correct ranges and criteria [OK]

Hint: Use AVERAGEIFS for multiple conditions with matching ranges [OK]

Common Mistakes:

• Using AVERAGEIF for multiple conditions
• Swapping average and criteria ranges
• Adding averages instead of filtering both conditions