You will need

- Spreadsheet editor of Microsoft Office Excel.

Instruction

1

Start Excel and open the document that contains the data groups, the correlation coefficient between which you want to calculate. If such a document has not yet been created, enter the data into an empty table - the table editor creates it automatically when the program starts. Each of the groups of values, the correlation between which you are interested in, enter in a separate column. It doesn't have to be adjacent column, you are free to arrange the table in the most convenient way to add additional columns with explanations to the data, column headers, summary cells with total or average values, etc. Can even be positioned in a vertical data (columns) and horizontal (rows) direction. The only requirement that must be met - the data cells of each group should be placed one after another, to thus create a contiguous array.

2

Navigate to the cell that contains the correlation value data of two arrays, and click on the Excel menu tab "Formulas". In the group of commands "Library functions" and click the very last icon - "Other functions". Will reveal a drop-down list where you should go to "Statistics" and select the function correl. This will open the wizard window function with a shape designed to fill. The same window can be opened without the tab "Formulas" just by clicking on the icon insert function, placed to the left of the formula bar.

3

Specify the first group of correlated data in the field "Pattern1" masters of formulae. To enter a range of cells manually enter the address first and last cells, separated by a colon (no spaces). Another option - just select the desired range with the mouse, and the desired entry in this field form Excel places on their own. The same operation should be repeated and with the second group of data in the field "Pattern2".

4

Press the OK button. The table editor will calculate and display the correlation value in the cell with the formula. If necessary, you can save this document for future use (Ctrl + S).

# Advice 2: How to calculate correlation coefficient

By definition, the correlation coefficient (normalized correlation moment) is the ratio of the correlation time of the system of two random variables (CERs) to its maximum value. In order to understand the essence of this question, it is necessary first of all to get acquainted with the concept of correlation time.

You will need

- paper;
- - handle.

Instruction

1

Definition: Correlation time CERs X and Y is the mixed Central moment of second order (see Fig.1)

Here W(x,y) is the joint probability density DIS

The correlation time is a characteristic of: a) the mutual variation of CERs relative to the mean values or mathematical expectations (mx, my); b) degree of linear relationship between ST. X and Y.

Here W(x,y) is the joint probability density DIS

The correlation time is a characteristic of: a) the mutual variation of CERs relative to the mean values or mathematical expectations (mx, my); b) degree of linear relationship between ST. X and Y.

2

Properties of the correlation time.

1. R(xy)=R(yx) – from the definition.

2. Rxx=Dx (variance) is defined.

3. For independent X and Y. R(xy)=0.

Indeed, with M{HC,UC}=M{HC}M{UC}=0. In this case, the lack of linear relationship, but not all, and, say, quadratic.

4. In the presence of "hard linear relationship between X and Y, Y=aX+b – |R(xy)|=bhbu=max.

5. –bhbu≤R(xy)≤, bhbu.

1. R(xy)=R(yx) – from the definition.

2. Rxx=Dx (variance) is defined.

3. For independent X and Y. R(xy)=0.

Indeed, with M{HC,UC}=M{HC}M{UC}=0. In this case, the lack of linear relationship, but not all, and, say, quadratic.

4. In the presence of "hard linear relationship between X and Y, Y=aX+b – |R(xy)|=bhbu=max.

5. –bhbu≤R(xy)≤, bhbu.

3

Now back to the consideration of the correlation coefficient r(xy), the meaning of which is in the linear relationship between SV. Its value varies from -1 to 1, furthermore it has no dimension. In accordance with the above, we can write

R(xy)= R(xy)/bhbw (1)

R(xy)= R(xy)/bhbw (1)

4

To explain the meaning of normalized correlation time, imagine that experimentally derived values of ST X and Y are coordinates of points in the plane. If you have "hard" line of when these points will exactly fall on a straight line Y=aX+b. Positie only positive correlation values (with a

5

If r(xy)=0, all the obtained points will be inside the ellipse with center in (mx, my), the magnitude of the semiaxes of which is determined by the values of the SV dispersions.

In this issue of the calculation of r(xy), it would seem, should be closed (see formula (1)). The problem lies in the fact that the researcher who received the experimental SV values, can not 100% know the probability density W(x,y). Therefore it is better to assume that the task considers the sample values SV (that is, obtained in the experiment), and use estimates of the desired quantities. Then the rating

mx*=(1/n)(x1+x2+...+xn) (for SV, Y the same). Dx*=(1/(n-1))((x1 - mx*)^2+ (x2 - mx*)^2+...

+(xn - mx*)^2). R*x=(1/(n-1))((x1 - mx*)(y1 - my*)+(x2 - mx*)(y2 - my*)+...+(xn - mx*)(yn - my*)). BKH*=sqrtDx (the same for the SV Y).

Now you can safely for estimates use the formula (1).

In this issue of the calculation of r(xy), it would seem, should be closed (see formula (1)). The problem lies in the fact that the researcher who received the experimental SV values, can not 100% know the probability density W(x,y). Therefore it is better to assume that the task considers the sample values SV (that is, obtained in the experiment), and use estimates of the desired quantities. Then the rating

mx*=(1/n)(x1+x2+...+xn) (for SV, Y the same). Dx*=(1/(n-1))((x1 - mx*)^2+ (x2 - mx*)^2+...

+(xn - mx*)^2). R*x=(1/(n-1))((x1 - mx*)(y1 - my*)+(x2 - mx*)(y2 - my*)+...+(xn - mx*)(yn - my*)). BKH*=sqrtDx (the same for the SV Y).

Now you can safely for estimates use the formula (1).