+ Reply to Thread
Results 1 to 3 of 3

Correlating Data from Two Sets - Which Formula / Function?

  1. #1
    Registered User
    Join Date
    11-06-2019
    Location
    Bristol, England
    MS-Off Ver
    2016
    Posts
    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

    Thanks in advance

    66E80B19-BA1D-455D-8321-52302552D1CA.png

  2. #2
    Registered User
    Join Date
    12-03-2012
    Location
    CHENNAI , INDIA
    MS-Off Ver
    Excel 2007
    Posts
    93

    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

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,409

    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.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to identify multiples of 3 whilst correlating to the first 5 years
    By Rose19 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-30-2019, 01:09 AM
  2. [SOLVED] Correlating data from multiple worksheets into a new one by timestamp
    By adyady55 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 11-27-2015, 05:22 AM
  3. [SOLVED] Is there a binning function for data sets?
    By Danja in forum Excel General
    Replies: 4
    Last Post: 08-12-2014, 11:35 PM
  4. Formula for correlating cells based on a drop down
    By PittyPotts in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-20-2014, 01:29 PM
  5. [SOLVED] Transposing Correlating Data from Adjacent Cells via Formula
    By RoyM56 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-31-2013, 08:44 PM
  6. Correlating x-axis data with two y-axes that are non-linear
    By utahmoose in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-24-2012, 07:59 AM
  7. Correlating data: Which function(s).
    By Jo-Jo in forum Excel - New Users/Basics
    Replies: 22
    Last Post: 11-27-2009, 09:40 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1