Calculate the average of numbers

Microsoft Office Excel 2003

previous page next page
  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 Button image on the Standard toolbar, and then click Average, and then press ENTER.

ShowCalculate 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.

ShowHow?

  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

ShowCalculate 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.

ShowHow?

  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

ShowCalculate 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.

Show 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

previous page start next page