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).
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.
In the below example, I have used arguments with different values.
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”.
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.
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.