# Thread: Median and Mode formulas (Excel XP)

1. ## 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

2. ## 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. ## 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. ## 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.

5. ## 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. ## 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.

#### Posting Permissions

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