How to Perform Analyses of Variance in Excel
How to Enter a Function in Excel with the Function Command
How to Import Delimited Text Files in Excel 2013

How to Use the Covariance Analysis Tool in Excel

The Covariance tool, available through the Data Analysis add-in in Excel, quantifies the relationship between two sets of values. The Covariance tool calculates the average of the product of deviations of values from the data set means.

To use this tool, follow these steps:

1

Click the Data Analysis command button on the Data tab.

The Data Analysis dialog box appears.

2

When Excel displays the Data Analysis dialog box, select the Covariance tool from the Analysis Tools list and then click OK.

Excel displays the Covariance dialog box.

3

Identify the range of X and Y values that you want to analyze.

To look at the correlation between ads and sales data, enter the worksheet range $A$1:$B$11 into the Input Range text box.

Select the Labels in First Row check box if the input range includes labels in the first row.

Verify that the Grouped By radio buttons — Columns and Rows — correctly show how you've organized your data.

4

Select an output location.

Use the Output Options radio buttons and text boxes to specify where Excel should place the results of the covariance analysis. To place the results into a range in the existing worksheet, select the Output Range radio button and then identify the range address in the Output Range text box. If you want to place the results someplace else, select one of the other Output Options radio buttons.

5

Click OK after you select the output options.

Excel calculates the covariance information for the data that you identified and places it in the specified location. Here are the covariance results for the ads and sales data.

blog comments powered by Disqus
How to Run the Excel PivotTable Wizard
How to Use the Custom AutoFilter on an Excel Table
How to Set Pivot Table Options in Excel
How to Use Data Validation to Keep Excel Data Clean
Set Up and Define Problems in Excel's Solver Add-In Utility
Advertisement

Inside Dummies.com