Computer Tips From A Computer Guy

Calculate a Correlation Using OpenOffice Calc

Posted: 10 Jan 2011 03:29 AM PST

Among the applications included in the OpenOffice suite of productivity software, Calc is considered an excellent competitor of the more popular spreadsheet program Excel from Microsoft. Both programs come with a number of built in formulas and functions for making statistical, financial, mathematical, and even logical calculations. In this article, we demonstrate how to use Calc to calculate a correlation between two columns of data.

Use Calc to Calculate a Correlation

Suppose you have an OpenOffice Calc spreadsheet that looks like the one pictured below. You want to calculate the correlation coefficient between the data in Column A and the data in Column B. In addition, you want the coefficient to be displayed in cell C1.

To begin, click on cell C1 and then click on the Function Wizard button located next to the input line.

You should now be looking at the Function Wizard window. Click on the Functions tab on the left pane and select Statistical from the Category drop down menu. In the Function options pane, locate and click on the function labeled CORREL. Finally, click on the Next button located in the lower right hand corner of the window.

The Function Wizard window is your portal for accessing all of Calc’s functions. Unlike other spreadsheet programs such as Excel that use multiple windows to help you use the application’s functions, Calc uses just one.

So, when you click on the Next button, you should notice that only the right side of the Function Wizard changes; it changes so you can input the parameters specifically for the CORREL function.

To complete your correlation calculation, you need to fill in two pieces of information:

The location of the data in Column A
The location of the data in Column B

These data are labeled Data_1 and Data_2 on the Function Wizard window. You can use the button to the right of each input box to select the data directly in the spreadsheet. Since we know exactly where our data is located, we will simply type it in. In the box labeled Data_1, type in A1:A10 and in the box labeled Data_2, type in B1:B10.

You will notice that after you input the locations for the correlation calculation, Calc tells you some important information in the Function Wizard window. First, Calc tells you the actual formula that will reside in cell C1. You can view this formula in the Formula section of the Function Wizard window.

Second, Calc tells you what will be the result of the calculation in C1 based on the information you supplied above. Notice that in this case, the correlation of the data in Columns A and B will be 0.91 as indicated in the Result box on the Function Wizard window. This information can help save you time because you can see whether there is error in the way you input the information Calc needs to calculate the function.

When done, click on the OK button and notice that the number 0.91 is now in the C1 cell. Now we know that the correlation between the data in the two columns is 0.91. Notice that when cell C1 is active, you can view in the input line the formula that is used to calculate the correlation.

Using OpenOffice Calc, you can calculate the correlation between any two sets of data in your spreadsheet. Calc’s Function Wizard walks you through use of the CORREL function to calculate the correlation.

When finished, the wizard automatically puts the formula in the cell you specified so you can edit it directly in the input line and not have to use the wizard again to make changes to the formula.

Post from:

Calculate a Correlation Using OpenOffice Calc