Microsoft Office Tutorials and References

In Depth Information

**Deviating from the Middle**

increases (or decreases), then data set B also increases (or decreases). A

negative value means that the two data sets tend to move in opposite

directions: When A increases, B decreases, and vice versa. The covariance’s

absolute value reflects the strength of the relationship.

When COVARIANCE.S or COVARIANCE.P returns 0, there is no relationship

between the two sets of data.

Sales of bread will likely create sales of butter; they’re somewhat related. In

other words, the amount of butter a store sells is likely to follow the amount

of bread it sells — more bread, more butter:

Day

Loaves of Bread Sold

Tubs of Butter Sold

Monday

62

12

Tuesday

77

15

Wednesday

95

26

As bread sales increase, so do sales of butter. Therefore, sales of butter are

expected to have a positive relation to sales of bread. These items

complement each other. In contrast, bread and muffins compete against each other.

As bread is purchased, the sales of muffins likely suffer because people will

eat one or the other. Without even using any function, you can conclude that

bread sales and butter sales move in the same direction and that bread sales

and muffin sales move in differing directions. But by how much?

Figure 9-12 shows an example that measures snowfall and the number of

customers coming into a store. Two covariance calculations are given — one

for snowfall between 0 and 3 inches, and one for snowfall between 0 and 8

inches.

Figure 9-12:

Using

COVARIANCE

to look for a

relationship

between

two data

sets.