AVERAGEA Function in Excel: Explained

In this article, you will learn how to use the AVERAGEA function in Excel

‍

What does the AVERAGEA function in Excel do?

The AVERAGEA function in Excel calculates the average of a set of numerical values, including cells that contain text or logical values (TRUE/FALSE). It is a statistical function that considers both numeric and non-numeric entries in the range.

‍

How is the AVERAGEA function different from other AVERAGE functions in EXCEL?

The AVERAGEA function in Excel differs from other average functions in how it handles non-numeric values within the range. Here are the key differences between AVERAGE.A and other average functions:

1. Averages with Non-numeric Values: The AVERAGEA function includes non-numeric values (such as text or logical values) in the calculation and treats them as zero (0). Other average functions like AVERAGE, AVERAGEIF, and AVERAGEIFS exclude non-numeric values from the calculation.
2. Compatibility: The AVERAGEA function is available in Excel 2016 or later versions and is designed to work with dynamic arrays. 
3. Handling Empty Cells: The AVERAGEA function ignores empty cells in the range and does not consider them in the calculation. Other average functions typically treat empty cells as zero (0) and include them in the calculation.
4. Ignoring Error Values: The AVERAGEA function ignores error values (such as #DIV/0!, #VALUE!, etc.) in the range and excludes them from the calculation. Other average functions treat error values as zero (0) and include them in the calculation.

To summarize, the AVERAGEA function is unique in that it includes non-numeric values as zero in the calculation, ignores empty cells, and excludes error values. Other average functions in Excel typically exclude non-numeric values, treat empty cells as zero, and include error values in the average calculation. The AVERAGEA function is particularly useful when you have a mix of numeric and non-numeric values in the range and want to include all values in the average calculation.

‍

What are some areas where the AVERAGEA function is useful in Excel?

The AVERAGEA function in Excel has several uses due to its ability to handle both numeric and non-numeric values within a range. Here are some common uses of the AVERAGEA function:

1. Inclusive Average Calculation: The AVERAGEA function is particularly useful when you have a range containing a mix of numeric values, text, logical values (TRUE/FALSE), and empty cells. It calculates the average by considering all these values, including non-numeric entries.
2. Handling Errors: The AVERAGEA function excludes error values (such as #DIV/0!, #VALUE!, etc.) from the calculation. This makes it useful for averaging ranges that may contain error values, ensuring accurate results without manually filtering or removing errors.
3. Textual Analysis: If you have a range with numeric values represented as text, the AVERAGEA function can handle such scenarios and calculate the average without requiring you to convert the text to numbers explicitly.
4. Flexible Data Ranges: The AVERAGEA function can work with ranges that dynamically expand or contract. When new values or entries are added or removed from the range, the AVERAGEA function automatically adjusts the calculation to include the updated data.

Overall, the AVERAGEA function is versatile and useful when you need to calculate an average that includes both numeric and non-numeric values within a range. It provides flexibility and convenience in data analysis, allowing you to work with diverse datasets without the need for extensive data manipulation or preprocessing.

‍

How to use the AVERAGEA function in Excel?

Please see below for the syntax of AVERAGEA function:

=AVERAGEA(value1, [value2], ...)

In the following example we have a data set that includes error and text values. The output shows how the AVEARAGEA function includes both these values in the calculation and accordingly displaying =180/8 or 22.5 as the result. The regular average function on the other hand excludes these values and accordingly displaying =180/6 or 30 as the result

Example using AVERAGEA function in Excel

‍

Note: If all the values in the range are numeric, you can use the regular AVERAGE function (AVERAGE) instead of AVERAGEA. The AVERAGEA function is particularly useful when you have a mix of numeric and non-numeric values in the range and want to include them in the average calculation.

‍

By using the AVERAGEA function, you can calculate the average of a range of values while accounting for non-numeric entries, providing a more comprehensive measure of central tendency for diverse datasets.

‍