While I haven't tried this, this webpage seems to provide a pretty good tutorial on how to get both Pearson's and Spearman's correlation coefficients using Excel, http://www.mnstate.edu/wasson/ed602calccorr.htm
Greetings all,
I am struggling with finding out the Pearson's coefficient when there are more than 2 variables, and would welcome any assistance on this subject. Please note that I do not have access to SPSS / any-other-software other than Excel 2007.
For a sample population of 22 students, I have their responses to a group of 5 questions. The responses are across a 7-item Likart scale (1-Strongly agree, 7-strongly disagree). I would like to know the degree of correlation between the responses to the 5 questions, say Ra. In all the books referred, they only talk about correlation between two variables (or questions), and not multiple variables. Is it possible to arrive at a single number that says that there is a strong correlation between the 5 issues involved?
Further, for the same sample population, I have their responses for another group of 6 questions, with the same Likart scale. Once I know the technique for multi-variate Pearson's coefficient, I can apply it for this second group and arrive at say Rb.
My second question is: how to work out whether there is a strong correlation between the two groups using Ra and Rb?
Alternatively, is there a better way to finding whether the variables within a given group are correlated, and whether two groups as a whole are correlated?
Any help would be most appreciated.
Thanks in advance,
Bill
While I haven't tried this, this webpage seems to provide a pretty good tutorial on how to get both Pearson's and Spearman's correlation coefficients using Excel, http://www.mnstate.edu/wasson/ed602calccorr.htm
Hello SmilingSara,
Thank you very much for your help.
In the webpage you mentioned, the example still uses two variables: 'Reading' and 'Spelling'. What I have is a set of 5 "items" and 22 "cases", to use the correct statistic jargon. (There are 22 students, who have responded to 5 questions. The response is from '1' to '7', ie. from 'Strongly Agree' to 'Strongly Disagree'.) How do I calculate the coefficient of correlation for the 5 items and arrive at one single number?
One solution that comes to mind is to calculate the variance for each item across 22 cases, say v1, v2, v3, v4, and v5. And then perhaps get an average of these 5 numbers. So I have one single number. Next I do what????
I am not sure if I am on the right track, and would really appreciate being shown the right way.
Thanks,
Bill
Sorry about my oversight. I think what you're looking for is the inter-item correlation.
Again, I'll refer you to a web page that does a nice job of explaining this simply, http://www.socialresearchmethods.net/kb/reltypes.php (about 1/2 way down the page).
In excel, I did this by calculating the correlations for the 5 items, and then taking an average of all of them:
Select Tools | Data Analysis | Correlation
Highlight the array to be considered (your 5 item columns)
Select Labels in First Row box if you have titles/names
Choose or click on the cell for the output.
Then just do an =average(corr1,corr2,corr3...)
Hello SmilingSara,
Thank you very much! This is what I had been looking for these past three days.
My Excel (2007) version didn't show any "Data Analysis" button; perhaps you have a plug-in?
In the "Formulas" tab, "Insert function" | "Statistical" category, there are two functions: "Correl", which the help says "returns the correlation coefficient between two data sets"; and "Pearson", which it says "Returns the Pearson product moment correlation coefficient, r". In my present situation, which of these would be more appropriate? Your input will be much appreciated.
Thank you once again,
Bill.
Last edited by billcurtis; 12-13-2008 at 11:45 PM.
Hi again,
I completed the Excel exercise as suggested by SmilingSara, and came up with two Correlation numbers - one each for one construct.
My next obstacle is to somehow say that the two constructs are correlated to each other. How do I do that using these two numbers?
To recap, construct #1 has 5 items and contruct #2 has 6. They both have responses (on Likart scale) from the same sample population of 22 students. I wish to show what correlation exists between these two constructs, if at all.
Should I average the two correlation numbers I arrived at? Or should I go a step back and arrive at a column, containing 22 entries, that somehow reflects the essence of the 5 items in construct #1 and another column which does the same thing for the 6 items in construct #2. And then work out the correlation between these two new columns? Will this help?
Your help will help me see this through.
Thanks,
Bill.
Hi all, please help me,
Please tell me the differences of Pearson's correlation coefficients between Excel and SPSS program.
Why and when will we use the SPSS or Excel?
I already calculated a data chain by both excel and SPSS butthe results were much different, SPSS always showed the much higher values than excel (about 100 times).
Thanks very much.
Envi
for correlation analysis follow the link to perform analysis in SPSS
http://itfeature.com/correlation-and...on-coefficient
any body help me, pls!
Tweet |