How to calculate an average in Excel: blanks, zeros, conditions and weighted averages

How do you calculate an average in Excel?

Type =AVERAGE( in an empty cell, select the cells with your numbers, close the bracket and press Enter. Excel adds up the numbers and divides by how many there are, so the average of 4, 8 and 9 is 21 divided by 3, which is 7. You can skip the typing, too: click below your column, then on the Home tab click the arrow next to AutoSum and pick Average (Microsoft).

=AVERAGE(B2:B9)

It's hardly an obscure function. In 2019 Microsoft called SUM and AVERAGE the two most used functions in Excel (Microsoft). Yet averages still go wrong, because a blank cell, a 0 and a number stored as text all count differently. Below you'll see how, on real sheets from our Excel course. Want to practise the basics first? Start with the lesson working out the average with AVERAGE.

How does the AVERAGE function work?

=AVERAGE(number1, [number2], ...)

Only number1 is required: the first number, cell or range. After that you can add more, up to a maximum of 255 according to Microsoft (Microsoft), and each argument can be a whole range. =AVERAGE(D2:D11,F2:F11) takes two columns together, and =AVERAGE(D2:E11) a block two columns wide. So it works across rows and columns alike, just like SUM.

Most English-language help separates the arguments with commas, but that comes from your settings, not from Excel itself or from Windows versus Mac. According to Microsoft, the list separator depends on the regional settings of your operating system and of Excel (Microsoft). Where the comma is the decimal mark, as in much of mainland Europe, you'll see =AVERAGE(D2:D11;F2:F11) instead.

How do you average a column in Excel?

Take the whole column as the range, blank cells included: AVERAGE skips what's blank by itself. Here's an example. Ruth, the chair of the Green Fingers allotments in Harrogate, weighs the harvest of ten plots every year. Green Fingers is the second client in our Excel course. Column D holds the kilos of tomatoes. Plot P04 is vacant and Luke grew no tomatoes on P09, so those two cells are blank.

=AVERAGE(D2:D11)

The formula returns 18.4375, which Excel shows as 18.44 kg in a column formatted to two decimals. That's 147.5 kg divided by 8, not by 10: AVERAGE skips the two blank cells. Work it out yourself with =SUM(D2:D11)/10 and you get 14.75 kg, as if P04 and P09 each grew 0 kg of tomatoes. But a plot without tomatoes didn't have a bad tomato harvest. It simply didn't have one.

Now try it yourself, on the real Green Fingers sheet, with the courgettes and beans as well. One formula is enough. Fill it to the right and the range moves along to the next column, and AVERAGE skips the blank cells in each column on its own.

Your formula ran in your own browser, and we then checked it on a second, hidden version of the sheet with different harvests. Type 18.44 instead of a formula, or divide by 10 yourself, and it might pass here but it gets caught there.

Does AVERAGE count blank cells, zeros and text?

Blank cells no, zeros yes, text no. Microsoft says that if a range contains text, logical values or empty cells, AVERAGE ignores them, while cells with the value zero are included (Microsoft). You can see the difference clearly on the member list of The Splash swimming pool in Tamworth, the third client in the course.

=AVERAGE(D2:D11) returns 9.78 visits: 88 divided by 9. Nothing's filled in for Laura, so she isn't counted. Sara never came, and that 0 does count. That's right, too: Sara has an annual pass and really didn't come. But if someone types a 0 for Laura because they don't know, the average drops to 8.8, even though nobody came less often.

For the ages, =AVERAGE(B2:B11) returns 36.44 years: 328 divided by 9. Hannah's 12 is stored as text, so it's skipped without any warning. As a number, the average would be 34. Not sure? Compare =COUNT(B2:B11) with =COUNTA(B2:B11): 9 numbers against 10 filled cells. You'll practise how COUNT works in the lesson counting with COUNT.

A zero you can't see still counts. Turn off "Show a zero in cells that have a zero value" in Excel's options and a cell holding 0 looks blank, but AVERAGE still includes it (Microsoft).

How do you average without zeros, or with a condition?

With AVERAGEIF, which averages only the numbers that meet a condition. Microsoft gives the no-zeros example itself, using "<>0", not equal to zero, as the criteria (Microsoft). At The Splash that returns 11 visits, 88 divided by 8, leaving out Sara and Laura's blank cell. Only use it when those zeros really shouldn't count.

=AVERAGEIF(D2:D11,"<>0")

AVERAGEIF takes three arguments: the range to test, the criteria, and the range to average. Leave out the third and Excel averages the first range (Microsoft). With all three, the Second Chance charity shop in Gloucester, the fourth client in the course, works out what an item of clothing fetched on average this week.

=AVERAGEIF(B2:B16,"Clothing",C2:C16)
=AVERAGEIFS(C2:C16,B2:B16,"Clothing",C2:C16,">5")

The first formula returns £7.67: six items of clothing that fetched £46 together. For more than one condition you use AVERAGEIFS, and there the range to average comes first (Microsoft). The second formula returns £12, the average of the clothing over £5. You write the same criteria when adding up with SUMIF and counting with COUNTIF. Your turn now, with the toys.

Average or median: which should you use?

Use the median when a few outliers skew your average. The median is the middle number once everything's sorted, and an outlier barely moves it. At Second Chance an item sold for £8.93 on average, yet ten of the fifteen items cost less than that. The £35 kids' bike pulls the average up.

=AVERAGE(C2:C16)
=MEDIAN(C2:C16)

The median is £6: seven items cost less and seven cost more. Leave the bike out and the average drops to £7.07, while the median only moves to £5.50, because with an even number of values MEDIAN averages the two in the middle. According to Microsoft, the average and the median are the same when the numbers are symmetrically distributed, and can differ when the distribution is skewed (Microsoft).

Which one you report depends on your question. If you want to know what a week brings in, you need the average, because average times count is the total. If you want to know what a typical item costs, the median says more. When in doubt, work out both: if they're far apart, there's an outlier in your data.

How do you calculate a weighted average in Excel?

Multiply each value by its weight, add up those products and divide by the sum of the weights. AVERAGE gives every number the same weight, and that isn't always what you're after. Take a Saturday evening at The Golden Chip in Whitby, the first client in the course.

=AVERAGE(B2:B9) returns £2.79, the average price on the menu. But what did a customer pay on average per item sold? Then every price should count as often as it went over the counter, so the mushy peas, sold 70 times, weigh more than the water, which sold 22 times.

=SUM(B2:B9*C2:C9)/SUM(C2:C9)

That returns £2.56: £919.20 in sales divided by 359 items sold. In Microsoft 365 you simply confirm the formula with Enter. In older versions it's a legacy array formula, which you confirm with Ctrl+Shift+Enter (Microsoft). Many people use SUMPRODUCT instead, as Microsoft does in its own weighted-average example (Microsoft): =SUMPRODUCT(B2:B9,C2:C9)/SUM(C2:C9).

Want to see every step? Use a helper column. Put =B2*C2 in D2, fill it down to D9 and divide with =SUM(D2:D9)/SUM(C2:C9). That works in every version, and you can see at a glance which product brought in the most: the small chips, with £179.20.

How do you round an average?

With ROUND: =ROUND(AVERAGE(D2:D11),1) turns the 18.4375 kg of tomatoes into a real 18.4. Showing fewer decimals with the formatting buttons is something else. The cell then looks rounded, but every formula that refers to it keeps calculating with 18.4375. The second argument is the number of decimal places, and 0 gives you whole kilos. You'll practise the difference in the lesson rounding with ROUND.

Why does AVERAGE return #DIV/0!?

Because there's nothing to average. An average is a sum divided by a count, and without a single number you're dividing by zero. Excel shows #DIV/0! when a number is divided by zero (Microsoft). With AVERAGE that happens when the range is empty or holds only text. AVERAGEIF also returns it when no cell meets the criteria (Microsoft).

Second Chance sold no furniture this week, so =AVERAGEIF(B2:B16,"Furniture",C2:C16) returns #DIV/0!. That's actually an honest answer: there's no average. If you'd still rather see some text on the sheet, say for a report you print, catch that one case with IF and COUNTIF:

=IF(COUNTIF(B2:B16,"Furniture")=0,"No sales",AVERAGEIF(B2:B16,"Furniture",C2:C16))

IFERROR is shorter, but Microsoft warns that it suppresses all errors, not just #DIV/0! (Microsoft). A typo in the formula would vanish too. With IF you only catch the case where there's nothing to average. You'll find how IF chooses between two results in the IF function explained.

Why is my Excel average wrong?

Usually AVERAGE calculates exactly what you ask, but the range holds something other than you think. You won't get an error message either: the number looks believable and is still wrong. Run through these four causes before you send it on:

• A 0 where nothing belongs. Leave an unknown value blank. If someone types a 0 for Laura, the average at The Splash drops from 9.78 to 8.8 visits.
• Numbers stored as text. AVERAGE skips them without a warning, like Hannah's age. Select the cells, click the alert button that appears beside them and choose Convert to Number (Microsoft).
• Hidden rows. AVERAGE uses every cell in the range, including rows you've hidden or filtered out. For only what's visible, use =SUBTOTAL(101,D2:D11). With 101, SUBTOTAL also skips rows hidden with the Hide Rows command, and it always leaves out rows a filter hides (Microsoft).
• An average of averages. At Second Chance, clothing averaged £7.67, books £2.50, household £8, toys £14.67 and electrical £15. The average of those five is £9.57, while an item sold for £8.93 on average: the single radio weighs as much as six items of clothing. Average the original numbers instead.

Which functions go with AVERAGE?

You'll find the best and the worst harvest at Green Fingers with MIN and MAX, in the lesson the biggest and the smallest. And if you want to know the other formulas you'll use every week, read 12 basic Excel formulas everyone should know.

Averaging is the first lesson of module 2 in our Excel course, at Green Fingers. You calculate on real sheets, with the function names and separator of your own Excel, and every formula is checked on its result. The first three modules are free.