+ Reply to Thread
Results 1 to 3 of 3

median function

  1. #1
    Registered User
    Join Date
    08-09-2006
    Posts
    30

    median function

    In Column A I have the ages of a random number of people. I need to take the median of the top 25% of this list. First I sorted the list, and now i need to run Median(A1:AX) Where X is the cell that equals 25% of my total population. My problem is the population(and thus the number of rows used) is constantly changing. How can I put in a value for AX into this equation? Thanks in advance.
    Karl

  2. #2
    Toppers
    Guest

    RE: median function

    try:

    =MEDIAN(INDIRECT("A1:A"&INT(COUNTA(A:A)/4)))

    This rounds down so that if there are 27 numbers, median is calculated as A1
    to A6 ....should it be A1 to A7?

    HTH

    "flyingmeatball" wrote:

    >
    > In Column A I have the ages of a random number of people. I need to
    > take the median of the top 25% of this list. First I sorted the list,
    > and now i need to run Median(A1:AX) Where X is the cell that equals 25%
    > of my total population. My problem is the population(and thus the
    > number of rows used) is constantly changing. How can I put in a value
    > for AX into this equation? Thanks in advance.
    > Karl
    >
    >
    > --
    > flyingmeatball
    > ------------------------------------------------------------------------
    > flyingmeatball's Profile: http://www.excelforum.com/member.php...o&userid=37302
    > View this thread: http://www.excelforum.com/showthread...hreadid=572682
    >
    >


  3. #3
    Barb Reinhardt
    Guest

    RE: median function

    Let's say the ages are in A2:A26.

    I'd use the following function

    =MEDIAN(IF(A2:A26>QUARTILE(A2:A26,3),A2:A26))

    COMMIT using CTRL SHIFT ENTER and you should see {} around the whole thing.

    I'd probably use a named range with an offset to determine the dynamic range
    for A2:A26, but that's another whole conversation. Check here for
    information on dynamic ranges

    http://www.cpearson.com/excel/named.htm

    "flyingmeatball" wrote:

    >
    > In Column A I have the ages of a random number of people. I need to
    > take the median of the top 25% of this list. First I sorted the list,
    > and now i need to run Median(A1:AX) Where X is the cell that equals 25%
    > of my total population. My problem is the population(and thus the
    > number of rows used) is constantly changing. How can I put in a value
    > for AX into this equation? Thanks in advance.
    > Karl
    >
    >
    > --
    > flyingmeatball
    > ------------------------------------------------------------------------
    > flyingmeatball's Profile: http://www.excelforum.com/member.php...o&userid=37302
    > View this thread: http://www.excelforum.com/showthread...hreadid=572682
    >
    >


+ 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