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

1. ## Median, Average, and Standard Deviation from large set of data

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

2. ## RE: Median, Average, and Standard Deviation from large set of data

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

--
Gary''s Student

"Humberto Goyen" wrote:

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

3. ## RE: Median, Average, and Standard Deviation from large set of data

"Gary''s Student" wrote:
> "Humberto Goyen" wrote:
> > 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?

4. The formula

=median(A1:A5000)

will also work.

Regards.

5. ## Re: Median, Average, and Standard Deviation from large set of data

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
"BenjieLop" <BenjieLop.1z9hyy_1133273701.2835@excelforum-nospam.com> wrote
in message news:BenjieLop.1z9hyy_1133273701.2835@excelforum-nospam.com...
>
> The formula
>
> =MEDIAN(A1:A5000)
>
> will also work.
>
> Regards.
>
>
> --
> BenjieLop
>
>
> ------------------------------------------------------------------------
> BenjieLop's Profile:
> http://www.excelforum.com/member.php...o&userid=11019
>

6. ## Re: Median, Average, and Standard Deviation from large set of data

"Humberto Goyen" wrote:
> 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?

7. ## Re: Median, Average, and Standard Deviation from large set of data

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1