AVERAGEIF and AVERAGEIFS in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the AVERAGEIF function do in Excel?

AVERAGEIF calculates the average (mean) of numbers in a range that meet a single condition or criteria.

beginner

How is AVERAGEIFS different from AVERAGEIF?

AVERAGEIFS calculates the average of numbers that meet multiple conditions, while AVERAGEIF works with only one condition.

beginner

Write the syntax of AVERAGEIF function.

AVERAGEIF(range, criteria, [average_range])<br> - range: cells to check the condition<br> - criteria: condition to meet<br> - average_range (optional): cells to average if different from range

intermediate

What happens if no cells meet the criteria in AVERAGEIF or AVERAGEIFS?

Excel returns the #DIV/0! error because it tries to divide by zero (no matching cells to average).

intermediate

Give a real-life example where AVERAGEIFS is useful.

Calculating the average sales amount for a specific product in a specific region, using multiple conditions like product name and region.

Which function would you use to find the average of numbers greater than 50 in a list?

A AVERAGEIFS

B SUMIF

C AVERAGEIF

D COUNTIF

AVERAGEIF calculates the average of numbers that meet a single condition, such as being greater than 50.

What is the correct syntax for AVERAGEIFS?

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

B AVERAGEIFS(criteria_range, criteria, average_range)

C AVERAGEIFS(range, criteria)

D AVERAGEIFS(criteria, range)

AVERAGEIFS requires the average_range first, then pairs of criteria ranges and criteria.

If you want to average scores only for students who scored above 80 and attended more than 90% classes, which function is best?

A AVERAGEIFS

B SUMIF

C AVERAGEIF

D COUNTIF

AVERAGEIFS allows multiple conditions, so it fits this need.

What result do you get if no cells meet the criteria in AVERAGEIF?

A 0

B #DIV/0! error

C Blank cell

D Sum of all cells

Excel returns #DIV/0! error because it tries to divide by zero when no cells match.

Which argument is optional in AVERAGEIF?

A range

B criteria_range

C criteria

D average_range

average_range is optional; if omitted, Excel averages the cells in range.

Explain how to use AVERAGEIF to find the average of sales above $1000 in a sales list.

Think about which cells you want to check and which cells to average.

Describe a scenario where AVERAGEIFS is better than AVERAGEIF and how you would set it up.

Consider filtering data by more than one rule.