Results 1 to 6 of 6
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Median and Mode formulas (Excel XP)

    I'm curious about Excel's formula's for Median and Mode. What are the mathmatical formulas that Excel is using for these two functions?

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Median and Mode formulas (Excel XP)

    The median of a series of arguments is calculated as follows:
    <UL><LI>Throw out empty and non-numeric arguments. They will be ignored in the calculation.
    <LI>Sort the numeric arguments in ascending order.
    <LI>If the number of numeric arguments is odd, pick the middle one. This is the median.
    <LI>If the number of numeric arguments is even, calculate the average of the two middle ones. This is the median.[/list]Example: =MEDIAN(33,8,"Lounge",27,5)
    Ignore the text argument and sort the rest:
    5, 8, 27, 33
    There is an even number of numeric arguments. The two middle ones are 8 and 27. The median is their average (8+27)/2 = 17.5

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Median and Mode formulas (Excel XP)

    Just as for Median, there is no formula for computing the Mode, just an algorithm (a recipe).

    <UL><LI>Throw out empty and text arguments. These will be ignored.
    <LI>Compute the number of occurrences of each unique value among the numeric arguments.
    <LI>If none of the unique arguments occurs more than once, return #N/A.
    <LI>Otherwise, find the highest frequency. Return the first argument with that frequency as Mode.[/list]Example: =MODE(8,8,2,3,3,6)

    <table border=1><td>Argument</td><td>Frequency</td><td align=right>8</td><td align=right>2</td><td align=right>2</td><td align=right>1</td><td align=right>3</td><td align=right>2</td><td align=right>6</td><td align=right>1</td></table>
    The highest frequency is 2, and the first argument with frequency 2 is 8. This is returned as the Mode.

    Note: AFAIK, mathematicians would say that 8 and 3 are both Mode values. Excel returns only one.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Median and Mode formulas (Excel XP)

    For both median and mode, Excel Help states that zeros are counted. Thus in the series:

    0, 28, 8, 0, 12, 0, 7, 19

    modal value per Excel is 0 and median per Excel is 7.5.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Median and Mode formulas (Excel XP)

    Hi John, thanks for the addition. Like in other statistical functions such as AVERAGE, empty and text values are ignored (that is what I stated in my replies), but 0 values are included. empty values are NOT treated as zeros in statistical functions.

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Median and Mode formulas (Excel XP)

    Thanks guys - I wondered if they had some MEDIAN and MODE formulas that I wasn't aware of.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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