Median, Percentile, MAX Based on Criteria From Another Column

[PWD]

Good morning. It's my Birthday and I've had to come in to work!!!! Brain's a bit fuzzy!

Anyway. Is it possible to obtain the Median, Percentile, MAX based on criteria from another column? Basically, every month we will be adding figures to a worksheet and will need to calculated Year To Date figures. The first column will be patients who have either been "Admitted" or "Non Admitted" following a visit to our Accident & Emergency Department (ED). The second column will be the length of time each patient spent in the Department. What I would like to be able to do is create a formula for each measure where currently this is amended monthly when new data are added, i.e.:

=PERCENTILE(B5:B1467,0.95) is the range of Admitted patients (so far)

=PERCENTILE(B1468:B5402,0.95) is the range of Non Admitted patients (so far)

So, is there any way to make the range dynamic like I could if I were just doing a total, e.g.

=SUMPRODUCT((A5:A50000="Admitted")*(B5:B50000))

=SUMPRODUCT((A5:A50000="Non Admitted")*(B5:B50000))

Many thanks,
D€$

 

[PWD]

Should I be looking at AGGREGATE?

Many thanks,
D€$

 

[PWD]

=AGGREGATE(14,6,(B5:B50000)*(A5:A50000="Non Admitted"),1) = "MAX"

=AGGREGATE(16,6,(B5:B50000)/(A5:A50000="Non Admitted"),0.95) = "Percentile"

Just got to get the syntax for Median,

Many thanks,
D€$

 

[PWD]

Oh, it appears as if it's the 50th Percentile that equals Median.

=AGGREGATE(16,6,(B5:B50000)/(A5:A50000="Non Admitted"),0.5)

Bit wired today as I only drink decaff but my colleagues bought me a cafetiere and some real coffee so it would have been rude not to use it! Chocolate cream cakes also adding to the caffeine quotiant!!

Many thanks,
D€$

 

[Andrzejek]

Have you considered using structured table in Excel?

Also, you do know you can edit your own posts, right?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Deniall]

Andrzejek.[ ] How does one edit one's own post?
Thanks, Skip.

 

[SkipVought]

Bottom right-hand corner of the post you want to edit.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Deniall]

Thanks, Skip.[ ] (See above.)

 

[SkipVought]

Was that prophetic?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PWD]

Hi Andy, this is data that's coming out of Business Objects so that's enough for our people to be going on with for now!!

Many thanks,
D€$