Excel Tutorials
Averaging a column is one line of Excel, and the answer is wrong more often than people realise, because a blank cell and a zero produce two different results from the same data. This lesson covers AVERAGE and its variations, exactly how empty cells are treated, weighted averages, and when the median is the more honest number. It uses the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
AVERAGE adds up the numbers in a range and divides by how many there are, giving you the arithmetic mean. =AVERAGE(D2:D20) is the whole thing. It is one of the first functions anyone learns, and one of the easiest to get a misleading result from.
The catch is what counts as "how many there are". AVERAGE deliberately skips blank cells, text and logical values. A blank is not a zero, it is a row that does not exist as far as the calculation is concerned. So a column of ten cells with two left empty averages eight values, not ten, and swapping those blanks for zeros changes the answer significantly.
That is not a bug, it is a decision you have to make about your data. A blank means "no data submitted" and should be excluded. A zero means "genuinely nothing" and belongs in the calculation. The rest of this page covers that distinction, along with conditional averages, weighted averages, and why the median is sometimes the better number to put on a report.
Step-by-step, matching the video above.
Usually directly below or beside the column of numbers. Keep it outside the range you are averaging, because including the answer cell in its own range creates a circular reference.
=AVERAGE(D2:D20). Drag across the cells or type the reference. Excel adds up everything in the range and divides by how many numeric values it found.
On the Home tab far right, or the Formulas tab, click the small arrow beside AutoSum and choose Average. Excel guesses the range above your cursor, so always check what it highlighted before pressing Enter.
Separate them with commas: =AVERAGE(D2:D10,D15:D20). You can mix ranges and individual cells, and Excel treats the whole lot as one pool of numbers.
Select any range of numbers and the status bar along the bottom shows Average, Count and Sum without writing a formula at all. Right-click it to add Min and Max to that display too. Perfect for a one-off check you do not need to keep.
It skips blank cells, text and logical values entirely. A blank cell is not counted as a zero, which is the single most important behaviour to understand and the subject of the section below.
=ROUND(AVERAGE(D2:D20),2) genuinely rounds the value to two decimals. Decreasing the decimal places from the ribbon only changes how it looks, leaving the full precision underneath, which is why totals sometimes appear not to add up.
If nothing in the range is numeric, AVERAGE returns #DIV/0!, since it cannot divide by zero values. Wrap it: =IFERROR(AVERAGE(D2:D20),"No data").
This one behaviour causes more wrong averages than everything else combined.
A blank cell is not treated as zero. It is excluded from both the total and the count, as though the row were not there. Ten cells with two blanks gives you the average of eight values.
An actual 0 is a real value, so it is added to the total and increases the count. This is why entering 0 rather than leaving a cell empty produces a completely different result on the same data.
If a salesperson sold nothing, that is a genuine 0 and belongs in the average. If their figure has not been submitted yet, that is a blank and should be excluded. Getting this backwards misrepresents performance in one direction or the other.
A formula ending ,"") looks blank but returns empty text, not a blank cell. AVERAGE ignores text, so it behaves like a blank here, but other functions will not agree, which is a common source of confusion.
=AVERAGEA(D2:D20) includes text as 0 and FALSE as 0, TRUE as 1. It is rarely what you want, and it is worth knowing mainly so you can recognise it when someone else's sheet returns a suspiciously low number.
| Formula | What It Does | Result |
|---|---|---|
| =AVERAGE(D2:D6) | Averages 5 values, one blank | 362.50 |
| =AVERAGEA(D2:D6) | Counts the blank-looking text as 0 | 290.00 |
| =AVERAGE(D2:D6,0) | Adds a real zero to the pool | 290.00 |
| =MEDIAN(D2:D6) | Middle value, ignores the outlier | 310.00 |
| =AVERAGEIF(B2:B6,"Gold",D2:D6) | Average of Gold tier only | 483.33 |
| =ROUND(AVERAGE(D2:D6),0) | Rounded for a report | 363 |
| =AVERAGE(F2:F6) | Range with no numbers at all | #DIV/0! |
Rows one and three are the same data producing different answers purely because of how an empty cell is handled, which is the whole point of the section above. Row four is worth its own attention: when one large value skews the set, the median is often the more honest number to report, and quoting an average without checking it against the median is how a single outlier ends up defining your entire summary.
Four variations that answer better questions than a simple mean.
=AVERAGEIF(B2:B50,"Gold",D2:D50) averages only the rows matching a condition. AVERAGEIFS handles several conditions at once, with the average range moving to the front.
=MEDIAN(D2:D20) returns the middle value. On salary, order value or response time data, where a handful of extremes pull the mean upward, the median describes the typical case far better.
=SUMPRODUCT(D2:D20,E2:E20)/SUM(E2:E20) weights each value by a quantity in column E. Averaging unit prices without weighting by quantity sold gives a number that describes nothing real.
=TRIMMEAN(D2:D20,0.2) drops the top and bottom 10% before averaging. Common in scoring and survey work where a couple of extreme responses should not carry the result.
Three routes to the same function.
Home tab, far right, or Formulas > AutoSum. Click the arrow, not the button, and choose Average. The plain button inserts SUM.
Formulas > More Functions > Statistical lists AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS, MEDIAN and TRIMMEAN together, each with a description on hover.
Select the range and read it off the bottom of the window. Right-click the status bar to choose which of Average, Count, Numerical Count, Min, Max and Sum are shown.
They do not. AVERAGE excludes them from both the sum and the count, so a column with gaps averages fewer values than rows. Enter a real 0 if the value genuinely is zero.
=AVERAGE(D2:D21) where D21 is a SUM of the rows above roughly doubles the result and looks plausible enough to go unnoticed. Stop the range one row short of any total.
Averaging D2:D20 in cell D20 creates a circular reference. Excel warns you, but on a large sheet the warning is easy to dismiss without reading.
Imported figures formatted as text are skipped entirely, so the average is calculated over a smaller set. Check the Count on the status bar against your row count, and convert with Text to Columns if they disagree.
A single very large value drags the mean somewhere unrepresentative. Compare against MEDIAN before publishing the number, and use the median if the two are far apart.
The average of several percentages is not the overall percentage unless every group is the same size. Use SUMPRODUCT divided by SUM to weight them properly.
That only alters the display. The underlying number keeps its full precision, which is why a column of rounded-looking figures can appear not to add up. ROUND changes the value itself.
It means nothing in the range was numeric. On a template where data arrives later, wrap it in IFERROR so the sheet does not look broken before it has been filled in.
Recalculating the same average by hand each month means the number is stale the moment you finish.
Free 14-day trial. No credit card required.
Use =AVERAGE(range), for example =AVERAGE(D2:D20). Excel totals the numeric values in that range and divides by how many it found.
Yes. Blank cells are excluded from both the total and the count, so they are not treated as zeros. This means a column with gaps averages fewer values than it has rows.
Enter an actual 0 in those cells, or use AVERAGEA, which treats text as zero. There is no setting that makes AVERAGE itself read blanks as zeros.
AVERAGE only considers numbers. AVERAGEA also includes text as zero and logical values as 0 or 1, which usually lowers the result.
Nothing in the range was numeric, so Excel had no values to divide by. Wrap it in IFERROR to show a message instead.
Use AVERAGEIF with a condition, such as =AVERAGEIF(B2:B50,"Gold",D2:D50). AVERAGEIFS handles more than one condition.
Divide SUMPRODUCT by SUM: =SUMPRODUCT(D2:D20,E2:E20)/SUM(E2:E20), where column E holds the weights such as quantity.
When a few extreme values are pulling the mean away from what is typical. The median reports the middle value and is unaffected by outliers.
Separate them with commas inside one function, as in =AVERAGE(D2:D10,D15:D20). You can mix ranges and single cells.
Yes. Select the range and the status bar at the bottom of the window shows Average, Count and Sum. Right-click it to add Min and Max.
Wrap it: =ROUND(AVERAGE(D2:D20),2). Using Decrease Decimal on the ribbon only changes the display, not the stored value.
Click the arrow beside AutoSum on the Home or Formulas tab and choose Average. It is also under Formulas, More Functions, Statistical.
Every business starts with a spreadsheet. Updoot is where you scale past it, records link themselves automatically.
Start Your Free Trial →