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

1. ## 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. ## 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:
`Please Login or Register  to view this content.`

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

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

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

3. ## 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. ## 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:
`Please Login or Register  to view this content.`

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

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

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

WORKS! Many thanks mate!

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

#### 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