Hi, everyone! :)

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

I am working in Excel 2007, and I am attempting to create a formula that automatically returns the median of a quartile of a dataset. I.e. I want the formula to return the median of the top quartile, one to return the 2nd quartile etc. 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.

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. I have 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 right direction :)

Best regards,
Lasse :)

Hi Lasse,

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

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

S.

Hi, S.

Is it not possible to upload an example workbook from excel? I tried, but it says I cannot upload such a file...

Anyways I attached a picture of an example which hopefully helps illustrate the problem.

As you can see I have a table with information in several different columns. (And their ordering changes when I use the filter button.)

I want to create a formula that gives me the median of the top quartile in column P. I.e. the median of the 25% with the highest numbers in this column.

Manually I would have to work out the top 25% of the sample. And calculate the median of this sample. I.e. if there are 100 numbers, I would want the formula to fetch the 13th largest (25% of 100 is 25, and median of 25 is 13).

Do you have any insights as to how I might proceed? My thinking is that I ideally want to use the Quartile function to fetch the numbers somehow - so that the size of the sample can easily be changed without it affecting my calculations.

Best regards,
Lasse

Hi,

The screen grab is too small to read.
If you add your spreadsheet to a .zip file you will be able to attach it to a post here.

Thank you for the help :) Attatched is a .zip file of the sample excel file. As you can see in the file I want to calculate (in W6) the median of the top quartile of the firms ranked by TSR '10-'12 (column P).

The tricky bit is that I want it to be an automatic formula so that I can add/delete companies to the list without it effecting the validity of my result.

My thinking is that I need to use a median function first. And then I need a function that fetches the top quartile of the sample automatically into this formula. (I would also need the same for the 2nd, 3rd and 4th quartile).

Thank you again for the patience. It very nice as I am still trying to get used to posting problems on this site :)

Best,
Lasse

"LFCDRM" wrote:
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%).


"LFCDRM" wrote:
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:A10050,A1:A100),50%)

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

Thanks for the input!

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


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

The MatchIndex function works as I round the sample into equally 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 with it for now :p )


Thx!

"LFCDRM" wrote:
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.


"LFCDRM" wrote:
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(...).


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

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