How to use Excel AVERAGEA Function

    how to use excel averagea function

    Quick Intro

    Use Excel AVERAGEA Function to average numbers, logical values, and numbers which are stored as a text.

    In simple words, It will calculate the average for the number, logical values (TRUE or FALSE) and number which are stored as text(in double quotes).

    Syntax

    AVERAGEA(value1,value2,…)

    • value1  A value which is a number, a  logical value or a number which is stored as a text.
    • [value2] A value which is a number, a logical value or a number which is stored as a text.

    More Information on Excel AVERAGEA Function

    • The difference between AVERAGE & AVERAGEA is that AVERAGEA also considers text and logical values while calculating the average.
    • Logical value TRUE will consider as 1 and FALSE will consider as 0.
    • If you enter a text directly into the function it will return #VALUE! & if you refer a cell which has a text, it will treat that text as 0.
    • If you enter a number directly into the function using double quotation marks, it will ignore that quotation marks & take it as a number. But if you refer to a cell which has a number formatted as text, it will consider that argument as 0. You can change the number format in that case.
    • If any of the specified arguments is an error, it will return an error in the result.

    Examples

    In the below example, I have used arguments with different values.

    Examples For Excel AVERAGEA Function

    • A cell which has text in it is considered as 0.
    • A cell which has FALSE in it is considered as 0 & TRUE is considered as 1.
    • All cells which have numbers in it as taken as their actual numbers.

    And, value returned by the function is 10.17 which is “(0+0+1+10+20+30)/6”.

    Examples For Excel AVERAGEA Function Using Arguments Directly Into The Function

    In above example, I have inserted values directly into the function.

    • A number which I have inserted using double quotation marks, excel take it as a number.
    • TRUE is considered as 1 & FALSE is considered as 0.

    You can use AVERAGE Function where you have values other than numbers in your data.

    Sample File

    download sample file to learn more about this tips

    What’s Next?

    To learn more about Excel AVERAGEA Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.

    There is also you have an AVERAGE function which is highly useful. Apart from this, I have a list of excel functions and some real life formulas examples.