Over 2 million + professionals use CFI to learn accounting, financial analysis, modeling and more. Unlock the essentials of corporate finance with our free resources and get an exclusive sneak peek at the first module of each course.
Start Free
What is the AVERAGEA Function?
The AVERAGEA Function[1] in Excel is a Statistical function. The function calculates the average of a group of supplied values.
It differs from the AVERAGE function, as it evaluates the logical values TRUE and FALSE, and numbers represented as text, whereas AVERAGE just skips these values during calculation. The AVERAGEA function was introduced in MS Excel 2007 and is not available in older versions.
Formula
=AVERAGEA(value1, [value2], …)
Where:
Value1 is a required argument. However, subsequent values are optional.
Values that are supplied can be numbers, ranges, named ranges, or cell references that contain values. Up to 255 arguments can be supplied.
How to use the AVERAGEA Function in Excel?
It is a built-in function which can be used as a worksheet function in Excel. To understand the uses of this function, let’s consider a few examples:
Example 1
With the arguments given below, let’s see how AVERAGEA function is used:
As we can see in the above example:
As shown in C5 – A cell with text in it would be considered as 0.
As shown in B6 & B7 – A cell with FALSE in it is considered as 0 and TRUE is considered as 1.
All cells with numbers would be taken as their actual numbers.
The average value returned by the formula is 66666667. The formula returned is “(0+1+0+25+45+65)/6”.
Example 2
Let’s see a few more examples to understand the behavior of this function:
As seen in the table above, we can see how each argument behaves when we apply the AVERAGEA function. The function can be supplied as a single number or cell, or as an array of numbers or cells.
The results are below:
Differences between AVERAGE and AVERAGEA Functions
Particulars
AVERAGE Function
AVERAGEA Function
If we use logical values or text representations of numbers that are typed directly into the list of arguments
They are counted as (TRUE=1, FALSE=0)
They are counted as (TRUE=1, FALSE=0)
Text that cannot be interpreted as a number and are typed directly into the list of arguments
#VALUE! error
#VALUE! error
When we entered logical values within arrays or reference arguments
Ignored by this function
They are counted as (TRUE=1, FALSE=0)
When we enter text that includes empty text "", text representations of numbers, or other text within arrays or reference arguments
Ignored by this function
The function counts it as zero
Empty cells
Ignored by this function
Ignored by this function
Things to remember in AVERAGEA Function:
AVERAGEA evaluates TRUE as 1 and FALSE as zero.
The arguments can be numbers, names, arrays, or references that contain numbers; text representations of numbers; or logical values, such as TRUE and FALSE, in a reference.
Arguments that contain TRUE are evaluated as 1 and arguments that contain FALSE are evaluated as 0 (zero).
If an argument is an array or reference, only values in that array or reference are used. Empty cells and text values in the array or reference are ignored.
The formula will return errors when the arguments are error values or text that cannot be translated into numbers.
If we do not wish to include logical values and text representations of numbers in a reference as part of the calculation, it would be better to use the AVERAGE function.
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:
To master the art of Excel, check out CFI’s Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Excel Crash Course now to take your career to the next level and move up the ladder!
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.