• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Correlation in Excel, separate for different countries (Solve)

Excel_beginner

New Member
Since correlation analysis isn't available in pivot tables, how would I go about showing Pearson r (correlation coefficient) for every country in my dataset? Imagine it as a pivot table in Excel where country would be used as a filter.

=CORREL(x,y). How can change this formula in order to calculate the correlation for let's say USA?
My data looks like the this, it's a big file with many counties. Here is just an example.

Country name------Var X------Var Y


 

shili12

Member
You can use the FILTER function in Excel to filter the data by country, and then use the CORREL function to calculate the correlation coefficient for the filtered data. Here's an example formula for calculating the correlation coefficient for the USA:

=CORREL(FILTER(Var X, Country name = "Germany"),FILTER(Var Y, Country name = "Germany"))

This will return the Pearson correlation coefficient for the data in the Var X and Var Y columns where the Country name is "Germany". You can use the same formula, but change the country name in the filter function to get the correlation coefficient for any country in the dataset.
 
Top