Correlation Data Analysis Tool

Real Statistics Data Analysis Tool: The Real Statistics Resource Pack provides the Correlation data analysis tool. This tool calculates the Pearson’s, Spearman’s (rho) and Kendall’s (tau) correlation coefficients, as well as conducts various versions of a one-sample correlation test.

Example 1: Repeat Example 1 of Correlation Testing via the t Test (regarding Pearson’s correlation) using the Correlation data analysis tool.

To use this tool, press Ctrl-m and select Correlation from the menu of choices that appears. Fill in the dialog box that appears as shown in Figure 1 and press the OK button.

Figure 1 – Correlation data analysis tool dialog box

The output for the two-tailed test is shown on the right side of Figure 2 (starting at cell J3). The output contains the Pearson’s, Spearman’s and Kendall’s correlation coefficients (cells K5, K6 and K7). In addition, both the two-tailed t-test and normal test using the Fisher transformation are displayed where the hypothetical population Pearson’s correlation ρ = 0.

If you want to conduct a one-sample correlation test where ρ is equal to some value that is not zero, you would simply perform that same steps as described above and then change the value in cell N11 to this value. In this case you would need to ignore the results of the t test since they would not be accurate.

You can also change the value for alpha and/or the number of tails, and the output values will change automatically.

Figure 2 – Output from Correlation data analysis tool (Pearson’s)

Example 2: Repeat Example 3 of Spearman’s Correlation using the Correlation data analysis tool.

Press Ctrl-m and select Correlation as before, but when the dialog as in Figure 1 appears, select the Spearman’s option. The result is shown in Figure 3.

Figure 3 – Output from Correlation data analysis tool (Spearman’s)

We see from the figure that Spearman’s rho is -.67442 (cell N14). Since p-value = .005821 < .05 = α, we are pretty confident that rho is not zero. If you change the values in cells N11 and/or N12 the other values of rho, t-stat and p-value will change automatically.

Example 3: Repeat Example 1 of Kendall’s Tau using the Correlation data analysis tool.

Press Ctrl-m and select Correlation as before, but when the dialog as in Figure 1 appears, select the Kendall’s option. The result is shown in Figure 4.

Figure 4 – Output from Correlation data analysis tool (Kendall’s)

We see from the figure that Kendall’s tau is -.52381 (cell Y13) and that we are 95% confident that the actual value is in the interval (-.901, -.147). Since p-value = .006493, we are pretty confident that tau is not zero.

If you change the values in cells Y10 and/or Y11 the values of tau, s.e., etc. will change automatically.

Observation: The ties correction factor is used for the Kendall’s Tau option of the Correlation data analysis tool.

54 Responses to Correlation Data Analysis Tool

1. amila says:

i have a problem with converting my original survey results in to statistical analysis. i designed a questionnaire to determine relationship between service quality & promotional effort impact on brand equity.the like-rt scale define results between strongly agree( +2) to strongly disagree(-2).( eg; leasing company should maintain all necessary modern equipment – (agree 3 )which in like-rt scale is 0).how do i convert this results to correlation analysis data.

• Charles says:

Amila,
It is not clear from your description what you are correlating with what. In any case, to use the tools described on the referenced webpage all you need to do is create two columns of data one for each of the two data sets that you are correlating.
Charles

2. Swati says:

Hi. If i have two variables and data points on only one of them is available. However the correlation cofficient between the two variables is given. How do i calculate the data points of the second variable?

• Charles says:

You can’t. There are an infinite number of possible values for the second variable that yield the same correlation coefficient.
Charles

3. nic says:

Dear Mr. Zaiontz

I have a question regarding the Kendall Tau function in the Correlation Data Analysis Tool.

Above you state the following: “Observation: The ties correction factor is used for the Kendall’s Tau option of the Correlation data analysis tool.”

Does this “ties correction factor” correspond to Kendalls Tau-b, Kendalls-Tau-c or is it something completely different?

N.

• Charles says:

Tau-b is tau-a adjusted for ties.
Charles

4. Stephen Yahaya says:

Pls I have data to analyze using spearman rank correlation but don’t know how to go about.

Pls help me as how to go about it. Thanks

5. Cynthia says:

Hello Charles,

If I am analyzing survey results which contain Likert scale answers, is it better to input them as:
strongly disagree = -2
disagree = -1
agree = 1
strongly agree = 2

OR

strongly disagree = 1
disagree = 2
agree = 3
strongly agree = 4

Also wondering something the same thing for a question that asks how long a call took, with answer choices being Less Time than Expected, About as Much Time as Expected, and More Time than Expected. Less=1, About=2, More=3 OR Less=-1, About=0, More=1?

Thank you!

• Charles says:

