Excel 2010 Median Formula

[PWD]

Good morning, I need to find the Median from a list that has been generated from a Business Objects crosstab (its 'equivalent' of a pivot table). Also, this is only test data so it's not great. Basically there is a month Column, "C" that corresponds to dates in Row 1, a location Column, "A" that corresponds to Column "H" and a length of stay Column, "B"; so there will/may be several entries for each month and location for different lengths of stay.

Ward Spell LoS Month Discharge
CCU 1 01/07/2015
CCU 3 01/07/2015
G1 5 01/08/2015
G4 1 01/07/2015
G4 4 01/08/2015
G5 1 01/07/2015
G5 1 01/08/2015
G5 4 01/08/2015
G5 5 01/08/2015
G8 1 01/07/2015
G9 14 01/08/2015
G9 15 01/08/2015
G9 28 01/08/2015

At present I use this to get the SUM:

=SUMPRODUCT(--($C$2:$C$24=$I$1),--($A$2:$A$24=$H2),($B$2:$B$24))

Looking at the above formula the second array shows me {1;1;0;0;0;0;....}, which is where the location in Column "A" matches the location in Column "H" ("CCU") - but I'm not sure if that's of any real help!

Any thoughts?

Many thanks,
D€$

 

[PWD]

I think I may have been able to extract this from Business Objects.

Ward Month Discharge Median LoS
CCU 01/07/2015 1
G1 01/08/2015 0
G4 01/07/2015 10
G4 01/08/2015 0
G5 01/07/2015 0
G5 01/08/2015 0
G8 01/07/2015 1
G9 01/08/2015 15
F7 01/07/2015 1
F8 01/07/2015 2.5
F8 01/08/2015 1
F9 01/08/2015 0
F10 01/07/2015 0
F10 01/08/2015 0


How would I be able to cross-refer this to something like this?

Ward 01/04/2015 01/05/2015 01/06/2015 01/07/2015 01/08/2015
CCU
G1
G3
G4

I was thinking some sort of VLOOKUP that also matched the Month Discharge, just can't figure it out.

Many thanks,
D€$

 

[PWD]

OK I've got:

{=IFERROR(INDEX($C$2:$C$17,MATCH($F2,IF($B$2:$B$15=J$1,$A$2:$A$15),0)),"")}

My columns have headers so I need to offset this all by 1 if I want to use "C:C", "B:B" & "A:A". Where would that go in?

Many thanks,
D€$

 

[PWD]

Oh, this appears to work:

=IFERROR(INDEX($C:$C,MATCH($F2,IF($B:$B=J$1,$A:$A),0)),"")

Many thanks,
D€$

 

[SkipVought]

DE,

First please don't use QUOTE for your table, as you can see the unsatisfactory results in your post. RATHER use PRE.../PRE.

I don't believe that your formula will return the MEDIAN() of a subset in your table. I used G9 in yur example which would return a MEDIAN() of 15 for 1/8/2015. I can't get your formula to return that value.

Without regard to the Discharge Date, this formula returns 15 for the MEDIAN for G9...
[tt]
=MEDIAN(OFFSET(tPWD[Spell],MATCH(F2,tPWD[Ward],0)-1,0,COUNTIF(tPWD[Ward],F2),1))
[/tt]
Oh, yes, I'm using Structured Table notation, where your table name is tPWD.

The OFFSET() function returns an array that is a subset of your table. Your table MUST Be SORTED CORRECTLY, in order for this to work: by Ward, Discharge Date.

So if I were to include Discharge Date in the above formula, here's what I'd do: Create a new HELPER column that concatenates the Ward and the Discharge Date and use THAT HELPER column rather than the Ward column. So then my Lookup Value in F2 would be the concatenated value of G9 and the 1/8/2015 heading. Keep in mind that dates are really NUMBERS.

Skip,

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

 

[SkipVought]

Okay, you got a report that aggregates the median Los by ward and discharge date.

So to pivot the dates then a simple SUMPRODUCT()

[tt]
I2: =SUMPRODUCT((tPWD[ward]=$H2)*(tPWD[DischargeDate]=I$1)*(tPWD[Median LoS]))
[/tt]

Assumes that your new report/table is a Structured Table named tPWD and your pivot begins in H1, where row 1 is dates and column H is wards.

Skip,

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

 

[PWD]

Hi Skip, thanks for the tip re PRE /PRE, I'll have to remember that.

For some reason the SUMPRODUCT doesn't work on my Business Objects output - probably something to do with the formatting of the dates - I HATE BUSINESS OBJECTS!!!!! That's why I've had to do it that way.

As ever I'm feeding data from a new source and trying to keep the existing reports. Following your evangelical enthusiasm for Tables, I started using them whenever possible a few years ago.



Many thanks,
D€$

 

[SkipVought]

I remember having a similar love affair with Business Objects.

Skip,

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

 

[SkipVought]

BTW, if your "SUMPRODUCT doesn't work", its moste likely because this is the problem...
[tt]
tPWD[DischargeDate]=I$1
[/tt]

meaning the DischargeDate DATA and the date DATA in row 1 are different data types. Did you happen to make the table that you are pivoting into a Structured Table? If your did, the Headings that are Dated have been converted to TEXT in the Structured Table Creation process! That's one possible explanation why "SUMPRODUCT doesn't work"!

Skip,

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

 

[PWD]

Thanks Skip, yes I tried all the formatting permutations and combinations that I could think of, including using Format Painter, and using simple B2 = F1 just to check what was recognising what. Back to Business Objects now before 'they' start to rebuild the Universe and I lose the ability to refresh the data.

Many thanks,
D€$