Microsoft Excel 2003 Functions and Formulas
Display the Most Common Value in a Series
Summary: This Excel 2003 function calculates the most frequent value in a series.
There may be instances where you need to report the most common value present in a particular series. For example:
* What is the most common grade students made on a particular test or in a particular class?
* What was the most common weekly profit-to-earnings ratio in a year?
* What is the most common age in a group of survey respondents?
To produce this value, use Microsoft Excel 2003's =MODE function, followed by the range of values.
For example, if a spreadsheet contained the following values:
Column A: A group of survey respondents.
Column B: Their ages.
A1: Bob
A2: Sally
A3: Tom
A4: Nancy
B1: 27
B2: 22
B3: 27
B4: 28
B5: =MODE(B1:B4)
The result of B5's formula would be 27, as 27 appears most often in B1:B4, specifically in cells B1 and B3.
Two caveats:
* If a series contains two or more sets of values represented equally, the value represented first in the series would be the result.
* If no value is represented more than once in a series, Excel will display "#N/A" in the cell. You can change this result by using the following more complex formula:
=IF(ISNA(MODE(series)),desired_value,MODE(series))
* Replace "series" with the data series to calculate
* Replace "desired_value" with the value to display in the cell if a series does not contain any duplicate elements.
For example, if an Excel spreadsheet contained the following values:
B1: 27
B2: 22
B3: 28
B4: 21
B5: =IF(ISNA(MODE(B1:B4)),0,MODE(B1:B4))
The result of B5's formula would be 0 instead of "#N/A".
Add: 
Del.icio.us | 
Digg | 
Furl | 
My Yahoo!
Last Modified on: July 13, 2008, at 12:04 A.M. EDT
Return to the Microsoft Excel 2003 Functions and Formulas page.
Search the Site: