Calculate the average of numbers

The average is also called the mean.

Calculate the average of numbers in a contiguous row or column

  1. Click a cell below or to the right of the numbers for which you want to find the average.
  2. Click the arrow next to AutoSum on the Standard toolbar, and then click Average, and then press ENTER.

Calculate the average of numbers not in a contiguous row or column

Use the AVERAGE function to do this task.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

How?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
3
4
5
6
7
A
Data
10
7
9
27
0
4
Formula Description (Result)
=AVERAGE(A2:A7) Averages all of numbers in list above (9.5)
=AVERAGE(A2:A4,A7) Averages the top three and the last number in the list (7.5)
=AVERAGE(IF(A2:A7<>0, A2:A7,"")) Averages the numbers in the list except those that contain zero, such as cell A6 (11.4)

Note The last formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the cell A11. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the error #VALUE! is returned.

Function details

AVERAGE

Calculate a weighted average

Use the SUMPRODUCT and SUM functions to do this task.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

How?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

This example calculates the average price paid for a unit across three purchases, where each purchase is for a different number of units at a different price per unit.

 
1
2
3
4
A B
Price per unit Number of units
20 500
25 750
35 200
Formula Description (Result)
=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4) Divides the total cost of all three orders by the total number of units ordered  (24.66)

Function details

SUM

SUMPRODUCT

Calculate the average of numbers, ignoring zero (0) values

Use the AVERAGE and IF functions to do this task.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

How?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
3
4
5
6
7
A
Data
10
7
9
27
0
4
Formula Description (Result)
=AVERAGE(IF(A2:A7<>0, A2:A7,"")) Averages the numbers in the list except those that contain zero, such as cell A6 (11.4)

Note The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the cell A9. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the error #VALUE! is returned.

Function details

AVERAGE

IF