Finding the Min & Max of the middle 50% of a range of data
Linda
I'm working in Excel97 on Win98. I have the data
I need, but I know I've done it the long way round.
I have a range of data, SAT scores, from say
330-800 and I need to get the min score and max
score of the middle 50% (ex. 510-610 was the
high and low scores of the mid 50% of students).
The long way round was counting up and down
to find the middle (my boss's way).
I've read the different functions, came across
quartile and that kinda worked...but I would think
there would be some statistical formula that I'm
missing. This will need to be done a couple times
a year and I would like to find a fairly quick
calculation as I have to have data on numerous majors
within different colleges. I'm not a math major
so any help would be very apprecited!
Thanks
Linda
Bernie Deitrick
For data in A1:A1000
Max:
=LARGE(A1:A1000,INT(COUNT(A1:A1000)/4)+1)
Min:
=SMALL(A1:A1000,INT(COUNT(A1:A1000)/4)+1)
They are approximate unless you have exact multiples of 4 as the number
of datapoint.
HTH,
Bernie
Dick Kusleika
I read that a little bit differently and I just wanted to clarify. Do your
functions isolate the middle 50% based on number of scores or on actual
scores? It looked like number of scores, but when I tested it I got the
right answer. It may have been a fluke though. I started playing with
array formulae and came up with this for the max:
=MAX((B6:B13<QUARTILE(B6:B13,3))*(B6:B13)) array-entered
The min did not work quite as well. All the array items that don't meet the
criteria are evaluated to false and thus zero so the answer is always zero.
One could, however, find the max of the 25th percentile which would be the
next lower score than the one intended
=MAX((B6:B13<QUARTILE(B6:B13,1))*(B6:B13),1) array-entered
If my thinking is right, how could you negate the effects of the zeros in
the array or use the second formula and adjust up one score?
Dick K.
Bernie Deitrick <dei...@consumer.org> wrote in message
news:394A76E2...@consumer.org...
Bernie Deitrick
I like your formulas. To get around the 0s limitation on the MIN array formula,
use and IF formula inside the MIN, like so:
=MIN(IF(B6:B13>QUARTILE(B6:B13,1),B6:B13,MAX(B6:B13)))
The If returns the max value in place of values that don't meet the criteria,
and the max will never be the min.
My formulas using Large and Small find the INT(COUNT(A1:A1000)/4)+1 largest and
smallest. For example, if there are 4 data points, that evaluates to INT(4/4)
+1 or 2, so it finds the 2nd largest and 2nd smallest values, which are the
exact values for the mid 50 percentiles. However, if there are 6 values, then
it still returns the 2nd smallest, and 2nd largest, but I don't think those are
any worse than the values returned by the Quartile formulas, which may return
the middle two values. It just goes to show that statistics don't really work
well with small populations.
Bernie
Dick Kusleika
Thanks for that IF tip. So easy, yet so hard.
Dick K.
Bernie Deitrick <dei...@consumer.org> wrote in message
news:394AA88E...@consumer.org...
Bernie Deitrick
And you really don't need the whole formula (for simple cases)
=MIN(IF(B6:B13>QUARTILE(B6:B13,1),B6:B13,MAX(B6:B13)))
can be simplified to
=MIN(IF(B6:B13>QUARTILE(B6:B13,1),B6:B13))
You can do this since IF returns FALSE if the third argument is left out, and
FALSE is ignored by MIN. However, I usually leave the third argument in since
the return value of the IF is often multiplied by another value, in which case
FALSE * Value does equal 0. It's amazing what you learn hanging out in this
newsgroup.
HTH,
Bernie
Dick Kusleika
I've learned a lot from this thread. I hope Linda did.
Dick
Bernie Deitrick <dei...@consumer.org> wrote in message
news:394AAD6A...@consumer.org...
Linda
I did learn a LOT. This will make my job a heck of a lot
easier.
And Dick...The 50% is on actual scores...no null
or 0 values.
Now I need to get busy trying out all the different
formulas you gave me.
Thanks again...you guys are great.
Linda
Dick Kusleika wrote in message <6jL25.982$j06....@newsfeed.slurp.net>...