Averaging times that are not in a range
 

I am trying to get an average response time for numbers that are not listed
in a range. They are formated as a custom format like this: h:mm:ss. I have
been able to get a range to work, but not durations that have other cells
between them. Below is a picture of my table. The three center times,
Dispatch Time to On Scene, are the times I would like to average. Can you
please help me with the correct calculation? Thanks!

Tk-1 - Sta 1 - 96 Acoma
Dispatch Time to Enroute 0:01:29
Dispatch Time to On Scene 0:06:00
Total Time-Dispatch to Available 0:29:15

E-2 - Sta 2 - 2065 Kiowa
Dispatch Time to Enroute 0:01:26
Dispatch Time to On Scene 0:06:14
Total Time-Dispatch to Available 0:33:30

E-3 - Sta 3 - 3620 Buena Vista
Dispatch Time to Enroute 0:01:17
Dispatch Time to On Scene 0:05:47
Total Time-Dispatch to Available 0:39:24

Averaging times that are not in a range
 

This is an array formula that should be committed by pressing Ctrl-Shift-Enter

Assuming your data is in column A, starting in row 2

=AVERAGE(IF(A2:A14="Dispatch Time to On Scene",B2:B14))

"Nancy D" wrote:

I am trying to get an average response time for numbers that are not listed
in a range. They are formated as a custom format like this: h:mm:ss. I have
been able to get a range to work, but not durations that have other cells
between them. Below is a picture of my table. The three center times,
Dispatch Time to On Scene, are the times I would like to average. Can you
please help me with the correct calculation? Thanks!

Tk-1 - Sta 1 - 96 Acoma
Dispatch Time to Enroute 0:01:29
Dispatch Time to On Scene 0:06:00
Total Time-Dispatch to Available 0:29:15

E-2 - Sta 2 - 2065 Kiowa
Dispatch Time to Enroute 0:01:26
Dispatch Time to On Scene 0:06:14
Total Time-Dispatch to Available 0:33:30

E-3 - Sta 3 - 3620 Buena Vista
Dispatch Time to Enroute 0:01:17
Dispatch Time to On Scene 0:05:47
Total Time-Dispatch to Available 0:39:24

Averaging times that are not in a range
 

I will assume the text is in column A and the times in column B
How about something like =AVERAGE(B5, B9, B13)
But that is going to horrid if the list is long
This is what I did in D2:E4
Dispatch Time to Enroute 0:01:24
Dispatch Time to On Scene 0:06:00
Total Time-Dispatch to Available 0:34:03

The formula in E2 is
=SUMIF(A:A,D2,B:B)/COUNTIF(A:A,D2)
and this is copied down two rows

If you have EXCEL 2007: =AVERAGEIF(A:A,D2,B:B)
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Nancy D" <Nancy wrote in message
...
I am trying to get an average response time for numbers that are not
listed
in a range. They are formated as a custom format like this: h:mm:ss. I
have
been able to get a range to work, but not durations that have other cells
between them. Below is a picture of my table. The three center times,
Dispatch Time to On Scene, are the times I would like to average. Can you
please help me with the correct calculation? Thanks!

Tk-1 - Sta 1 - 96 Acoma
Dispatch Time to Enroute 0:01:29
Dispatch Time to On Scene 0:06:00
Total Time-Dispatch to Available 0:29:15

E-2 - Sta 2 - 2065 Kiowa
Dispatch Time to Enroute 0:01:26
Dispatch Time to On Scene 0:06:14
Total Time-Dispatch to Available 0:33:30

E-3 - Sta 3 - 3620 Buena Vista
Dispatch Time to Enroute 0:01:17
Dispatch Time to On Scene 0:05:47
Total Time-Dispatch to Available 0:39:24

Averaging times that are not in a range
 

I want to thank all three of you for responding to my question, Duke Carey,
Bernard Liengme and P45cal. I tried all of your solutions, and they all did
the trick! I so appreciate your time and expertise!
--
Nancy D


"Nancy D" wrote:

I am trying to get an average response time for numbers that are not listed
in a range. They are formated as a custom format like this: h:mm:ss. I have
been able to get a range to work, but not durations that have other cells
between them. Below is a picture of my table. The three center times,
Dispatch Time to On Scene, are the times I would like to average. Can you
please help me with the correct calculation? Thanks!

Tk-1 - Sta 1 - 96 Acoma
Dispatch Time to Enroute 0:01:29
Dispatch Time to On Scene 0:06:00
Total Time-Dispatch to Available 0:29:15

E-2 - Sta 2 - 2065 Kiowa
Dispatch Time to Enroute 0:01:26
Dispatch Time to On Scene 0:06:14
Total Time-Dispatch to Available 0:33:30

E-3 - Sta 3 - 3620 Buena Vista
Dispatch Time to Enroute 0:01:17
Dispatch Time to On Scene 0:05:47
Total Time-Dispatch to Available 0:39:24