How do I set up my data extraction

[Dirk Dewachter]

Hi All,

I am not a statistician but I need to track and compare the performance of
my employees to the team averages. Below is the worksheet that I will be
using to extract the data. For example two employees work as a team
throughout their shift and they can be paired up with different people
throughout the week. I enter their data on a spreadsheet and in the end I
can add up all the columns and figure out what each team does on average.
In order to extract each individual's performance I want to compare each
individual's data to the average of the teams.

What I am trying to accomplish is add up all the data by header for each of
the employees regardless who they are working with. For example in this
datasheet I want to look up what Employee #255 did during his two shfts (one
with #165 and one with #495) and divide by the number of shifts employee
#255 worked. I can then compare his or her performance to the average for
everybody.

I believe that I may have to break this data extraction up in a few formulas
but is there anybody that could direct me or help me with this project?

Thanks in advance

Dirk



EMPLOYEE#1 EMPLOYEE#2 Service Calls Time on Calls Out of Service
Time/OOS C6 Time/C6 Other Time/Other Idle Time TOTAL SHIFT TIME

05/15/09 255 165 10 6:00 2 0:13 1 0:04 2 4:10 5:05 15:32
05/15/09 643 525 10 2:14 5 1:41 4 0:37 1 4:02 6:58 15:32
05/15/09 750 495 6 10:17 0 0:00 3 1:13 1 4:16 1:30 17:16
05/15/09 362 740 7 2:08 1 0:34 5 1:13 3 4:18 7:20 15:33
05/15/09 980 520 5 1:44 2 1:22 2 0:10 1 4:12 8:12 15:40
05/15/09 157 636 4 1:52 3 0:49 0 0:00 1 0:43 8:14 11:38
05/17/09 495 255 12 8:15 3 0:23 1 0:22 2 0:54 4:00 13:54
05/17/09 165 643 8 4:56 4 2:54 0 0:00 2 0:24 6:43 14:57
05/17/09 520 362 6 2:54 3 1:20 3 0:41 1 1:12 8:12 14:19
05/17/09 636 980 5 3:32 4 1:42 1 0:30 1 0:43 8:14 14:41



255

 

[smartin]

Hi All,

I am not a statistician but I need to track and compare the performance of
my employees to the team averages. Below is the worksheet that I will be
using to extract the data. For example two employees work as a team
throughout their shift and they can be paired up with different people
throughout the week. I enter their data on a spreadsheet and in the end I
can add up all the columns and figure out what each team does on average.
In order to extract each individual's performance I want to compare each
individual's data to the average of the teams.

What I am trying to accomplish is add up all the data by header for each of
the employees regardless who they are working with. For example in this
datasheet I want to look up what Employee #255 did during his two shfts (one
with #165 and one with #495) and divide by the number of shifts employee
#255 worked. I can then compare his or her performance to the average for
everybody.

I believe that I may have to break this data extraction up in a few formulas
but is there anybody that could direct me or help me with this project?

Thanks in advance

Dirk



EMPLOYEE#1 EMPLOYEE#2 Service Calls Time on Calls Out of Service
Time/OOS C6 Time/C6 Other Time/Other Idle Time TOTAL SHIFT TIME

05/15/09 255 165 10 6:00 2 0:13 1 0:04 2 4:10 5:05 15:32
05/15/09 643 525 10 2:14 5 1:41 4 0:37 1 4:02 6:58 15:32
05/15/09 750 495 6 10:17 0 0:00 3 1:13 1 4:16 1:30 17:16
05/15/09 362 740 7 2:08 1 0:34 5 1:13 3 4:18 7:20 15:33
05/15/09 980 520 5 1:44 2 1:22 2 0:10 1 4:12 8:12 15:40
05/15/09 157 636 4 1:52 3 0:49 0 0:00 1 0:43 8:14 11:38
05/17/09 495 255 12 8:15 3 0:23 1 0:22 2 0:54 4:00 13:54
05/17/09 165 643 8 4:56 4 2:54 0 0:00 2 0:24 6:43 14:57
05/17/09 520 362 6 2:54 3 1:20 3 0:41 1 1:12 8:12 14:19
05/17/09 636 980 5 3:32 4 1:42 1 0:30 1 0:43 8:14 14:41



255

Assume your example in A1:M11, with the employee to report (255) in cell
B16, and the first data column (service calls) in column D.

Place the following in D16, fill right (for other measures) and down
(for other employees) as needed:

=(SUMIF($B$2:$B$11,$B16,D$2$11)+SUMIF($C$2:$C$11,$B16,D$2$11))/(COUNTIF($B$2:$B$11,$B16)+COUNTIF($C$2:$C$11,$B16))

Alternatively, use this much shorter array* formula:

=AVERAGE(IF($B$2:$C$11=$B16,D$2$11))

*Array formulas must be committed by pressing Ctrl+Shift+Enter, do not
press just Enter or Tab.