WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » MODE Excel Function

MODE Excel Function

By Tanuj KumarTanuj Kumar | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

By Tanuj Kumar

MODE Function in Excel

MODE Function is categorized as statistical functions in Excel. In mathematical terms, the MODE returns the MODE for a given data set. The Mode in Excel returns the most often occurring, or repetitive, the value in an array or range of data.

MODE Formula in Excel

MODE Formula in Excel

Parameters

MODE formula has the following parameters. i.e. number1 and [number2].

  • 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.
  • [number2]: it is an array of optional parameters

How to Use MODE Function in Excel?

MODE function in Excel is very simple and easy to use. Let us understand the working of the MODE Function by some examples. MODE Formula can be used as a worksheet function and as a VBA function.

You can download this MODE Function Excel Template here – MODE Function Excel Template

Example #1

Let’s consider a given set of numbers, and we have to calculate the MODE formula in excel of them, then apply the MODE Formula on the given values, as shown in the below table.

=MODE(A3:A22)  output will be 15.

MODE Function in Excel - Example -1

Example #2

Let consider a given set of values here; there are two logical Values in the given data set. Now apply the MODE Formula here. The MODE Function ignores the logical value and returns the output.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

=MODE(C3:C22) and output will be 15.

Example -2

Example #3

In the MODE Formula example, we have a data set with non-numeric values, empty value, and zero in the data, MODE function simple ignore the non-numeric value empty value but consider the zero as value and provide the most repetitive value from the given set of data as shown in the below table.

=MODE(E3:E22) and output will be 15.

Example -3

Example #4

In the MODE Formula example, we have a given set of data in the horizontal table, and we apply the MODE function to get the most repetitive values from the given set of data.

Example -4

MODE Function can be used as a VBA Function

Suppose we have the data sets located in the excel sheet range 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

  • Arguments can either be numbers or names, arrays, or references that contain numbers.
  • If an array or reference argument contains text, logical values, or empty cells, the values are ignored by this function. However, any cells with the value zero are included.
  • Arguments that are error values or text that cannot be translated into numbers cause errors.
  • The MODE function through #NUM! Error if there are no duplicates/repetitive values in the supplied data sets; hence, there is no mode within the supplied values.

=MODE(A28:A37) as there are no duplicate values in the supplied data set MODE function through #N/A error.

Example -5

  • The MODE function through #VALUE! Error when a value that is supplied directly to the MODE is non-numeric and also is not part of an array. Non-numeric functions that are in the array of values are ignored by the MODE function.

=MODE(C28:C37,”hdd”)

Example -6

MODE Excel Function Video

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 along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • VBA Worksheet Function
  • Excel VBA “MsgBox”
  • Print in Excel
  • Remove Duplicates in Excel
  • Change Chart Style In Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Category iconExcel Tutorials,  Excel, VBA & Power BI

Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download MODE Function Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More