Calculate Mode Using Excel

Microsoft Excel includes a number of statistical functions, including the ability to figure the mean, median and mode of a data sample. While mean, the average of a group of numbers, and median, the midpoint number of a data group, are used more often, mode, the most frequently appearing number in a data group, can be useful as well, such as using the most frequent numeric grade score to provide feedback on the effectiveness of a teaching method. Here's how to calculate mode using Excel.

Steps

Using the MODE Function

  1. Enter each number in the data set into its own cell. For consistency, it helps to enter the number in consecutive cells in either a row or column, and for readability, a column is better.
  2. Enter the MODE function into the cell in which you wish to display the result. The MODE function's format is "=MODE(Cx:Dy)," where C and D represent the letter of the column of the first and last cell in the range, and x and y represent the number of the first and last row in the range. (Although different letters are used in this example, you will use the same column letter for both the first and last cell if you entered the data in a column of cells or the same row number for both the first and last cell if you entered the data in a row of cells.)
    • You can also specify each cell individually, up to 255 cells, as in "=MODE(A1, A2, A3)," but this is not advisable unless you have only a very small dataset and do not plan to add to it. You can also use the function with constants, for example, "=MODE(4,4,6)," but this requires editing the function each time you wish to search for a different mode.
    • You may want to format the cell in which the mode will display with bolding or italics to distinguish it from the numbers in the dataset.
  3. Calculate and display the result. This normally happens automatically in Excel, but if you have set up your spreadsheet for manual calculation, you'll need to press the F9 key to display the mode.
    • For a dataset of 10, 7, 9, 8, 7, 0 and 4 entered in cells 1 through 8 of Column A, the function =MODE(A1:A8) will deliver a result of 7, because 7 appears more often in the data than any other number.
    • If the data set contains more than one number that qualifies as the mode (such as 7 and 9 each appearing twice and every other number appearing only once), whichever mode number is listed first in the data set will be the result. If none of the numbers in the data set appear more often than any other, the MODE function will display the error result #N/A.
    • The MODE function is available in all versions of Excel, including Excel 2010, which includes it for compatibility with spreadsheets created in earlier versions. Excel 2010 uses the MODE.SNGL function, which except for syntax (=MODE.SNGL(Cx:Dy)) works essentially the same as the MODE function in earlier versions of Excel.

Using the MODE.MULT Function

  1. Enter each number in the data set into its own cell.
  2. Select a range of cells equal to the number of modes you wish to find in the dataset. If you want to find three modes, select a range of three cells. If you select fewer cells than the number of modes you wish to find, you will see only that many modes.
  3. Enter the MODE.MULT function into the formula bar. The MODE.MULT function's format is "=MODE.MULT(Cx:Dy)," where C and D represent the letter of the column of the first and last cell in the range, and x and y represent the number of the first and last row in the range. (As with the MODE function, you'll most usually enter data in cells of the same column and use the same column letter for the first and last cell of the range, or in cells of the same row and use the same row number for the first and last cell of the range.)
    • MODE.MULT can also be used by specifying individual cells or constants within the parentheses, but either option should be used with only very small data sets that you don't plan to change.
  4. Use control+shift+enter to display the result as an array otherwise the result will output the same as MODE.SNGL. If you set your spreadsheet for manual calculation press F9 to see the result.
  5. Unlike MODE.SNGL, MODE.MULT displays multiple modes. For a data set of 2,1,3,4,3,2,1,7,1,2,3,8 entered in cells 1 through 12 of Column A, the formula =MODE.MULT(A1:A12) will return 1, 2 and 3 as the modes, as each appears three times in the data set.
  6. If no number in the data set appears more often than any other, the MODE.MULT function will display the error result #N/A.
  7. The MODE.MULT function is available only in Microsoft Excel 2010.



Tips

  • When adding more numbers to a data set, check the formula to be sure that it still accurately represents the first and last numbers in the range of cells. If you insert rows before the original first number in the range and enter data in them, you'll have to adjust the first cell number in the range. If you insert rows and add more numbers after the original last number in the range, you'll have to adjust the last cell number in the formula. If you insert rows in the middle, however, the range of cells will adjust automatically.

Warnings

  • Using the MODE.MULT formula to find a large number of modes at once may slow down your computer if it lacks sufficient processing speed and memory.

Related Articles

  • Calculate Range
  • Calculate Average or Mean of Consecutive Numbers
  • Calculate the Mode of Text in Excel 2010

Sources and Citations

Retrieved from "https://kipkis.com/index.php?title=Calculate_Mode_Using_Excel&oldid=351216"