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


[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]Incident No[/TD]
[TD]Shift[/TD]
[TD]Station[/TD]
[TD]Shift-Station[/TD]
[TD]Time [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]1[/TD]
[TD]1a
[/TD]
[TD]5:14[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]a[/TD]
[TD]1[/TD]
[TD]1a[/TD]
[TD]5:55[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]a[/TD]
[TD]2[/TD]
[TD]2a[/TD]
[TD]6:33[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]b[/TD]
[TD]2[/TD]
[TD]2b[/TD]
[TD]4:45[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]b[/TD]
[TD]3[/TD]
[TD]3b[/TD]
[TD]6:11[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]b[/TD]
[TD]3[/TD]
[TD]3b[/TD]
[TD]5:34[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]c[/TD]
[TD]4[/TD]
[TD]c4[/TD]
[TD]5:12[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]c[/TD]
[TD]5[/TD]
[TD]c5[/TD]
[TD]7:45[/TD]
[/TR]
</tbody>[/TABLE]

 

[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