+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Humberto Goyen
    Guest

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

    Please help!
    Thanks in Advance



  2. #2
    Gary''s Student
    Guest

    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???
    >
    > Please help!
    > Thanks in Advance
    >
    >
    >


  3. #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. #4
    Forum Contributor
    Join Date
    06-23-2004
    Location
    Houston, TX
    Posts
    571
    The formula

    =median(A1:A5000)

    will also work.

    Regards.
    BenjieLop
    Houston, TX

  5. #5
    Humberto Goyen
    Guest

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > The formula
    >
    > =MEDIAN(A1:A5000)
    >
    > will also work.
    >
    > Regards.
    >
    >
    > --
    > BenjieLop
    >
    >
    > ------------------------------------------------------------------------
    > BenjieLop's Profile:
    > http://www.excelforum.com/member.php...o&userid=11019
    > View this thread: http://www.excelforum.com/showthread...hreadid=488849
    >




  6. #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. #7
    Mike Middleton
    Guest

    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



+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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