The first scale introduces an implied zero rating, presumably meaning “neutral”. This sort of 5 point Likert scale of -2, -1, 0, 1, 2 should be equivalent to a 1,2,3,4,5 scale, but won’t be equivalent (at least mathematically) to the 1,2,3,4 scale that you have described.
Charles

• Cynthia says:

So if the answer choices do not contain a neutral rating, I should use 1,2,3,4?

• Charles says:

Yes

6. Jimmy Blumenkranz says:

Hi! I want to ask you on what correlation data analysis tool I should use for my study on self-esteem’s relationship with academic performance. I used the Sorensen Self-Esteem Test to evaluate the respondents’ self-esteem and took their GPA’s from last year for the academic performanc. In the Sorensen Self-Esteem Test, you can get four results: Fairly Good, Mild Low, Moderately Low, and Severely Low. What statistical tool should I use to get the correlation coefficient? Thanks.

• Charles says:

Jimmy,
You can simply use Excel’s CORREL function to calculate the correlation coefficient. The specific correlation data analysis tool to use really depends on what hypothesis you are trying to test.
Charles

7. vandana says:

Hi,

I want to represent results of two workshops using correlation
i.e. correlation between time spent on writing user stories and accuracy/ decrease in errors related to that would be time spent in workshop 1 and 2,
Thanks
Vandana

• Charles says:

Use the correlation coefficient, as described on this webpage.
Charles

8. shilpi says:

sir
is there difference in the correlation value calculated from excel and the values calculated values from statsitistical software , in case in those data which have been applied formula in excel sheet

• Charles says:

No differences that I know of. The correlation coefficient is pretty standard.
Charles

9. FATIN says:

Hi. I am doing the correlation between no of dengue cases and maximum temperature. Correlation is done by using Spearman, Kendall, and Pearson. My problem is, how to compare which method is best to used? I don’t know what performance evaluation tools should i use. Thank you.

• Charles says:

Generally you should use the Pearson correlation coefficient. Only when you want to see whether there is a significant difference between this coefficient and some hypothetical value would you consider using one of these other coefficients. This would be the case if the test assumptions are not met (e.g. normality). These issues are discussed on the website.
Charles

10. hi sir
how to use correlation coffetent for discriptive data in order to assesing energy expenditur& nutiritional intake of college athletes?
thank u!!

• Charles says:

You can use a formula of the form CORREL(R1,R2) where R1 is a data range which contains the energy expenditure of the college athletes being studies and R2 contains the corresponding nutritional intake for these athletes.
Charles

11. Diana Orozco says:

Hello,

I was wondering, if I have a survey and want to see the correlation between gender and political affiliation, can that be done with your instructions? I was thinking of coding the responses, (0=female, 1=male) and doing the same with question two. If I have this coded data in excel, can I run a correlation? Thanks.

• Charles says:

Diana,
Yes, you can do that. keep in mind that since one of the variables is dichotomous (Male/Female), essentially your test is equivalent to testing whether the mean of question 2 for males is significantly different from that of females (i.f. a t test or Mann-Whitney).
Charles

12. GERARDO says:

Apreciado Dr. Zaionits, reciba un cordial saludo, Dr excuseme una pregunta, si yo hago una tranformación de Box Cox Normal para mis datos, como calculo la inversa de la tranformación para interpretarlos?

Dear Dr. Zaionits, Yours sincerely, Dr Excuse me a question, if I make a transformation of Box Cox Normal for my data, as I do calculating the inverse of the transformation in order to interpret?

13. dhanya says:

how did you find the values of cigg, life expectancy from the questionnaire

• Charles says:

These are purely made-up numbers, used to illustrate the statistical test.
Charles

14. Ming says:

Hi, Sir.

Can I use correlation test (Pearson’s) to check the correlation between two dependent/response variables? Here’s a background of the study: I have 3 factors with 2 levels each then there are about 5 response variables measured after the study but I am interested to know the correlation between the 2 only. I used anova (three fixed factors using realstats addin) to check the significance of the 3 factors. How would I know if this significance means positive or negative? Also, regarding the 2 response variables obtained from the study, can I use correlation test to check correlation between them?

Ming

• Charles says:

Ming,
You can use a correlation test to test the correlation between two variables.
Anova tests whether there is a significant difference between two or more levels in a factor or various interactions. I don’t know what you mean by positive or negative significance. Significance is not directional; there is only significance.
Charles

• Ming says:

So I can use correlation test between the 2 response variables measured, right? Thanks. “Anova tests whether there is a significant difference between two or more levels in a factor or various interactions.” – Only for the levels in a factor? Can I also conclude from Anova that a factor has a significant effect to the response measured if for example, I get a “yes” in the ‘sig column’ referring to ‘A’ factor in the Anova table. Can I also conclude that there is an interacting effect between two factors if I get a “yes” for ‘AxB’ for the response measured? By positive, I mean if I increase the magnitude of the factor (in case of my study it is metal concentration), the response measured (metal uptake in the study) also increases. In negative, if I decrease the factor, the response also decreases. Does any input in the anova table say something about it? Thanks for the help!

