AVERAGEIF and AVERAGEIFS in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to calculate the average of values in range B2:B10 where the condition in A2:A10 equals "Apple".

Excel

=AVERAGEIF(A2:A10, [1], B2:B10)

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

A"A2:A10"

BApple

C"Apple"

DB2:B10

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to put the condition text in quotes

Using a range instead of a condition

Using the wrong range for the condition

The condition argument in AVERAGEIF must be a text string with quotes, so use "Apple".

2fill in blank

medium

Complete the formula to calculate the average of values in C2:C15 where the values in B2:B15 are greater than 50.

Excel

=AVERAGEIF(B2:B15, [1], C2:C15)

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

A>50

B>=50

C50

D<50

Attempts:

3 left

💡 Hint

Common Mistakes

Using just the number without operator

Using the wrong comparison operator

Not putting the condition in quotes

The condition must be a string with the comparison operator and number, so use ">50".

3fill in blank

hard

Fix the error in the formula to average values in D2:D20 where A2:A20 equals "Yes" and B2:B20 is less than 100.

Excel

=AVERAGEIFS(D2:D20, A2:A20, [1], B2:B20, [2])

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

A"Yes"

BYes

C"<100"

D<100

Attempts:

3 left

💡 Hint

Common Mistakes

Not putting criteria in quotes

Using numeric values without operators as criteria

Mixing up the order of criteria

In AVERAGEIFS, criteria must be text strings with quotes, so use "Yes" and "<100".

4fill in blank

hard

Fill both blanks to calculate the average of E2:E30 where C2:C30 equals "Completed" and D2:D30 is greater than or equal to 75.

Excel

=AVERAGEIFS(E2:E30, C2:C30, [1], D2:D30, [2])

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

A"Completed"

BCompleted

C">=75"

D>=75

Attempts:

3 left

💡 Hint

Common Mistakes

Leaving out quotes around criteria

Using numeric values without operators

Swapping criteria order

Criteria must be text strings with quotes, so use "Completed" and ">=75".

5fill in blank

hard

Fill all three blanks to calculate the average of F2:F40 where B2:B40 equals "Active", C2:C40 is less than 20, and D2:D40 is not equal to "Pending".

Excel

=AVERAGEIFS(F2:F40, B2:B40, [1], C2:C40, [2], D2:D40, [3])

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

A"Active"

B"<20"

C"<>Pending"

DActive

Attempts:

3 left

💡 Hint

Common Mistakes

Not quoting criteria

Using numeric values without operators

Using wrong syntax for not equal (should be "<>")

All criteria must be text strings with quotes. Use "Active", "<20", and "<>Pending" for the conditions.