Averaging times that are not in a range

[Nancy D]

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

 

[Duke Carey]

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))

 

[Bernard Liengme]

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

 

[p45cal]

With the likes of
=AVERAGE(A5,A12,A19)
and format that cell to the same as the others.
When creating the formula you can hold the control key down whil
selecting the individual celll to average.
Would there be very many cells to average like this, in whci case ther
may be an another answer:

If you have xl2007 you could use the likes of:
=AVERAGEIF(A2:A15,"Dispatch Time to On Scene",B2:B15)

and with earlier versions use:
=AVERAGE(IF(A2:A15="Dispatch Time to On Scene",B2:B15))
BUT, you should ARRAY-ENTER it by using Ctrl+Shft+Enter rather tha
just Enter, to put it into the cell.



I am trying to get an average response time for numbers that are no
listed

 

[Nancy D]

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!