Excel MODE Function (Table of Contents)
MODE in Excel
Mode function in excel is another type of function which allows only numbers. Mode function returns the most frequently occurring number from the selected range or value. For example, if there are 3 cells in the range out of 3, 2 cells contain the same number then Mode function will return that number which is repeated 2 times. And if all the 3 cells contain different numbers then we will get an error message as #N/A, as Mode function couldn’t return anything.
MODE Formula in Excel:
Below is the MODE Formula in Excel.
Explanation of MODE Function in Excel
The MODE Formula in Excel has a below-mentioned argument :
- number1 (compulsory OR required argument) – Arrays of cell reference or numeric values (set of one or more numeric values) for which we have to calculate the mode.
- number2 (Optional OR not required) – Arrays of cell reference or numeric values (set of one or more numeric values) for which we have to calculate the mode.
Note: 255 numbers can be supplied as separate arguments.
MODE.SNGL function returns a single mode & considers first repetitive numeric value whereas MODE.MULT function returns an array of all of the modes. MODE.MULT returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data & return more than one result if there are multiple modes & if it is entered as an array formula (Note: If the MODE.MULT formula is not entered as an array formula, then it returns single result as 8 similar to MODE.SNGL function).
To return a horizontal array, transpose function is added along with MODE.MULT function i.e. TRANSPOSE(MODE.MULT(number1,number2,…))
How to Use the MODE Function in Excel?
MODE Function in Excel is very simple and easy to use. Let understand the working of MODE Function in Excel by some MODE Formula example.
Example #1
In the below-mentioned example, Table contains the Name of the student in column B (B8 to B24) & score of each student (C8 to C24). Here, I need to find out most frequently occurring or repetitive score or value in a range of data by using MODE.SNGL FUNCTION.
Let’s apply MODE.SNGL function in cell “C26”. Select the cell “C26” where MODE.SNGL function needs to be applied.
4.8 (5,839 ratings)
View Course
Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “MODE” in the search for a function box, three options appear in select a function box i.e. MODE, MODE.SNGL & MODE.MULT. The MODE function is used for excel 2007 & earlier version. So, you need to select MODE.SNGL function. Double click on MODE.SNGL function.
A dialog box appears where arguments for MODE.SNGL function needs to be filled or entered i.e. =MODE(number1, [number2], …
=MODE.SNGL (C8:C24) Here the score data is present in the range (C8 to C24) for which we need to apply MODE.SNGL function.
To enter Number 1 argument, click inside cell C8 and you’ll see the cell selected, then Select the cells till C24. So that column range will get selected i.e. C8: C24. Click ok.
After entering the number1 argument. =MODE(C8:C24) i.e. returns the most frequently occurring or repetitive score or value 55 as the result in the cell C26.
Example #2
MODE.MULT returns more than one result if there are multiple modes if it is entered as an array formula. Let’s check out this with a below-mentioned example.
In the below-mentioned Table, it contains Name of the student in the Name column (G8 to G24) & score of each student (H8 to H24). Here, I need to find out most frequently occurring or repetitive score in a range of data by using MODE.MULT FUNCTION.
Here we need a repetitive score for more than one occurrence i.e. MULTIMODE, so we need to use the array formula where array formula performs an operation on multiple values instead of a single value. Therefore, select cells from J12 TO J14, where MODE.MULT is applied.
Let’s apply MODE.MULT function Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “MODE.MULT” in the search for a function box, So, you need to select MODE.MULT function to select a function box. Double click on MODE.MULT function.
A dialog box appears where arguments for MODE.MULT function needs to be filled or entered =MODE.MULT(number1, [number2],
= MODE.MULT (H8:H24) Here the score data is present in the range (H8 to H24) for which we need to apply MODE.MULT function.
To enter Number 1 argument, click inside cell H8 and you’ll see the cell selected, then Select the cells till H24. So that column range will get selected i.e. H8:H24. Click ok.
Result is :
After entering the number1 argument.
To apply an array formula, first I have to highlight the range of cells for the function result i.e. from H8 TO H24. To highlight click on formula toolbar. So that automatically the range H8 TO H24 & J12 to J14 cells gets selected for an array.
Click Ctrl + Shift + Enter so that output values of MODE.MULT returns in the cell J12 to J14.
Array formula applied will appear as {=MODE.MULT(H8:H24)} where MODE.MULT in the formula bar is encased in curly braces { }.
MODE.MULT return more than one result. i.e. 83, 44 & 55.
Things to Remember
- Number arguments can be either of them i.e. names, numbers, references or arrays.
- If a reference or array argument contains any empty cells, text and logical values, those values are ignored. (Note: It will consider cells with zero value)
- If the data set values don’t contain any duplicate values or data points, MODE.SNGL results in or returns #N/A error i.e. No mode.
No repetitive number is found in the below-mentioned data range (A3 TO A9).
The Result will be :
- If the data set values contain any non-numeric values or data points, those values are ignored by MODE.SNGL & returns a #VALUE! error.
The Result will be :
- Combination of range & numeric values argument in MODE Function, in Excel mode Function we can combine range & numeric values argument such as A3:A12 (RANGE) + 3, 7 (Numeric values) to get the desired output. In the below-mentioned example, it returns most frequently occurring number i.e. 3 formula used: =MODE.SNGL(A3:A12,3,7)
The Result will be :
Recommended Articles
This has been a guide to MODE in Excel. Here we discuss the MODE Formula in Excel and How to use MODE Function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
