What-is-the-formula-AverageA-and-when-to-use-it-in-Excel---image-by-Elisa-Wynd-from-unsplashIn one of my last tips I talked about the formula Average. Today I would like to explain what AverageA is and how to use this formula.

Average works out the average of numbers only! AverageA works out the average of anything!

What do I mean?

To work out an average of a set of numbers you add the numbers together then divide by how many numbers you have. Example1

The total amount is 389 but the average is 389/5 because there are 5 numbers in your set. This equals 77.8

We will use the Insert Function method to create the formula.

  • Select Fx button on the formula bar. Fx tool

This dialog box appears. Example1

  • Type “aver” in the first area in this dialog box and press enter.

This list appears. Example1

  • Select the function we need today. “AverageA”.

Underneath the list is an explanation of the function. averageA explained

You can use the following in the averageA function:

  • Numbers,
  • Arrays,
  • References,
  • Names,
  • Numbers as text,
  • Logical values such as TRUE or False. TRUE is counted as 1 and FALSE is counted as 0.
  • Arrays or references that contain text are counted as 0
  • Empty text is counted as 0
  • Text representations of numbers and Logical values typed directly into the list of arguments are also counted.


It is good to note that:

  • Arguments that are error values or text that cannot be translated into numbers and will cause an error.

Here is an example: Example 2

A mixture of numbers and text and empty cells.

I will compare calculations of Average and AverageA. Result

The results are quite different.

Therefore you need to make sure you are using the correct formula for the various examples you will be working with.

When you need to know how many times an item appears in a list in Excel

Linking spreadsheets in Excel 19

Using dates that automatically update in Excel


Please enter your comment!
Please enter your name here