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".

*Press the "print" button on your browser or select "File" - "Print" to print this tip. Then, return to Microsoft Excel 2003 Functions and Formulas - Display the Most Common Value in a Series.*

Standard disclaimer applies - Read http://tipsforspreadsheets.com/copyright-disclaim.html.