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".