Median, Average, and Standard Deviation from large set of data

[Humberto Goyen]

Hi all,

I'm stumped. I have a very large set of data (over 200.000 records in
MSAccess DB) that I'm analyzing using Pivot Tables. Average and Standard
deviation functions are "built-in" so no problem there... now, how can I
calculate the median???

Please help!
Thanks in Advance

 

[Guest]

Sort them, go half-way down and select:


if data is in A1:A5000 or there-abouts, then sort and then

=index(A1:A5000,count(A1:A5000)/2) should be the median

 

[Guest]

I have a very large set of data (over 200.000 records in
MSAccess DB) that I'm analyzing using Pivot Tables.
[....] how can I calculate the median???

Sort them, go half-way down and select:
if data is in A1:A5000 or there-abouts, then sort and then
=index(A1:A5000,count(A1:A5000)/2) should be the median

Besides being the wrong definition for the median of an
even number of data, if that were the right approach, why
not simply use MEDIAN() or QUARTILE(...,2) and dispense
with the sorting?

 

[BenjieLop]

The formula

=MEDIAN(A1:A5000)

will also work.

Regards

 

[Humberto Goyen]

Thanks Guys... but the problem is the underlying data set my pivot table is
over 200.000 rows! so i need an alternate approach ... maybe the problem i'm
posing can't be solved in Excel!

thanks
Humberto

 

[Guest]

Thanks Guys... but the problem is the underlying data
set my pivot table is over 200.000 rows! so i need an
alternate approach ... maybe the problem i'm posing
can't be solved in Excel!

That's what I thought would be the problem. I was simply
saying that if the solution to your problem were as "simple"
as "Gary's Student" made it seem, there is as usual an even
more straight-forward solution along the same lines.

But I suspected that the problem was much more complex
than "Gary's Student" response might suggest. I was hoping
that someone familiar with pivot tables might comment.

I know nothing about pivot tables. But having just reviewed
some tutorial information, I confess that I am confused by
your initial question and last response.

You said there is "no problem" with computing the avg and
std dev. How are you doing that, if not by using AVERAGE()
and STDEVP()? And alternatively, if you are using those
functions, why doesn't MEDIAN() satisfy your needs just as
well?

 

[Mike Middleton]

joeu2004 wrote :

I know nothing about pivot tables. But having just reviewed some tutorial
information, I confess that I am confused by your initial question and
last response. You said there is "no problem" with computing the avg and
std dev. How are you doing that, if not by using AVERAGE() and STDEVP()?
And alternatively, if you are using those functions, why doesn't MEDIAN()
satisfy your needs just as well? <

Average, StdDev, and StdDevP are among the many summary functions available
as Field Settings in pivot tables. Median is not available.

A relevant Help topic in Excel 2003 is "Summary functions available in
PivotTable and PivotChart reports."

- Mike
www.mikemiddleton.com