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.