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

Excel's MODE function can help you find averages

Christin Hume \ Unsplash

Using the MODE function in Excel makes it easy to find the value that occurs most often in a set of selected data. Learn how to find an average with the MODE function in Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, and Excel Online.

MODE Functions

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

Starting with 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

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

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

Consider the following factors before you begin using the MODE function.

  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:
    1. text strings;
    2. logical or Boolean values;
    3. empty cells.

 

MODE Function Example

Excel MODE function example

Two options exist for entering the function's arguments.

  1. You can type in the data or cell references.
  2. You can use 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.

These are the steps to enter the MODE function into cell F2 using the point and click method.

  1. Click on cell F2 make it the active cell;
  2. Type or paste =MODE( into the formula bar.
  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.

The answer 3 should appear in cell F2 since this number appears the most (twice) in the list of data.

When you click on cell F2 the complete function =MODE(A2:D2) appears in the formula bar above the worksheet.