+ Reply to Thread
Results 1 to 5 of 5

Calculating average for one column based on value existing in another column

  1. #1
    Registered User
    Join Date
    07-28-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Calculating average for one column based on value existing in another column

    Hi,

    I have the following problem: I would like to calculate average productivity of two people who do not always work together.
    I want to see if they are more productive individually than in a tandem and here's how the part of raw data looks like:

    Date------- Employee1-------Employee2
    31/10/2012------- 0.064------- 0.269
    02/11/2012------- -0.177------- #N/A
    03/11/2012------- -0.383------- #N/A
    05/11/2012------- 0.005------- 1.100
    07/11/2012------- 0.281------- 0.808
    08/11/2012------- #N/A------- 0.840
    11/11/2012------- #N/A------- 0.687
    14/11/2012------- -0.225------- 0.986
    17/11/2012------- 0.681------- -0.258


    If there is an #N/A it means employee was not working that day.
    So what I actually need is to have:
    1. Average for both employees when they are working together
    2. Avg for both employees when the other one was absent.

    Any ideas will be really appreciated.
    Thanks,

    Leszczur

  2. #2
    Forum Contributor
    Join Date
    12-19-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: Calculating average for one column based on value existing in another column

    Woking in my head this should do it. Might need a tweak in the sheet though as I might have messed something up!

    Employee 1 alone:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Employee 2 alone:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Employee 1 together:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Employee 2 together:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-28-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Calculating average for one column based on value existing in another column

    Perhaps I should have made myself clearer.
    For calculating average for Employee 1 - I'm not only looking to ignore cells with "#N/A" value, but I want only those where in corresponding column for Employee 2 we have "#N/A".
    So for the raw data of Employee 1 I want an average from row 2 and 3 (as Employee 2 was not at work). And for Employee 2 I want average of rows 6 and 7 (when Empl 1 was absent).

  4. #4
    Forum Contributor
    Join Date
    12-19-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: Calculating average for one column based on value existing in another column

    D'oh - yeah I knew something wasn't right, I gave you the inverse for the "alone" working. The ones for them both working together should still be correct.

    Employee 1 alone:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Employee 2 alone:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Again, let me know if I'm just having one massive Monday brain fart!

  5. #5
    Registered User
    Join Date
    07-28-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Calculating average for one column based on value existing in another column

    WORKS! Many thanks mate!

+ 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. How to create new column based on existing column?
    By Buchu in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-30-2014, 12:31 PM
  2. Replies: 2
    Last Post: 06-06-2013, 08:29 AM
  3. Replies: 2
    Last Post: 08-10-2012, 11:52 PM
  4. Replies: 2
    Last Post: 06-25-2012, 11:21 PM
  5. Replies: 8
    Last Post: 02-16-2011, 05:03 PM

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