I need an Excel formula for correlation?

Question

In column B I have students' grades (A, B, C, D, or F). In column C I have the number of referrals a student recieved, whether it be 0, 1, or 10. I would like to know how to calculate the correlation between a student receiving an "F", and receiving 1 referral, and then again for more than 1 referral.

Answer 1

Well a really ugly brute force solution could look like this: Add 5 more columns, one for each grade letter. In the column labeled A, put a statement like this =if(B1="A",C1,0) in row 1. Copy it down the page so it is in every row in that column. If you look, for example, in row 13 of your "A grades" column, you should see the equation =if(B13="A",C13,0). You are using the "if" statement to test the letter as a text item and selecting a number (either the number of references if the grade is a match or zero if it is not) depending on the outcome. Then, what will happen when you run the sheet.is that there will be the number of references in that column for every A and a 0 for every other grade. Repeat the column for each grade. You will wind up with five columns with the references for each of the selected grades. Now sum up each column, find the sum of all the columns and compare them. Describing software in words is clumsy, but if you get my drift, it will work.

Answer 2

I was just toying with this. Lets assumed that grades are linear, assigning 1 to A, 2 to B and so on.

With that, you can plot a scatter chart. Right click, add trendline, select linear, select R-square in chart. The R-square thingy tells you the correlation strengths.