Find the Average (Mode) with Excel's MODE Function

01
of 01

Find the Most Frequently Occurring Value in a Range of Data

Find the Most Frequent or Average Value with Excel's MODE Function
Find the Most Frequent or Average Value with Excel's MODE Function. © Ted French

MODE and Excel MODE Function Overview

The mode for a list of data values is defined as the most frequently occurring value in the list.

For example, in row two in the image above, the number 3 is the mode since it appears twice in the data range A2 to D2, whereas all other numbers appear only once.

The mode is also considered, along with the mean and median, to be a measure of the average value or central tendency for data.

For a normal distribution of data - represented graphically by a bell curve - the average for all three measures of central tendency is the same value. For a skewed distribution of data, the average value can differ for the three measures.

Using the MODE function in Excel makes it easy to find the value that occurs most often in a set of selected data.

Changes to the MODE Function - Excel 2010 

In Excel 2010, Microsoft introduced two alternatives to using the all-purpose MODE function:

  • MODE.SNGL Function: used for data with a single mode - very similar to the MODE function covered here;
  • MODE.MULT Function: used if the data is likely to have multiple modes - two or more values that occur with the same frequency.

To use the regular MODE function in Excel 2010 and later versions, it must be entered manually, as there is no dialog box associated with it in these versions of the program.

 The MODE Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the MODE function is:

= MODE ( Number1, Number2, Number3, ... Number255 )

Number1 - (required) the values used to calculate the mode. This argument can contain:

  • the actual numbers - separated by commas - the example in row 3 in the image above;
  • a single range containing cell references to the location of the data in the worksheet - the examples in rows 2, 5,6, and 7;
  • a combination of cell references and data - the example in row 4;
  • a named range;
  • individual cell references separated by commas.

Number2, Number3,... Number255 - (optional) additional values or cell references up to a maximum of 255 used to calculate the mode.

Notes

  1. If the selected data range contains no duplicate data, the MODE function will return the #N/A error value - as shown in row 7 in the image above.
  2. If multiple values in the selected data occur with the same frequency (in other words, the data contains multiple modes) the function returns the first such mode it encounters as the mode for the entire data set - as shown in row 5 in the image above. The data range A5 to D5 has 2 modes - 1 and 3, but 1 - the first mode encountered - is returned as the mode for the entire range.
  3. The function ignores:
    • text strings;
    • logical or Boolean values;
    • empty cells.

MODE Function Example

In the image above, the MODE function is used to calculate the mode for several ranges of data. As mentioned, since Excel 2007 there is no dialog box available for entering the function and its arguments.

Even though the function must be entered manually, two options still exist for entering the function's argument(s):

  1. typing in the data or cell references;
  2. using point and click to select the cell references in the worksheet.

The advantage of point and click - which involves using the mouse to highlight the cells of data - is that it reduces the possibilities of errors caused by typing mistakes.

Below are listed the steps used to manually enter the MODE function into cell F2 in the image above.

  1. Click on cell F2 - to make it the active cell;
  2. Type the following: =mode(
  3. Click and drag with the mouse to highlight cells A2 to D2 in the worksheet to enter this range as the function's arguments;
  4. Type a closing round bracket or parenthesis " ) " to enclose the function's argument;
  5. Press the Enter key on the keyboard to complete the function;
  6. The answer 3 should appear in cell F2 since this number appears the most (twice) in the list of data;
  7. When you click on cell F2 the complete function = MODE (A2 : D2) appears in the formula bar above the worksheet.
Format
mla apa chicago
Your Citation
French, Ted. "Find the Average (Mode) with Excel's MODE Function." ThoughtCo, Aug. 13, 2016, thoughtco.com/excel-modesngl-function-4072671. French, Ted. (2016, August 13). Find the Average (Mode) with Excel's MODE Function. Retrieved from https://www.thoughtco.com/excel-modesngl-function-4072671 French, Ted. "Find the Average (Mode) with Excel's MODE Function." ThoughtCo. https://www.thoughtco.com/excel-modesngl-function-4072671 (accessed January 20, 2018).