• Charles says:

Ming,
Regarding your third question, about positive/negative, what do you mean by increasing/decreasing the magnitude of the factor? Does this mean, for example, doubling (or halving) all the sample data values?
Charles

• Ming says:

For example, the ‘A’ factor is metal concentration with two levels of concentrations 0 and 50, if I increase the concentration from 0 to 50, the response which is metal uptake by the plants also increases. That is if I increase the amount of metal in the water, the amount of metal taken by the plant also increases. Is that what it means if in the anova table it says ‘yes’ in the ‘sig column’ for ‘A’?

• Charles says:

Ming,
I don’t completely understand your description, but it sounds like what you want is follow-up testing after ANOVA (contrasts, Tukey HSD, etc.).
Charles

• Ming says:

Alright. Thanks, Sir Charles.

15. Sheetal says:

Hi Charles,

I am into random numbers testing for online gambling. I need to perform below tests:-
1. Serial Correlation Test : The Serial Correlation Test determines whether or not the numbers picked for the current draw in any way determines subsequent plays. We calculate a correlation coefficient r based on pairs of data taken from the same number position on consecutive draws.
2. Interplay Correlation Test : This test determines whether or not there is any relation between two positions of numbers chosen within the same draw. It is essentially the same analysis that is performed in the Serial Correlation Test, but it is performed on pairs of data taken from different number positions within the same draw rather than between draws.

I had figured out approach for serial correlation test, but not sure about calculating correlation coefficient for second test. Can you please help ? Below the example of number drawn:-
A B C D E
3 1 3 0 2
0 2 4 1 1
2 2 4 3 1
2 2 1 1 0
2 1 1 0 1
2 0 3 1 2
3 2 1 1 2
1 1 5 0 2

• Charles says:

Sheetal,
Sorry, but I am not familiar with the interplay correlation.
Charles

• Sheetal says:

No problem, Charles!

16. Wasif says:

Hey Charles wonderful job. But here is one problem that correlation is of 2 types; phenotypic and genotypic. What about calculation of genotypic correlation?

• Charles says:

From a statistical point of view there is only “correlation”. I am not a biologist or geneticist and so don’t have specific knowledge about how this term might be used in those fields.
Charles

17. Manfred Becker says:

Hi,
I need to compare correlations of testscores from different test-results of the same Test/differents Tests (same construct) from same/different populations.
I think I need to to standardize the results via Fishers’ Z? Is there any tool her?

• Charles says:

Manfred,
It is not clear to me what specifically you are looking for, but perhaps the following webpage can help
http://www.real-statistics.com/correlation/two-sample-hypothesis-testing-correlation/
Also there is the FISHER function which may be useful.
Charles

• Manfred Johannes Becker says:

Hi Charles,
thanks for the answer: my problem: I have to tests (intelligence, dyslexia,… ) with correlations to school-grades. The correlations are somewhat different. I want to know whether the differences are significant, so that I can prefer one test. Samples, time of norming etc are different (ages, gender, schoolform are mostly similar)
Manfred

• Charles says:

Manfred,
You say that you “want to know whether the differences are significant.” In order for me to be able to help you, please give me an example of what sort of differences you are referring to.
Charles

• Manfred Becker says:

Hi Charles,
the differences of Correlations between different tests and school grades
Thanks,
kind regard manfred

• Charles says:

Sorry Manfred, but I don’t understand your comment.
Charles

18. Ross says:

Great article Charles. I am working with a series of non-random numbers (stock price changes on a given day). On 30 random days, I have the returns on 10 different stocks. I would like to find out of A, these companies are correlated with each other, and B, which of these companies are the most correlated with each other.

There is logical data behind why they should be correlated, but I’m not sure that part is relevant to the explanation here. Thank you

• Charles says:

Ross,
Assuming that “companies” and “stocks” refer to the same thing, I understand that you have data that can be organized into the range A1:J30 as follows: Each column contains the data for one of the 10 stocks and each row contains the data for one of the 30 days.
You can now create a correlation matrix using the Real Statistics formula =CORR(A1:J30). This will show all the pairwise correlations. The values that are the highest are the most correlated.
Charles

• Ross says:

Perfect, thank you

19. yuvaraj says:

can you mention the name of the tool used for co-relation process

• Charles says:

One Sample Correlation. This is a Real Statistics data analysis tool. You won’t find it among the standard Excel data analysis tools.
Charles

20. Juozas says:

Dear Charles,
I have two data sets each consisting of 17 means±S.D.
How can I calculate coefficient of correlation in this case?
Many thanks,
Juozas

• Charles says:

Juozas,
I don’t completely understand your question, but you can’t calculate the correlation coefficient from just the means and standard deviations.
Charles