Quartile Calculation - from a Newbie

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

Having managed to get averages, min, max and even weighted averages for a
data set I want to try to calculate upper and lower quartiles and median.

Looking at historical postings on the topic I may be working beyond my
ability as it looks though it needs to be coded but - and this is where the
newbie comes in, its not clear where the code goes and how I call or access
it :(

Is there some patient (probably very patient <G>) sole who can give me the
steps and, does anyone know of a sample database I can download showing it
in action

TIA

Rob
 
Rob said:
Having managed to get averages, min, max and even weighted averages for a
data set I want to try to calculate upper and lower quartiles and median.

Looking at historical postings on the topic I may be working beyond my
ability as it looks though it needs to be coded but - and this is where the
newbie comes in, its not clear where the code goes and how I call or access
it :(

Is there some patient (probably very patient <G>) sole who can give me the
steps and, does anyone know of a sample database I can download showing it
in action
Click to expand...

Refresh me: how do you calculate a quartile (or any percentile), and a
median? <g> or do I have to find those posts as well?
 
Rob,

Have you done any type of coding before (not necessarily Access). If not,
this might take a while. If you want to see an example of how code works,
create a form and add a command button. Follow the wizard to perform some
action then when you are done, look at the code and see what is going on.

Kelvin
 
Oops <G>

The 20th percentile would be 20% up an ordered list of values. Lower
Quartile=25%, Median=50%, Upper quartile=75%.

HTH
 
Kelvin,

Yes I have done coding before (eg Basic and, in a bygone era, fortran!) but
my experience has always been in stand-alone programs rather than modules of
something else.
 
Come to think of it, you don't really need to write code to perform the
operations you wanted. To find the 20th percentile, top 20%, you can do
this with a query. Create your query that sorts the results from highest to
lowest. Then in a gray area of the query window right click and select
properties. You will see an option for Top Value. This controls which
records to show. Since your results are already sorted, then entering 20
will show the top 20 records. 20% will show the top 20% of the results.
This will give you people in the 20th percentile. You can also make a query
to give the top 25% (top quartile). Resort your list from lowest to highest
and you can get the bottom 25%. You can then find the middle 50% by either
creating 2 queries to give the top 75% then the bottom 66% or the bottom 75%
then the top 66%. You could also create a query to find the unmatched
records using the whole list and the 2 quartile queries.

Kelvin
 
Kelvin,

What a simple solution <BG>. It wasn't quite complete since a percentile is
a single record rather than a group but the final bit is easy.

To calculate say, upper quartile (the 75% percentile), you run your query to
identify the top 25% of records and then another query on the result to
identify the minimum value - voila the answer !!

It might not be totally robust statistically (nor is the excel in-built
function) but it will produce valid answers for my data sets.

Thanks for the inspirational help.

Rob
 
Back
Top