Table Of Contents
MODE Function in Excel
The MODE function is categorized as statistical function in Excel. In mathematical terms, the MODE returns the MODE for a given data set. For example, the MODE in Excel returns the most often occurring or repetitive value in an array or range of data.
For example, =MODE(1,2,3,4,5,6,6,7) returns 6.
MODE Formula in Excel
Parameters
The MODE formula has the following parameters: number1 and .
- number1: It is an array of the number parameters or a set of one or more numeric values for which we want to calculate the mode.
- : It is an array of optional parameters.
How to Use MODE Function in Excel?
The MODE function in Excel is very simple and easy to use. Let us understand the working of the MODE function by some examples. The MODE formula can be used as a worksheet function and VBA function.
MODE Function can be used as a VBA Function
Suppose we have the data sets located in the Excel sheet ranging from A1 to A10. Then, we can calculate the MODE formula with the given data sets by using the below VBA functions:
Sub MODEcal() // start the MODE function scope
Dim x as Range //declare the range x and y
Dim MODE as integer
set x = Range("A1:A10") //set data sets to Range x.
MODE = Application.worksheetfunction.mode(x)
MsgBox MODE // print the MODE value in message box.
End sub // End the MODE function
Things to Know about the MODE Function in Excel
- The arguments can be numbers or names, arrays, or references containing numbers.
- This function ignores the values if an array or reference argument contains text, logical values, or empty cells. However, any cells with the value zero are included.
- The arguments that are error values or text we cannot translate into numbers cause errors.
- The MODE function gives #NUM! Error if no duplicates/repetitive values exist in the supplied data sets. Hence, there is no mode within the provided values.
=MODE(A28:A37) as there are no duplicate values in the supplied data set MODE function through #N/A error.
- The MODE function provides #VALUE! Error, when a value supplied directly to the MODE is non-numeric and not part of an array. The MODE function ignores non-numeric functions in the array of values.
=MODE(C28:C37,"hdd")
