Microsoft Office Tutorials and References

In Depth Information

**Deviating from the Middle**

In Figure 9-12, the first COVARIANCE measures the similarity of the amount

of snowfall with the number of customers, but just for 0 to 3 inches of snow.

The formula in cell G7 is =COVARIANCE.P(B5:B8,D5:D8). The answer is

–6.875. This means that, as snowfall increases, the number of customers

decreases. The two sets of data go in opposite directions. As one goes up,

the other goes down. This is confirmed by the result being negative.

The formula in cell G12 is =COVARARIANCE.P(B5:B13,D5:D13). This

examines all the values of the data sets, inclusive of 0 to 8 inches of snow. The

covariance is –47.7778. This, too, confirms that as snowfall increases, the

number of customers decreases.

However, note that the covariance of the first calculation, for 0 to 3 inches of

snow, is not as severe as the second calculation for 0 to 8 inches. When there

is just up to 3 inches of snow on the ground, some customers stay away —

but not that many. On the other hand, when there are 8 inches of snow, no

customers show up. The first covariance is comparably less than the second:

–6.875 versus –48.2222. The former number is closer to 0 — and tells you that

a few inches of snow don’t have much effect. The latter number is

significantly distanced from 0 — and, sure enough, when up to 8 inches of snow is

considered, customers stay home.

Note that the COVARIANCE.P function is used when using the data in rows

5 through 8 because those data points are being considered as a population

and not a sample of a population.

Here’s how to use the COVARIANCE.P function:

1. Enter two lists of numbers.

The lists must be the same size.

2. Position the cursor in the cell where you want covariance to appear.

3. Enter
=COVARIANCE.P(
to start the function.

4. Drag the pointer over the first list, or enter the address of the range.

5. Enter a comma (
,).

6. Drag the pointer over the second list, or enter the address of the range.

7. Enter a
)
and press the Enter key.

CORREL

The CORREL function works in the manner as COVARIANCE, but the result is

always between –1 and 1. The result is, in effect, set to a standard. Then the

result of one correlation can be compared to another.