The AVERAGEA Function in excel helps us to find out the exact average of the values we want. 

We all know the importance of average in financial world. At some level every organization expects average of particular values.

Previously In Excel Formulas: How to use AVERAGEIFS Function in Excel?

So, to understand the following function, let’s take an example for the same:


As you can observe in the image above, we have taken product color in relation to its availability in stock.

Step1: Input the Formula


The formula for the same is 


Step2: Input the values in the formula

As you can observe in the formula, a small box appears right below it expecting value 1, [value 2], and so on…

Select those values from the data (of which you want your average result to be). 


As you can observe in the image above, we have selected the values. Now all we have to do is press “Enter”.


And BAMMM, here’s your answer: The average of the input values is 20.33333.

You must be wondering for what’s the difference between the AVERAGE function and the AVERAGEA function.


As you can observe in the above, there is a value present as “YES”. 

The difference between average and averagea is that the average does not accept non-numeric values but averagea accepts the same. 

So, the average of 23, 44, 10, 11, 22 is 22. The AVERAGE function ignores the non-numeric value i.e. it is ignoring “YES” and so it is taking on the basis of 5 values.

But as there are 6 values in total, so AVERAGEA takes our average on the basis of 6 values even if one of the values is non-numeric value.


It’s easy, isn’t it?