Select Covariance from the list, and click OK.Įxcel displays the Covariance dialog box.Determining the linear association between number of press releases and weekly sales. To use Excel’s Covariance tool on the PR Releases & Sales spreadsheet shown in Figure 4-18, follow these steps: Figure 4-18. A positive covariance indicates a positive relationship, meaning that larger values of one variable tend to correspond with larger values of the other. You use covariance to measure the linear association between two sets of data. Cells E6 and F7 both hold the value 1, as a variable is always perfectly correlated with itself. The value in cell E7 is the correlation coefficient between press releases and sales. The cell listed in Figure 4-17 shows where Excel places the data table when you click the Output Range option button and enter D5 in the box. Alternately Click New Worksheet Ply to create a new worksheet in the current workbook for the data, or click New Workbook to create a new workbook for the data. Use the Output Options to describe the location you want for the Correlation data table.Ĭlick Output Range, and enter the upper left corner of the range where you want the data table to go.In the example, you would make sure the Columns option button is selected. In the example, you would enter A2:B11 in the Input Range box. Select Correlation from the list, and click OK.Įxcel displays the Correlation dialog box.
Determining the correlation coefficient between number of press releases and weekly sales. To use Excel’s Correlation tool on the PR Releases and Sales spreadsheet shown in Figure 4-16, follow these steps: Figure 4-16. Values near zero indicate lack or relationship, and values near +1 indicate a strong positive linear relationship. Values near –1 indicate a strong negative linear relationship.
Like covariance, a correlation coefficient also measures the linear association between two variables, but unlike covariance, correlation coefficients take values between –1 and +1. You can use Excel’s Correlation tool to create a table of Correlation coefficient data. Testing analysis of variance of populations.
To install the Analysis ToolPak, choose the Tools menu’s Add-Ins command, select the Analysis ToolPak check box, and click OK. This section describes the statistical data analysis tools that come with the Analysis ToolPak.