Finding the most frequently occurring number in a list, such as the best-selling shoe size or the most common exam score, is a small but frequent statistical task. Excel provides two functions for this, and they are easily confused: MODE.SNGL and MODE.MULT. The difference lies not just in the name, but in the number of results returned and how the formula is entered.
Single number: MODE.SNGL
The syntax is =MODE.SNGL(number1, [number2], ...). You can provide a single range of cells or up to 254 separate arguments. Microsoft's documentation gives an example with data 5,6; 4; 4; 3; 2; 4 in cells A2 to A7. The formula =MODE.SNGL(A2:A7) returns 4, as that number appears three times.
There are a few rules to remember:
- Text, logical values (TRUE/FALSE), and empty cells within the range are ignored. Cells containing zero are still counted.
- Error values in the data, or text that cannot be converted to a number when typed directly as an argument, will cause the formula to return an error.
- If no number is repeated, the result is
#N/A. This does not indicate a broken formula. It simply means there is no mode. Adding one of the same number to the data will immediately change the result to that number.
Microsoft's page for MODE.SNGL does not explain what happens if two numbers appear with the same frequency. Therefore, do not rely on MODE.SNGL for such data. Use MODE.MULT instead.
Multiple numbers at once: MODE.MULT
MODE.MULT uses the same arguments but returns a vertical array containing all the numbers that have the highest frequency. An example in Microsoft's documentation uses the data 1, 2, 3, 4, 3, 2, 1, 2, 3, 5, 6, 1. The numbers 1, 2, and 3 each appear three times, resulting in three numbers: 1, 2, and 3.
Since the result is more than one cell, Microsoft states that this function must be entered as an array formula. The procedure is: select several empty cells in a column, type the formula, and then press Ctrl+Shift+Enter. Any excess cells will display #N/A, as the modes have been exhausted.
The most common mistake: the formula is typed normally in a single cell and then Enter is pressed. According to the same documentation, a MODE.MULT formula entered as a regular formula will only return the first number, which is 1 in the above example, just like MODE.SNGL. So if you expect multiple numbers but only one appears, check how you entered the formula. For horizontal results, wrap it with TRANSPOSE, like =TRANSPOSE(MODE.MULT(A2:A13)).
What about MODE?
The older MODE function still exists, but Microsoft marks it as a compatibility function that has been replaced by MODE.SNGL and MODE.MULT. Its page mentions that this function may not be available in future versions of Excel. For new files, use the new names.
Hiding #N/A
If your data sometimes does not have repeating numbers, and you want the cell to display a neat text, wrap it with IFERROR. For example, =IFERROR(MODE.SNGL(A2:A20), "No mode"). IFERROR handles #N/A along with other types of errors and returns a substitute value that you specify. Be careful: IFERROR also hides other errors, so incorrectly typed data may also be obscured.
Summary
Choose MODE.SNGL if you are satisfied with a single number and the data clearly has a winner. Choose MODE.MULT if a tie may occur, and enter it as an array formula. Remember that #N/A means there are no repeating numbers, not that the formula is wrong.
If you prefer to see the explanation directly, there is a clip at taalenta.id/video/dec-cari-angka-paling-sering-muncul-di-excel-mode-sngl-atau-mode-mult.
Sources
- Microsoft Support, "MODE.SNGL function": https://support.microsoft.com/en-us/office/mode-sngl-function-f1267c16-66c6-4386-959f-8fba5f8bb7f8
- Microsoft Support, "MODE.MULT function": https://support.microsoft.com/en-us/excel/functions/mode-mult-function
- Microsoft Support, "MODE function": https://support.microsoft.com/en-us/office/mode-function-e45192ce-9122-4980-82ed-4bdc34973120
- Microsoft Support, "IFERROR function": https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611