Formula help

[lee009]

Hello,

I need help with a formula. Below is a very small sample size of data I have. Typically, I have thousands of Incident numbers (column A) with hundreds to each Shift-Station(column D). I need to get the median time (column E) for each Shift-Station. Is there an easy formula to type that will give me the median based on when the Shift-Station cells change? My method right now takes way too long; I sort the report based on the Shift-Station (column D); scroll all the way thru the document until the Shift Station changes (example, from 1a to 2b), insert a row and then type use formula

=median(e2:to whatever cell the Shift-Station changes).

A	B	C	D	E
Incident No	Shift	Station	Shift-Station	Time
1	a	1	1a	5:14
2	a	1	1a	5:55
3	a	2	2a	6:33
4	b	2	2b	4:45
5	b	3	3b	6:11
6	b	3	3b	5:34
7	c	4	c4	5:12
8	c	5	c5	7:45

<tbody>
</tbody>

 

[mrhstn]

Hi

Data >> Subtotals

At Each: Shift-Station

Use Function: Average

Add subtotal to: Time

Click ok

Now do find/replace (ctrl+H)

Find: SUBTOTAL(1,
Replace: AGGREGATE(12,6,

You can also search and replace Average for Median.

HTH