Cellwise Try it free

Excel guide · 3 min read

How to calculate an average in Excel

Quick answerType =AVERAGE( then select the cells and close the bracket, for example =AVERAGE(D2:D8). AVERAGE adds the numbers and divides by how many numbers there are, skipping empty cells and text but including zeros. To leave out zeros use =AVERAGEIF(D2:D8,">0"), and to average one group use AVERAGEIF or AVERAGEIFS.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1RepRegionDealsRevenue
2AnaNorth1218400
3BenSouth913250
4CaraNorth00
5DevWest1522100
6ElifSouth
7FinnNorth79800
8GiaWest1116900

AVERAGE: the basic formula

This sheet shows each rep's revenue for the quarter. Cara joined late and closed nothing (0). Elif was on leave, so her row is empty. AVERAGE skips Elif because her cell is blank, but includes Cara's 0.

That makes the result the average over six people: 80,450 divided by 6. If 0 is a real result (Cara was working), this is the correct answer.

=AVERAGE(D2:D8)

Result: 13408.333333

13,408.33: the average of the six entered revenues, including Cara's 0.

Leave out zeros with AVERAGEIF

If a 0 means "no data" rather than "zero sales", it drags the average down unfairly. AVERAGEIF with ">0" averages only the positive values.

This is the single most common averaging question. Decide what a zero means in your data before you pick the formula: blank cells are already skipped, zeros are not.

=AVERAGEIF(D2:D8,">0")

Result: 16090

16,090: the five reps with revenue above 0.

Average by group: AVERAGEIF and AVERAGEIFS

AVERAGEIF(range, criteria, average_range) averages one column based on another. =AVERAGEIF(B2:B8,"North",D2:D8) gives 9,400, the average of Ana, Cara and Finn.

To add conditions, switch to AVERAGEIFS, which puts the average range first (same order as SUMIFS). Here North reps with revenue above 0, which drops Cara.

=AVERAGEIFS(D2:D8,B2:B8,"North",D2:D8,">0")

Result: 14100

14,100: the average of Ana's 18,400 and Finn's 9,800.

When the average misleads: use MEDIAN

A single very large or very small value pulls the average toward it. MEDIAN returns the middle value instead, which is often a better "typical" figure for salaries, house prices or delivery times.

Here the average with Cara's 0 is 13,408 but the median is 15,075. Report both if the gap is big; it tells your reader the data is skewed.

=MEDIAN(D2:D8)

Result: 15075

15,075: halfway between the two middle values, 13,250 and 16,900.

Quick check without a formula

Select the cells and look at the status bar at the bottom of the window. Excel shows the Average, Count and Sum of the selection. Right-click the status bar to add Min and Max.

For a formula that stays on the sheet, Home > AutoSum has an arrow with Average in the list, which writes =AVERAGE() with a guessed range for you to confirm.

  1. Select D2:D8.
  2. Read Average in the status bar.
  3. To insert it as a formula: click the cell below, then Home > AutoSum arrow > Average, and press Enter.

Common mistakes

Dividing SUM by COUNTA

=SUM(D2:D8)/COUNTA(A2:A8) divides by 7 people and gives 11,492.86, treating Elif's empty row as a zero. AVERAGE divides only by the numbers it found.

Typing 0 for missing data

A 0 is included in the average, a blank is not. Leave missing values empty, or use AVERAGEIF(range,"<>0").

Averaging averages

The average of two regional averages is wrong when the regions have different sizes. Average the raw data, or use a weighted average with SUMPRODUCT.

Numbers stored as text

AVERAGE skips text, so pasted numbers can silently drop out. If COUNT is lower than you expect, convert the column first.

FAQ

Does AVERAGE include blank cells?

No. Empty cells and text are skipped. Cells containing 0 are included.

How do I round an average to 2 decimal places?

Wrap it in ROUND: =ROUND(AVERAGE(D2:D8),2). Or keep the full value and use Decrease Decimal to show fewer digits.

Why does AVERAGE return #DIV/0!?

There are no numbers in the range, often because every value is text or every row failed the AVERAGEIF condition. Wrap it in IFERROR if an empty result is normal.

What is the difference between AVERAGE and AVERAGEA?

AVERAGEA counts text as 0 and TRUE as 1, so "absent" would pull the average down. AVERAGE ignores text. You almost always want AVERAGE.

Functions used

Related guides

Updated