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