Results 1 to 6 of 6

20030501, 22:47 #1
 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

20030501, 23:18 #2
 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 nonnumeric 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

20030501, 23:29 #3
 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.

20030501, 23:37 #4
 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

20030501, 23:45 #5
 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.

20030502, 01:13 #6
 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