Find Correlation Between Datasets In Excel 2010

Excel 2010 supports a multitude of statistical functions that bring ease for calculating statistical data promptly. Amongst Stats functions, finding a correlation manually between two given datasets can be cumbersome, but with built-in Excel 2010 CORREL function, finding co-relation coefficient is a cinch. The function lets you easily calculate co-relation between any two given data sets or set of values. You need to specify the data range or values, it instantly shows you the co-relation between them. You can quickly apply applied CORREL function over required data sets by just copying the cell containing the CORREL function. All it requires is two simple arguments to be filled in the form of arrays. The Correlation values range from –1.0 to +1.0, implying +1.0 as best co-relation between datasets and converse is the case with –1.0.

To begin, launch Excel 2010 and open a spreadsheet you want to use the co-relation. For instance we have created two fields namely Values 1 and Values 2.

Correlation resultant values ranges from –0.1 to +0.1, where +1.00 being the best positive relationship you can get. We will find out coefficient correlation between above mentioned data values field.

To start off with, first you need to familiar with the following syntax.

=CORREL(array1,array2)

array1 in function argument is the dataset 1 and array2 refers to dataset 2. Under Values 1 and Values 2 fields, we will add a label namely Correlation Coefficient.

coefrfice

We will write the CORREL with the following data ranges.

=CORREL(A2:A11,B2:B11)

In function argument, A2:A11 is the range of dataset 1, in mentioned spreadsheet that is Values 1 field, and B2:B11 is the range of field Values 2. We will be putting this formula underneath the Correlation Coefficient label. This will instantly show the result for values residing in both fields.

yields

The result 0.299 shows, which is not a perfect correlation between fields but not that much negative as it is in the range of 0 to +1

You may also be interested in previously reviewed on Excel Functions; MROUND, MODULUS & QUOTIENT, RANK.AVG and RANK.EQ, WORKDAY.INTL, LARGE, CEILING, TIME, ADDRESS ,FACT (factorial), MAX,MIN, MAXA, MINA and EXACT, Logical Functions, INFO, SUMSQ, DOLLAR, SUMPRODUCT, SUMIF, COUNTIF, VLOOKUP, HLOOKUP ,PMT, & LEN.

قالب وردپرس

About Alyse Kalish

Alyse Kalish
As an Associate Editor for The SalesJobInfo, Alyse is proud to prove that yes, English majors can change the world. She calls many places home, including Illinois where she grew up and the small town of Hamilton where she attended Colgate University, but she was born to be a New Yorker. In addition to being an avid writer, Alyse loves to dance, both professionally and while waiting for the subway.

Leave a Reply

Your email address will not be published. Required fields are marked *