Conditional average of time
 

Hi everyone,

I have a big problem and need descreptly your help on that.

I have an excel 2000 sheet in which i need to calculate average of time
taken to solve the problem. Sheet details are given below

time is in [h]:mm format and conditions are there i.e. if data in column A
meets creteria then only it will add the respective time viz..

A B
123 36:56
123 12:13
235 00:15
528 28:16

now on based on this example i want to take average of time value from
column B where data in column A is 123. which should come (36:56+12:13)/2.

Please help
--
"Rome was not built in one day"

Conditional average of time
 

=SUMPRODUCT(--(A1:A4=123),B1:B4)/COUNTIF(A1:A4,123)

Format as [h]:mm



"Lovik" wrote:

Hi everyone,

I have a big problem and need descreptly your help on that.

I have an excel 2000 sheet in which i need to calculate average of time
taken to solve the problem. Sheet details are given below

time is in [h]:mm format and conditions are there i.e. if data in column A
meets creteria then only it will add the respective time viz..

A B
123 36:56
123 12:13
235 00:15
528 28:16

now on based on this example i want to take average of time value from
column B where data in column A is 123. which should come (36:56+12:13)/2.

Please help
--
"Rome was not built in one day"

Conditional average of time
 

=AVERAGE(IF(A1:A100=123,B1:B100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Lovik" wrote in message
...
Hi everyone,

I have a big problem and need descreptly your help on that.

I have an excel 2000 sheet in which i need to calculate average of time
taken to solve the problem. Sheet details are given below

time is in [h]:mm format and conditions are there i.e. if data in column A
meets creteria then only it will add the respective time viz..

A B
123 36:56
123 12:13
235 00:15
528 28:16

now on based on this example i want to take average of time value from
column B where data in column A is 123. which should come (36:56+12:13)/2.

Please help
--
"Rome was not built in one day"