In Excel, the AVERAGEA function works to get the average value of any kind of given data type, whether numeric values or text or boolean, AVERAGEA counts all of them as input values. In this article, we will show you how to use the AVERAGEA function in Excel to get the arithmetic mean of a group of values.
You can download the free practice Excel workbook from here.
Overview of The AVERAGEA Function
The AVERAGEA Function
The AVERAGEA Function calculates the average of a group of values – numbers, texts or boolean – and returns the average (arithmetic mean) of the values.
- Generic Syntax
AVERAGEA(value1, [value2], …)
- Argument Description
|value1||Required||value1 can be a range of cells containing numbers and/or texts and/or boolean expression, or cell references or other values that you want to get the arithmetic mean. From 1 to 255 arguments can be passed as parameters.|
|value2||Optional||value2 can be any type of value you want to get the average result.|
- Return Value
A number, representing the arithmetic mean (average) of the given group of values.
10 Examples of Using The AVERAGEA Function in Excel
Below in this section, we will let you know how to use Excel’s AVERAGEA function with the 10 easiest and effective examples.
1. The AVERAGEA Function to Calculate Numbers in Excel
Excel’s AVERAGEA function works exactly as the mathematical AVERAGE function. It takes a range of numbers as input, calculates the sum, divides the sum result with the number of numeric values provided and returns the average value.
2. Calculate Text with the AVERAGEA Function
Unlike the Average function, Excel’s AVERAGEA function also counts cells that contain text values. It counts the text as 0 and passes the cell inside the argument to calculate the arithmetic mean.
3. The AVERAGEA Function to Calculate Numbers and Text
When the AVERAGEA function finds numbers and text together to calculate, it calculates all the cells containing both the numeric value and the text value as 0 and returns a numeric value as the average result.
4. Calculate Blank Cells with the AVERAGEA Function
The AVERAGEA function in Excel doesn’t count the blank cells. When it finds cells that are empty, it throws a #DIV/0! error.
5. AVERAGEA to Calculate Blank Cells with Numbers or Texts
While calculating numbers with blank cells, the AVERAGEA function only calculates the numeric values and skips the cells that have empty strings.
And when the function includes text values then it converts them as 0.
- How to Use SUMIFS Function in Excel (6 Handy Examples)
- Use SUMIF Function in Excel (With 5 Easy Examples)
- How to Use SUMPRODUCT Function in Excel (4 Examples)
- Use RAND Function in Excel (5 Examples)
- How to Use SEQUENCE Function in Excel (16 Examples)
6. Calculate Zeros with the AVERAGEA Function in Excel
Excel’s AVERAGEA function also counts the cells that contain zero (0) while calculating the average result of a group of values.
7. Blank Cells vs. Cells with Zeros with the AVERAGEA Function
Blank cells and the cells containing zeros – the AVERAGEA function counts them differently. While calculating, it skips the blank cells; and as for the cells with zeros(0), it passes the cells as arguments to get the arithmetic mean.
8. AVERAGEA Function to Calculate Logical Values in Excel
While calculating the Boolean functions, TRUE or FALSE, the AVERAGEA function counts TRUE as 1 and FALSE as 0. And based on that, it performs the arithmetic mean of the group of values.
9. Calculate Logical Values, Numbers and Text with the AVERAGEA Function
Logical values can also be calculated with numbers and text with the AVERAGEA function. It will take as parameters the numbers, text as 0 and 1 or 0 based on TRUE or FALSE.
10. AVERAGEA to Calculate All Types of Values (Numbers, Text, Blank Cells, Zeros and Logical Values)
What we have discussed till now, let’s summarize everything.
The AVERAGEA function counts,
- Numbers as numbers
- Text as 0
- Cell with Zeros as 0
- Doesn’t count Blank Cells
- Count TRUE =1 and FALSE = 0
This article explained in detail how to use the AVERAGEA function in Excel with 10 examples. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.