I have a problem with summing a lookup function

My data looks like this:

A1: text B1: time C1: time D1: time E1: time

A1 = could be any of 8 different sets of two letter initials of counselor
B1 = time arrived
C1 = time seen
D1 = time departed
E1 = average wait time

I have a table from A1:D40. I want to query this table, by counselor , and
return the average wait time for each counselor. Exampe: "DF" shows up three
times and has the wait times of 3 min, 6 min and 4 min then the return answer
would be 4 min 20 sec. (3+6+4)/3 = 4'20"

Any help? Maybe the lookup function is not my answer. Thanks in advance.

--
LAMP

You could probably try something like this, a conditional average

Assume you have cell F1 is where you input the counselor's ID: DF
A1:A10 = counselors, eg DF, FG, etc
E1:E10 = wait times (times are just numbers)

Array-entered into G1,
ie press CTRL+SHIFT+ENTER to confirm the formula:
=AVERAGE(IF((A1:A10=F1)*(E1:E10<""),E1:E10))
Success? hit the YES below
--
Max
Singapore
---
"lampatmyfeet" wrote:
I have a problem with summing a lookup function

My data looks like this:

A1: text B1: time C1: time D1: time E1: time

A1 = could be any of 8 different sets of two letter initials of counselor
B1 = time arrived
C1 = time seen
D1 = time departed
E1 = average wait time

I have a table from A1:D40. I want to query this table, by counselor , and
return the average wait time for each counselor. Exampe: "DF" shows up three
times and has the wait times of 3 min, 6 min and 4 min then the return answer
would be 4 min 20 sec. (3+6+4)/3 = 4'20"

Any help? Maybe the lookup function is not my answer. Thanks in advance.

--
LAMP

Max,

Your formula did the trick. Thanks so much and sorry it has taken me awhile
to respond. Was just able to get back on this project.
--
LAMP


"Max" wrote:

You could probably try something like this, a conditional average

Assume you have cell F1 is where you input the counselor's ID: DF
A1:A10 = counselors, eg DF, FG, etc
E1:E10 = wait times (times are just numbers)

Array-entered into G1,
ie press CTRL+SHIFT+ENTER to confirm the formula:
=AVERAGE(IF((A1:A10=F1)*(E1:E10<""),E1:E10))
Success? hit the YES below
--
Max
Singapore
---
"lampatmyfeet" wrote:
I have a problem with summing a lookup function

My data looks like this:

A1: text B1: time C1: time D1: time E1: time

A1 = could be any of 8 different sets of two letter initials of counselor
B1 = time arrived
C1 = time seen
D1 = time departed
E1 = average wait time

I have a table from A1:D40. I want to query this table, by counselor , and
return the average wait time for each counselor. Exampe: "DF" shows up three
times and has the wait times of 3 min, 6 min and 4 min then the return answer
would be 4 min 20 sec. (3+6+4)/3 = 4'20"

Any help? Maybe the lookup function is not my answer. Thanks in advance.

--
LAMP