problem with lookup function
 

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

problem with lookup function
 

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

problem with lookup function
 

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