How to Calculate Average in Excel: AVERAGE, AVERAGEIF, and Why Yours Is Wrong

You selected the range, typed =AVERAGE, and got a number. The problem is that it does not match what you calculated by hand.

Learning how to calculate an average in Excel sounds trivial, right up until the result quietly disagrees with your own math. The function is not broken. Nine times out of ten the culprit is a blank cell, a hidden zero, a stray piece of text, or an "average of averages" that quietly skews the result.

This guide walks through AVERAGE from the ground up: how the range really behaves, why zeros and blanks are treated differently, how to average with conditions using AVERAGEIF and AVERAGEIFS, and how to fix the average-of-averages mistake that catches even experienced analysts.

AVERAGE Basics and How Ranges Really Work

The basic syntax

The base syntax is =AVERAGE(number1, [number2], ...). The first argument is required, and you can add up to 255 in total.

The most common form is a continuous range like =AVERAGE(B2:B11). Only the numbers inside the range are averaged, and you never have to count the cells yourself. The key thing to remember is that AVERAGE counts numbers only. If text is mixed into the range, it is ignored, which is one reason your result can differ from a manual calculation.

Non-contiguous ranges and multiple arguments

You can also average ranges that are not next to each other, such as =AVERAGE(A1:A5, C1:C5, E1). Note that AVERAGEIF and AVERAGEIFS cannot work with non-contiguous ranges directly, so when you need to filter, it is safer to keep your data in one continuous range.

The Number One Cause of Wrong Averages: Zeros vs Blank Cells

Blank cells are skipped, zeros are counted

This is the single most common reason an average looks wrong. Blank cells are excluded from the calculation, but zeros are included. Microsoft's own documentation confirms it: cells with a value of 0 are counted, while truly empty cells and text are ignored.

In the example above, six students have scores, but two cells are blank and one is a real 0. The version that excludes blanks averages four numbers (including the zero) and returns 63.75. The version that treats every blank as a zero divides by six and returns 42.5. Same data, completely different answer, just because of how blanks and zeros are counted.

How to exclude blanks (and zeros) cleanly

An empty cell and a real 0 mean different things, so it is worth distinguishing them at the data-entry stage. When you need to leave both out, AVERAGEIF is the cleanest approach. To average only cells greater than zero, use =AVERAGEIF(B2:B11, ">0"). It drops blanks and zeros at the same time, which is a pattern you will use constantly.

Conditional Averages with AVERAGEIF and AVERAGEIFS

AVERAGEIF for a single condition

AVERAGEIF averages only the cells that match one condition. The syntax is =AVERAGEIF(range, criteria, [average_range]). If you omit average_range, the range itself is averaged, so whenever the condition range and the value range are different, you must include the third argument.

For example, =AVERAGEIF(A2:A10, "Sales", B2:B10) averages the values in column B only for rows where column A is Sales. If no rows match the condition, you get a #DIV/0! error, so confirm the value actually exists in your data first. For conditional logic that goes beyond averaging, the IF function handles the same kind of criteria in a more general way.

AVERAGEIFS for multiple conditions

AVERAGEIFS lets you apply several conditions at once. The syntax is =AVERAGEIFS(average_range, criteria_range1, criteria1, ...), and you can specify up to 127 criteria pairs.

For instance, =AVERAGEIFS(C2:C12, A2:A12, "Sales", B2:B12, ">=80") averages Q1 scores only for the Sales department where the score is at least 80. The key is not to confuse the range you are averaging with the ranges you are testing. Also keep in mind that blank cells in a criteria range are treated as 0, so if the conditions look right but the answer seems off, check there first.

Why the Average of Averages Is Wrong (and the Right Way)

Four regional teams have average deal sizes, so you average those four numbers and call it the company average. The trouble is that the teams are not the same size, so that simple average does not reflect reality. This is the classic average-of-averages trap.

The answer is a weighted average. In Excel, the go-to method is SUMPRODUCT divided by SUM: =SUMPRODUCT(value_range, weight_range) / SUM(weight_range). You multiply each value by its weight, add those up, then divide by the total weight.

In the example, a plain =AVERAGE of the four regional averages returns 7875, but the weighted formula =SUMPRODUCT(B3:B6, C3:C6) / SUM(B3:B6) returns 7930.77. The bigger teams pull more weight, which is exactly what a true company-wide average should do. Whenever your groups are different sizes, use a weighted average so the data is not distorted. Once averages feel solid, the natural next step is measuring how spread out your data is with standard deviation.

Which Average Function Should You Use?

When in doubt, start with AVERAGE, move to AVERAGEIF or AVERAGEIFS the moment you need conditions, and reach for the SUMPRODUCT pattern whenever you are averaging groups that are not the same size.

Frequently Asked Questions

My AVERAGE result does not match my manual total. How do I check it?

Start with blank cells, text, and zeros. Selecting the range and reading the Average and Count values in the status bar together makes it easy to spot where the difference comes from.

How do I average a range that contains text?

AVERAGE ignores text and averages only numbers. If numbers are stored as text (like a "100" that is left-aligned), convert them to real numbers with the VALUE function or Text to Columns before averaging.

How do I fix a #DIV/0! error?

First confirm that data matching your condition actually exists. If you only want to clean up the display, wrap the formula in IFERROR, such as =IFERROR(AVERAGEIF(...), "N/A").

A Smarter Way to Average with inline AI

You now know why a careless average goes wrong. The harder part is catching those blanks, hidden zeros, and weighting traps on every report, every week, by hand.

That is exactly where inline AI helps. Ask it in plain English, like "average Q1 scores for the Sales team where the score is at least 80," and it picks the right function, gets the ranges right, and hands you the number in real time, with no fragile formula to debug later.

And because it runs locally on your PC, your data stays on your machine with nothing uploaded to the cloud, accuracy you can trust where it counts, ranking #1 on SpreadsheetBench with 92% accuracy.

Spend less time second-guessing your formulas and more time trusting the numbers they produce.