Excel’s AVERAGE function – the hidden pitfalls

AVERAGE Hidden Pitfalls

AVERAGE is one of those easy functions in Excel.  Like, super easy!  It has just one argument, the cells, or numbers to be averaged.  So what’s the problem?  It’s too easy to get wrong, yet we might not notice for weeks, months, years or ever.

Look at the screenshot below.  The result of each method for calculating an average is 9, which is the result we are expecting.

AVERAGE - Clean Data

You might be wondering why we need so many methods of calculating an average.  As we consider different data scenarios, these formulas will start to show different results.

Most of the formulas are simple enough to understand without explanation, but I will highlight a few of the more unfamiliar:

AVERAGEA or COUNTA functions

These functions include all the populated cells, rather than just the numerical cells.

Array formulas

The formulas with {Array} in their description are special formulas.  Enter these by pressing Ctrl + Shift + Enter, which adds curly brackets at the beginning and end.  Do not type the curly brackets; Excel will insert them itself.

These formulas perform a calculation on an array of cells before proceeding.  Look at the example below:


Each cell in the Range B2-B13 is multiplied by 1 before it proceeds to be averaged.  It is the multiplication of every cell within a single formula which makes it an array formula.

Click here to learn more about array formulas.

AGGREGATE function

The AGGREGATE function includes the ability to calculate an average whilst excluding specific elements, such as errors or hidden rows.

Click here to learn more about the AGGREGATE function.

Now let’s work through some scenarios to show how small changes in data can cause havoc with the results.

Download the 100 Excel Macros ebook

100 Excel Macros Book

  • Contains 100 Excel VBA macros
  • Learn VBA by following along with the example codes
  • Apply to your macros, automate Excel, save time.

Download the ebook today!

Click the button below to subscribe, you’ll gain access to the subscriber area in which you can download the ebook.

Download Icon (on mid-green background)

Problem #1 – Text included within the dataset

In the screenshot below, the dataset has changed; the text string of “None” has replaced the zero values.

AVERAGE - Text in Data

The result of the average formulas now includes a mix of values.

Formulas including COUNT, AVERAGE and AGGREGATE all calculate as 12, rather than 9, as these functions ignore text values.  As a result, the total divides by the cells containing numbers, rather than the total number of cells.

COUNTA and AVERAGEA include text cells within the calculation, therefore the calculation result remains at 9.

The AVERAGE { Array } function multiplies each value by 1 before applying the average.  Causing an error, as a text string cannot be multiplied by a number.

The AVERAGE IFERROR { Array } formula calculates the correct value of 9.  The IFERROR function calculates first, forcing any errors to zero, therefore when the average is applied no text values remain.

Problem #2 – Blank cells included within the dataset

In the screenshot below, the dataset has changed; the zeros from the original example are replaced with blank cells.

AVERAGE - Blank Cells

The result of the average formulas includes just two results 9 or 12.

COUNT, COUNTA, AVERAGE, AVERAGEA and AGGREGATE functions all exclude the blank cells in the cell count.  Causing these formulas to calculate incorrectly.

The AVERAGE { Array } and AVERAGE IFERROR { Array } formulas multiply the blank cells by 1, which forces them to zero values before calculating the average.  The calculation result of these formulas remains correct at 9.

Problem #3 – numbers formatted as text

In this final example, the value in Cell B4 is formatted as text.

AVERAGE - number as text

There are two potential issues here:

  • The total value may exclude the number formatted as text
  • The cell count to divide by may exclude the text cell.

COUNT, AVERAGE and AGGREGATE functions exclude the text within the cell count, causing an incorrect total to divide by 11, rather than 12 cells (i.e., both issues have occurred)

COUNTA, AVERAGEA functions will include the text when calculating the cell count, but not in the total.  Therefore, the wrong value is divided by the correct number of cells (i.e., the first issue has occurred)

The two { Array } functions calculate the correct value.  Any number formatted as text multiplies by 1, converting the text to a number; maintaining the total value and cell count.

Comparison of all formulas

How well did each formula perform across all the scenarios?

AVERAGE - The Results

There is only one formula which calculated the correct result across all scenarios, the AVERAGE/IFERROR { array } formula.

It is worth noting that, in this post, I have made a significant assumption.  I have assumed we want to calculate the same result as the original data.  But what if we want to exclude blank cells or text values?  The array function which calculated correctly no longer meets our requirements.  We would need to pick another formula.

There is no perfect formula solution for all scenarios as so much depends on user intent.


There are many ways to calculate an average. Different formulas will fit with different datasets and scenarios.  The critical thing is to know your data and exactly what you want to achieve.

If you do not have control over the dataset, consider adding a check formula.  For example, the ISNUMBER formula will calculate to TRUE if all the cells in the dataset are numbers, otherwise it will calculate to FALSE.  Adding the IF function, the formula below would display “Text included in dataset” if any cell in the range B2-B13 were not a number.

=IF(ISNUMBER(B2:B13),AVERAGE(B2:B13),"Text included in dataset")

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

2 thoughts on “Excel’s AVERAGE function – the hidden pitfalls

  1. Sima says:

    I tried the following formulas and I still get #div/0!

    I need to calculate the average handle time (for calls) and there are rows that show – in the exported data.

    Any ideas, why the formulas doesn’t work?

    Thanks for your help!

Leave a Reply

Your email address will not be published. Required fields are marked *