Show All
Calculate the average of numbers
The average is also called the mean.
Calculate the average of numbers in a contiguous row or column
- Click a cell below or to the right of the numbers for which you want to find the average.
- 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?
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- 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.
|
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?
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- 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.
|
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?
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- 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.
|
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