I need help finding an average

J

JHL

Data on sheet 1:

Column A is a list of employees by initials: AA, BB, CC, DD, etc.
Column B is the type of project the employee was assigned: L, M, N, O, R, S,
T, U, V, etc.
Column C is the number of days it takes the employee to complete the
assignment: 7, 5, 11, etc.

Each time a new assignment is given it is logged on a row in the spreadsheet
by the employee's initials in Column A and the type of assignment in Column B
of the same row. When the assignment is completed, I note the number of days
it took to complete the assignment in Column C.

Data on sheet 2:

This is the monthly production report. Each employee is listed on a separate
row. Assignments have been graded by management as easy, medium, and
difficult.

Column A is for "easy" assignments: types L, U, S
Column B is for "medium" assignments: types M, N, R
Column C is for "difficult" assignments: types O, T



What I need to extract from sheet 1 is the average number of days it take
each employee to complete his assignments for the month separated by easy,
medium and difficult. Also, since not all assignments will be completed by
the end of the month, some cells in Column c on Sheet 1 will be blank.

Thanks for your help!
 
B

Bob Phillips

=AVERAGE(IF((Sheet1!A7:A12="Bob")*(Sheet1!B7:B12={"L","U","S"}),Sheet1!C7:C12))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
J

JHL

Thanks, Bob, but I'm still having trouble. The answer was returned as 1. For
the example I tried, there has only been 1 assignment completed for the month
and it took 11 days, so I think the answer should have been 11 since I'm
looking for an average number of days for completed assignments.
Any other suggestions?
Thanks,
 
B

Bob Phillips

Post the sample data, as it worked on my test data.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Top