Calculating Correlation from Summary Table

[McStagger]

I am curious if anyone has gone through the steps of calculating correlation from a summary table.

For example: You have two variables which are 5 point scales that have been cross tabbed and you want to determine the correlation between the two variables without unwinding the data. The data in any given cell are counts, not proportions.

I know that in this example the data are not continuous.

For my data set in question, I have already gone through the process of unwinding the data, I am just curious if there is a formula, function or add-in that will work directly with a summary table.

EDIT: Data set attached. Is there a way to calculate a Correlation Coefficient from "Raw Data" so that you do not need to go through the process of creating what is in "Unwound and Plotted"?

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

In addition would you add your Excel version and location to your profile. This often helps when we need to consider things like time zones and regional settings for dates and list separators.

[McStagger]

Post updated as requested.

[shg]

It seems like there should be a way, but nothing is jumping to mind. You could (someone could) certainly write a UDF that does the unwinding.

[McStagger]

My current process for unwinding is using Power Query's "From Table" tool and unpivoting the columns. From there I have some scripting I run that will duplicate my rows based on the counts for any given occurrence at which point I plot the data, generate the line and take the square root of the R^2. This isn't too bad for a one off, but for a larger set of tables it's less than ideal.

[shg]

With reference to https://en.wikipedia.org/wiki/Pearso...on_coefficient, it seems like this should work, but it doesn't:

	B	C	D	E	F	G	H	I	J	K
1									Weighted Mean
2	y \ x	1	2	3	4	5		mxw	3.0087	J2: =SUMPRODUCT(wi * xi) / SUM(wi)
3	1	639	642	263	157	641		myw	2.9707	J3: =SUMPRODUCT(wi * yi) / SUM(wi)
4	2	160	5	288	416	903
5	3	701	255	205	82	473			Weighted Cov
6	4	727	536	501	494	550		covxxw	2.3574	J6: =SUMPRODUCT(wi * (xi - mxw) * (xi - mxw)) / SUM(wi)
7	5	314	451	160	718	30		covxyw	-0.1872	J7: =SUMPRODUCT(wi * (xi - mxw) * (yi - myw)) / SUM(wi)
8								covyyw	2.0009	J8: =SUMPRODUCT(wi * (yi - myw) * (yi - myw)) / SUM(wi)
9
10									Correlation
11									-0.0862	J11: =covxyw / SQRT(covxxw * covyyw)

[MrShorty]

Would you be interested in "unwinding" the math/algebra of the thing? SQRT(RSQ()) is simply PEARSON(). My statistics is weak, but I would expect you could modify the standard form for the Pearson() function to give you a "weighted" Pearson function, which should be what you are looking for.

Wikipedia article -- see section weighted correlation coefficient: https://en.wikipedia.org/wiki/Pearso...on_coefficient

Naturally, weighted Pearson is not a built in function in Excel (to my knowledge).

Can someone whose statistics Kung Fu is stronger than mine verify that a weighted Pearson function would be correct for this? If it is correct, one could solve the problem by:

1) calculate the two weighted averages
2) calculate the three covariances from the raw data and the two weighted averages.
3) calculate r from the three covariances.

I would agree with shg that I would like to see that in a UDF if this was something I was going to do a lot. If the statistics is right, that might be easier than unwinding this table into a longer list like you are doing.

on edit: shg seems to have the same idea...

[shg]

It looks to me like all of the SUM(wi) terms should cancel out, but replacing all of them with 1 doesn't give the same result. Curious. MrS, see anything obviously wrong?

[MrShorty]

Why do we think that -.0862 is wrong? If I put =PEARSON(A:A,B:B) into D1 of the unwound and plotted sheet, I also get -.0862. -.0862^2 is 0.007431 (the chart says 0.0074). Maybe we are right????

[shg]

Doh!. Well, there you go.

[shg]

So ...

	B	C	D	E	F	G	H	I	J	K
3	y \ x	1	2	3	4	5			Weighted Mean
4	1	639	642	263	157	641		mxw	3.0087	J4: =SUMPRODUCT(wi * xi) / SUM(wi)
5	2	160	5	288	416	903		myw	2.9707	J5: =SUMPRODUCT(wi * yi) / SUM(wi)
6	3	701	255	205	82	473
7	4	727	536	501	494	550			Weighted Cov
8	5	314	451	160	718	30		covxxw	2.3574	J8: =SUMPRODUCT(wi * (xi - mxw) * (xi - mxw)) / SUM(wi)
9								covxyw	-0.1872	J9: =SUMPRODUCT(wi * (xi - mxw) * (yi - myw)) / SUM(wi)
10								covyyw	2.0009	J10: =SUMPRODUCT(wi * (yi - myw) * (yi - myw)) / SUM(wi)
11
12								Correlation	-0.0862	J12: =covxyw / SQRT(covxxw * covyyw)
13								R2	0.0074
14
15								Instant gratification	0.0074	J15: =SUMPRODUCT(wi * (xi - SUMPRODUCT(wi * xi) / SUM(wi)) * (yi - SUMPRODUCT(wi * yi) / SUM(wi)))^2 / (SUMPRODUCT(wi * (xi - SUMPRODUCT(wi * xi) / SUM(wi)) ^ 2) * SUMPRODUCT(wi * (yi - SUMPRODUCT(wi * yi) / SUM(wi)) ^ 2))

[McStagger]

Thanks a lot for tackling this guys. Now I need to figure out how a lot of these values are calculated so I have some Excel work ahead of me. Good to see there is a way though!

Thanks again!

Edit: Replicated the above on a couple data sets and it works!

In case this is looked up in the future...

wi = Range of scale data
xi = x (scale points)
yi = y (scale points)

[shg]

In case this is looked up in the future...

wi = total number of data points
xi = Sum of x scale range
yi = Sum of y scale range

No! They are the named ranges that contain the weight, x, and y values respectively.

[McStagger]

Whoops! Clearly I was not paying attention to the formula or concept. You are correct!