Help in Median of a quartile

[LFCDRM]

Hi, everyone!

This is the first time using this forum - so please bear with me i
there is some norms I have missed!

I am working in Excel 2007, and I am attempting to create a formula tha
automatically returns the median of a quartile of a dataset. I.e. I wan
the formula to return the median of the top quartile, one to return th
2nd quartile etc. The dataset I am fetching the data from must still b
able to change the order of the numbers as I am using the filte
function for it.

I have tried something like
=MEDIAN(QUARTILE(E4:E7;4);QUARTILE(E4:E7;3)) - but this doesn't work a
it only provides me with the median of the two numbers provided. I hav
also tried to use Index and Match functions within the Median function
but it doesn't work (or at least I can't make it!)

I would be very grateful if someone could help me/guide me in the righ
direction

Best regards,
Lasse :

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

Hi, everyone!

This is the first time using this forum - so please bear with me i
there is some norms I have missed!

I am working in Excel 2007, and I am attempting to create a formula tha
automatically returns the median of a quartile of a dataset. I.e. I wan
the formula to return the median of the top quartile, one to return th
2nd quartile etc. The dataset I am fetching the data from must still b
able to change the order of the numbers as I am using the filte
function for it.

I have tried something like
=MEDIAN(QUARTILE(E4:E7;4);QUARTILE(E4:E7;3)) - but this doesn't work a
it only provides me with the median of the two numbers provided. I hav
also tried to use Index and Match functions within the Median function
but it doesn't work (or at least I can't make it!)

I would be very grateful if someone could help me/guide me in the righ
direction

Best regards,
Lasse

Hi Lasse,

I think an example workbook would be beneficial here in enabling us t
help you out.

If you could include some information on what you think the result
should be and how you'd arrive at them if manually calculating tha
would be very helpful too.

S

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[joeu2004]

I am attempting to create a formula that
automatically returns the median of a quartile of a dataset. [....]
I have tried something like:
=MEDIAN(QUARTILE(E4:E7;4);QUARTILE(E4:E7;3))
- but this doesn't work as it only provides me
with the median of the two numbers provided.

Note that MEDIAN(A1:A100) is the same as QUARTILE(A1:A100,2), which is the
same as PERCENTILE(A1:A100,50%).

Likewise, QUARTILE(A1:A100,1) is the same as PERCENTILE(A1:A100,25%);
QUARTILE(A1:A100,3) is the same as PERCENTILE(A1:A100,75%); and
QUARTILE(A1:A100,4) is the same as PERCENTIlE(A1:A100,100%), which is the
same as MAX(A1:A100).

So the median of the 4th quartile is the percentile between 75% and 100%,
namely PERCENTILE(A1:A100,87.5%).

The median of the 3rd quartile is between 50% and 75%, namely
PERCENTILE(A1:A100,62.5%).

The median of the 2nd quartile is between 25% and 50%, namely
PERCENTILE(A1:A100,37.5%).

And the median of the 1st quartile is between 0 and 25%, namely
PERCENTILE(A1:A100,12.5%).

I am working in Excel 2007 [...].
The dataset I am fetching the data from must still
be able to change the order of the numbers as I am
using the filter function for it.

That's a bigger nut to crack.

MEDIAN, QUARTILE and PERCENTILE do not care about the order of the data.

But they are oblivious to rows that are hidden due to filtering.

For example, PERCENTILE(A1:A100,50%) returns the same value even if A1:A100
are the numbers 1 through 100 and filter numbers > 50.

Ostensibly, the SUBTOTAL function exists for this purpose. But SUBTOTAL
does not have an option for PERCENTILE.

Excel 2010 introduced the AGGREGATE function, which does have an option for
PERCENTILE.INC, the equivalent of PERCENTILE. So you might write
AGGREGATE(16,5,A1:A100,50%) to find the median of the filtered data.

But Excel 2007 does not have the AGGREGATE function.

Perhaps instead of using the Data Filter operation, you can use an array
formula to "filter" the data.

For my example above, you might array-enter the following formula (press
ctrl+shift+Enter instead of just Enter):

=PERCENTILE(IF(A1:A100>50,A1:A100),50%)

That finds the median of all values in A1:A100 greater than 50.

 

[LFCDRM]

Thanks for the input!

I am not sure if the percentile calculations (despite the calculatio
makng intuitive sense gave me the correct numbers....I.e. the 12,5
percentile with a dataset from 1-28, does not return the nr.4 (median o
7, as 7*4=28), but rather 4,375)...

In the meantime I have decided to use a simplification to solve th
issue. The formula I am using is:

=MEDIAN(INDEX('Raw data by country'!$O$6:$O$42;1;1):INDEX('Raw data b
country'!$O$6:$O$42;ROUND(COUNT('Raw data b
country'!$O$6:$O$42)/4;0);1)

The MatchIndex function works as I round the sample into equall
sizeable chunks. Not mathematically perfect (due to the rounding issue)
but at least it provides me with a good proxy :


(And I can't use the filter function, but I guess I'll have to live wit
it for now

Thx

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[joeu2004]

I am not sure if the percentile calculations (despite
the calculation makng intuitive sense gave me the
correct numbers....I.e. the 12,5% percentile with a
dataset from 1-28, does not return the nr.4 (median of
7, as 7*4=28), but rather 4,375)....

Actually, it is correct.

This has to do with "the" statistical definition of PERCENTILE. See the
help page, to wit:

"If k is not a multiple of 1/(n - 1), PERCENTILE __interpolates__ to
determine the value at the k-th percentile".

The same can be said for MEDIAN. Note that MEDIAN({1,2,3,4,5,6,7,8,9,10})
is 5.5, not 5.

I write "the" statistical definition because it is really "a" definition.
IIRC, there is more than one commonly-accepted way to define the percentile.

What you need to remember is: the x% percentile is the data point
__below_which__ comprises x% of the data. So by that definition,
PERCENTILE(...,100%) is invalid(!) -- unless it returns "a little more than"
the max data point.

In the meantime I have decided to use a simplification
to solve the issue. The formula I am using is:
=MEDIAN(INDEX('Raw data by country'!$O$6:$O$42;1;1):
INDEX('Raw data by country'!$O$6:$O$42;
ROUND(COUNT('Raw data by country'!$O$6:$O$42)/4;0);1))

Whatever blows your skirt!

FYI, the first INDEX expression is unnecessary. Simply write 'Raw data by
country'!$O$6:INDEX(...).

And I can't use the filter function, but I guess I'll
have to live with it for now )

You can probably develop an array-entered formula to perform the necessary
filter. Without details, there is nothing more that we can say.