AVERAGE vs AVERAGEA in Excel

The short answer to AVERAGE vs AVERAGEA: AVERAGE averages only the numbers in a range, while AVERAGEA also counts text as 0 and TRUE/FALSE as 1 and 0. Both skip empty cells. That one difference changes the result whenever your range holds words like "Absent" or "N/A", so this guide shows both on the same data and explains when each one gives the answer you actually want.

AVERAGE vs AVERAGEA at a glance#

The functions treat numbers, zeros and empty cells the same way. They differ on text and TRUE/FALSE values inside the range.

How AVERAGE works#

AVERAGE adds the numbers in its arguments and divides by how many numbers there are. Anything in a cell that isn't a number is skipped, as if it weren't there.

=AVERAGE(B2:B7)

Think of it as SUM(B2:B7)/COUNT(B2:B7). COUNT counts only cells with numbers. If there are no numbers at all, AVERAGE returns #DIV/0!, because it would be dividing by zero. The AVERAGE function guide covers the arguments, and the guide to AVERAGE, MIN and MAX shows it alongside its everyday partners.

One detail: values you type straight into the formula are treated differently from cells. =AVERAGE(10,TRUE) counts TRUE as 1 and returns 5.5. Inside a range, TRUE is skipped. AVERAGEA follows the same rule for typed values, so the difference between the two only appears with ranges and cell references.

How AVERAGEA works#

AVERAGEA averages every non-empty cell in its range. The "A" stands for "all": it gives every value a number.

• Numbers count as themselves.
• Text, including numbers stored as text, counts as 0.
• TRUE counts as 1 and FALSE as 0.
• Truly empty cells are still ignored.

=AVERAGEA(B2:B7)

Think of it as the total divided by COUNTA(B2:B7), the number of non-empty cells. That is why AVERAGEA is usually lower than AVERAGE when the range holds text: the text adds nothing to the total but adds to the count.

The same data, both ways#

Here are six students' test scores. Cara missed the test, and Eli's score hasn't been entered yet:

Both ignore Eli's empty cell. The only difference is Cara. AVERAGE leaves her out, so the class average is 75. AVERAGEA gives her a 0, so it is 60. Which one is right depends on your rule for missed tests. The COUNT vs COUNTA lesson explains why the two counts differ.

TRUE/FALSE values#

AVERAGEA has a second use. Say C2:C5 holds a "Completed" checklist: TRUE, FALSE, TRUE, TRUE.

Format the AVERAGEA cell as a percentage and it reads 75%: the share of tasks completed. The same works for a column of checkboxes in Microsoft 365, which store TRUE and FALSE.

When to use AVERAGE#

Use AVERAGE for almost every average of numbers. It's the right choice when:

• The column holds only numbers, such as sales, prices or hours. Both functions give the same result here, and AVERAGE is the one people expect to see.
• Text means "no data", such as "N/A", "TBC" or a dash. You don't want those rows to drag the average down.
• You want to leave out some rows on purpose. For conditions, AVERAGEIF and AVERAGEIFS are clearer. For example, =AVERAGEIF(B2:B7,">0") averages only positive scores; see how to average without zeros.
• A test or interview question asks for "the average". It means AVERAGE unless it says otherwise.

When to use AVERAGEA#

Use AVERAGEA when non-number entries should count. It fits these cases:

• Text should count as zero. "Absent" on a test, "No sale" for a day or "Missed" for a payment should lower the average, so AVERAGEA is the honest choice.
• You want the share of TRUE values. =AVERAGEA(C2:C5) turns a TRUE/FALSE column into a completion rate in one step.
• Every row must be in the count. If your rule is "divide by everyone who has an entry", AVERAGEA does it without a separate COUNTA.

If only some text should count as zero, it is clearer to replace that text with 0 in the data, or to write the rule out with SUM and COUNTA so readers can see it.

Common mistakes with AVERAGE and AVERAGEA#

These slips give averages that look reasonable but are wrong:

1. Numbers stored as text. An imported "85" that is really text is skipped by AVERAGE and counted as 0 by AVERAGEA. Neither is correct. Convert the cells to numbers first.
2. Cells that only look empty. A cell with a space, or a formula that returns "", is not empty. AVERAGE skips it, but AVERAGEA counts it as 0 and lowers the result.
3. Blank instead of zero (or the reverse). Both functions ignore empty cells but include 0. If a missed sale should count, enter 0. If it shouldn't, leave the cell empty.
4. Expecting AVERAGEA to fix #DIV/0!. AVERAGEA only helps if the range holds text or TRUE/FALSE. A truly empty range still returns #DIV/0!. Use =IFERROR(AVERAGE(B2:B7),0) or show a message instead.
5. Averaging percentages or rates directly. A simple average treats a store with 10 sales like one with 1,000. Use a weighted average instead; see the guides to the weighted average and averaging percentages correctly.
6. Using the average when the data has outliers. One huge value pulls any average up. Compare it with the median; the article on AVERAGE vs MEDIAN shows when each tells the truth.

Our recommendation#

Use AVERAGE by default. It does what most people mean by "average", it's the function everyone recognises, and it ignores notes like "N/A" that shouldn't affect the result.

Switch to AVERAGEA only when a text or TRUE/FALSE entry has a real numeric meaning: "Absent" should be 0, or you want the share of TRUE values. When you do, add a short note next to the cell, because many readers don't know AVERAGEA counts text as zero. If the rule is unusual, SUM/COUNTA or AVERAGEIF can make your intent clearer than either function.

In short: AVERAGE for numbers, AVERAGEA when words should count as zero.

Practise it#

Averages are easy to type and easy to get subtly wrong. Practise AVERAGE, MIN and MAX in a live spreadsheet to build the basics, then take the COUNT and COUNTA lesson to see exactly which cells each count includes. Every answer is checked instantly. Start with the exercise on this page: rebuild the AVERAGEA result with SUM and COUNTA.