# Correlating Data from Two Sets - Which Formula / Function?

1. ## Correlating Data from Two Sets - Which Formula / Function?

Hi guys

I am trying to understand how well correlated two data sets are and dont know which formula / function to use.

To give you an example I am trying to work out how good I am at predicting what hours a staff member will work across a range of projects. For example in the table below Scenario 1 shows that Fred worked exactly what was predicted so I would expect the correlation to be 100%. in scenario 2 he did not work anything which was planned so I would expect the correlation to be 0% but I dont know how to calculate the correlation for Scenario 3. Note I cant do correlation on the sum of the hours in each column as theyre all the same so need some kind of statistical test to check task by task

66E80B19-BA1D-455D-8321-52302552D1CA.png  Register To Reply

2. ## Re: Correlating Data from Two Sets - Which Formula / Function?

Hi ,

How do you measure the correlation between Planned and Actual for a task ?

Obviously if the two are equal , there is perfect correlation , but what if the two are unequal ? Does the correlation go to 0 or is there a gradation ?

Narayan  Register To Reply

3. ## Re: Correlating Data from Two Sets - Which Formula / Function?

Hi JD,

Here is a step by step example of how to find a Correlation Coefficient.

https://www.easycalculation.com/stat...orrelation.php

See if you can follow it.

Then look at what Excel has to offer for Correlation like:
https://www.excelfunctions.net/excel...-function.html

I hope this helps.  Register To Reply