Finding the Mean With AVERAGE

An average (also called the mean) tells you the typical value in a group of numbers. You add the numbers up and divide by how many there are.

For example, test scores of 80, 90, and 100 add up to 270. Divide by 3 and the average is 90.

You could do this by hand, but the AVERAGE function does both steps for you in one short formula.

AVERAGE follows the same shape as SUM:

=AVERAGE(range)

Point it at a range of cells and it returns the mean of the numbers inside. It quietly handles the adding and the dividing.

Just like SUM, it skips text and blank cells, so only real numbers are counted.

Suppose cells C2 through C4 hold three test scores:

• C2 = 80
• C3 = 90
• C4 = 100

The formula =AVERAGE(C2:C4) returns 90. Behind the scenes it added 80 + 90 + 100 to get 270, then divided by 3.

If you change C4 to 70, the average recalculates to about 80 right away.

Under the hood, AVERAGE is really two functions combined:

=SUM(range)/COUNT(range)

SUM totals the numbers and COUNT counts how many numbers there are. AVERAGE does both for you in one step.

Knowing this helps you understand why blank cells matter: they are not counted, so they do not pull the average down.

Here is a key detail: a blank cell and a zero are treated differently.

• A blank cell is ignored entirely, as if it is not there.
• A zero is a real number and gets included in the count.

So 80, 90, and a blank average to 85, but 80, 90, and 0 average to about 56.7. Decide whether missing values should be blanks or zeros before you build your formula.

As with SUM, you can average an entire column for a growing list:

=AVERAGE(E:E)

This finds the mean of every number in column E, including rows you fill in later. Headers and blank rows are ignored automatically.

Place this formula outside column E to avoid a circular reference.

A class of six students scored 72, 88, 95, 60, 100, and 85, stored in F2 through F7.

The formula =AVERAGE(F2:F7) returns 83.33. That single number tells the teacher the class's typical performance at a glance.

If a seventh student is added in F8, simply extend the range to =AVERAGE(F2:F8) and the mean updates.

AVERAGE works across rows too. If a row holds monthly sales from B2 to M2, find the monthly average like this:

=AVERAGE(B2:M2)

This adds the twelve values and divides by twelve, giving the typical month's figure.

The colon range works in any straight line, so rows and columns are both fair game.

The average is useful, but one extreme value can distort it. If salaries are 30, 32, 31, and 200 (thousands), the average jumps to about 73, even though most people earn around 31.

That single large number is called an outlier. When data has outliers, the average may not reflect the typical case well.

Later you will meet MEDIAN, which is more resistant to outliers, but for everyday balanced data AVERAGE is perfect.

AVERAGE works identically in Microsoft Excel and Google Sheets. The syntax =AVERAGE(range) is the same in both programs.

You can build a sheet in one and open it in the other without changing the formula.

Like SUM, AVERAGE is a core function that you will reach for constantly, so it is worth getting comfortable with now.

Two pitfalls to avoid:

• Including a total cell. If your range accidentally covers the SUM total at the bottom, the average will be wildly wrong.
• Mixing zeros and blanks unintentionally. Remember zeros count but blanks do not, which changes the result.

Double-check exactly which cells your range covers before pressing Enter.

Test your understanding of the AVERAGE function.

You learned to find the mean of a range:

• Use =AVERAGE(range), for example =AVERAGE(C2:C4).
• It equals SUM(range)/COUNT(range) done in one step.
• Blank cells are ignored, but zeros are counted.
• Watch out for outliers, which can pull the mean away from the typical value.

Next, you will combine separate ranges and single cells inside one SUM formula.