Microsoft Excel Functions & Formulas

Use the AVERAGE and IF functions to calculate

the average of a range, taking zero values

into consideration

Normally Excel calculates the average of a range without consider-

ing empty cells. Use this tip to calculate the correct average when

some values in a range are missing. As in the previous example, we

use the IF function to take care of the zero values. Combine the

AVERAGE and IF functions in an array formula to obtain the cor-

rect average of all listed costs.

4

To calculate the average of a range, taking zero values into

consideration:

1. In cells A2:A13 list the months January through December.

2. In column B list monthly costs down to row 7.