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 the Mode function will return that number repeated 2 times. And if all the 3 cells contain different numbers, then we will get an error message as #N/A, as the 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 the 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 us understand the working of the MODE Function in Excel by some MODE Formula examples.
Example #1
In the below-mentioned example, the Table contains the Name of the student in column B (B8 to B24) & the score of each student (C8 to C24). Here, I need to find out the 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.
Click the insert function button (fx) under the 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 & the 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 the 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 a result in the cell C26.
Example #2
MODE.MULT returns more than one result if multiple modes are entered as an array formula. Let’s check out this with the below-mentioned example.
The below-mentioned table it contains the Name of the student in the Name column (G8 to G24) & the score of each student (H8 to H24). Here, I need to find out the 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 to perform 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 the 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 the 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.
First, I have to highlight the range of cells for the function result to apply an array formula, i.e. from H8 TO H24. To highlight, click on the 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 cell J12 to J14.
The 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.
- Those values are ignored if a reference or array argument contains any empty cells, text, and logical values. (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. The below-mentioned example returns the 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 the MODE Function in Excel along with practical examples and downloadable excel templates. You can also go through our other suggested articles –
16 Online Courses | 23 Hands-on Projects | 140+ Hours | Verifiable Certificate of Completion
4.8
View Course
Related Courses
