The AVERAGEIF function in Google Sheets is a powerful tool designed to compute the average of a specified data range that meets defined criteria. This function is particularly useful when analyzing large datasets, allowing users to gain insights based on selective sampling rather than whole data aggregation. With AVERAGEIF, users can generate meaningful statistical summaries without manual calculations, making data analysis more efficient and precise.

Syntax

AVERAGEIF(criteria_range, criterion, [average_range])

• criteria_range: The range of cells that you want to evaluate against the specified criteria.
• criterion: The condition that determines which cells in the criteria range will be included. This can be a number, expression, or text.
• average_range: (Optional) The actual set of cells to average. If omitted, AVERAGEIF will average the cells within the criteria range.

Example #1

AVERAGEIF(A2:A10, ">10")

This function calculates the average of the values in the range A2 to A10 that are greater than 10. For example, if A2:A10 contains the values {5, 15, 20, 8, 12, 30}, the result would be 22.5 (average of 15, 20, and 30).

Example #2

AVERAGEIF(B2:B10, "Yes", C2:C10)

This formula finds the average of the values in the range C2 to C10, where the corresponding cells in B2 to B10 contain the text “Yes.” If B2:B10 has values {Yes, No, Yes, Yes, No}, and C2:C10 has {50, 40, 60, 70}, the result would be 60 (average of 50, 60, and 70).

Example #3

AVERAGEIF(D2:D10, "<1000", D2:D10)

In this case, the function calculates the average of the values less than 1000 in the D2 to D10 range. If D2:D10 contains {500, 1500, 800, 1200}, the result would be 650 (average of 500 and 800).

Error handling

• DIV/0!: This error occurs when there are no cells that meet the specified criteria, resulting in no values to average.
• VALUE!: This error happens if the criteria are incorrectly formatted, such as using text where a number is expected.
• N/A: This error is displayed when the specified criterion does not find any matching values in the criteria range.

Conclusion

In summary, the AVERAGEIF function in Google Sheets is invaluable for performing conditional averages on datasets. Its ability to evaluate data based on specified criteria enhances analysis capabilities, allowing users to derive relevant insights easily. By effectively utilizing AVERAGEIF, users can streamline their data analysis processes, resulting in more informed decision-making and efficient data management.