INTRODUCTION
EXCEL is the spreadsheet application offered by MICROSOFT which is one of the best spreadsheet application.
One of the functionalities is the presence of FUNCTIONS which return the result after calculation.
Whenever we try to create smart sheets, we always try to automate it up to the maximum extent possible.
To automate anything, we need to cover as many as possibilities to make the program error free.
For the different requirements we have different available functions.
Mode is a function which comes under the STATISTICS CATEGORY and help us to find out the most frequently occurring value in a data set.
Mode is a statistical measure and is used a lot when analyzing a data.
In this article, we’ll learn about the MODE FUNCTION and its syntax formula, usage and learn its usage with the help of different examples.
PURPOSE OF MODE FUNCTION IN EXCEL
MODE FUNCTION returns the most frequently occurring value in a given data set.
It simply means that mode function will return the value which is repeated most of the time in a given data set.
e.g. suppose, we have a data set like
2,3,4,5,6,7,6,5,4,3,5,6,1,2,43,2,3,4,2,3,2,2,2,2,2,2,2
The mode of this data set will be 2.
We’ll take a few examples later to find out more practical ways to use this function.
PREREQUISITES TO LEARN MODE FUNCTION
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- If you have knowledge about MODE in STATISTICS, it’ll be easier to get this function.
- Of course, access to EXCEL.
Helpful links for the prerequisites mentioned above
What Excel does? How to use formula in Excel? [ Although this article is for EXCEL but it’ll help you understand the basic working of any spreadsheet application including EXCEL]
SYNTAX: MODE FUNCTION
The Syntax for the MODE FUNCTION is
MODE( VALUE 1, VALUE 2, VALUE 3 ,…..)
VALUE 1, VALUE 2, VALUE 3… are the values which will be returned as per the index number given.
EXAMPLE 1: MODE FUNCTION IN EXCEL
Let us find out different examples to learn MODE FUNCTION in EXCEL.
FIND OUT THE MOST FREQUENT VALUE IN THE GIVEN DATA SET.
The data consist of 112 values.
Let us find out the value which is most frequent or is being repeated most of the time.
18 | 15 | 4 | 3 | 1 | 14 | 18 |
2 | 14 | 3 | 18 | 11 | 8 | 12 |
11 | 5 | 2 | 5 | 3 | 17 | 16 |
10 | 6 | 14 | 11 | 19 | 18 | 19 |
1 | 17 | 6 | 2 | 9 | 18 | 10 |
13 | 12 | 10 | 11 | 8 | 5 | 18 |
14 | 10 | 12 | 18 | 8 | 15 | 13 |
1 | 10 | 17 | 20 | 10 | 17 | 13 |
11 | 1 | 18 | 14 | 8 | 13 | 18 |
14 | 8 | 15 | 17 | 6 | 15 | 2 |
18 | 18 | 16 | 15 | 3 | 14 | 13 |
7 | 10 | 19 | 6 | 17 | 18 | 9 |
13 | 7 | 18 | 12 | 2 | 1 | 4 |
9 | 12 | 1 | 4 | 11 | 16 | 16 |
5 | 3 | 6 | 5 | 11 | 4 | 15 |
2 | 11 | 16 | 9 | 5 | 10 | 8 |
DATA TABLE
SOLUTION:
We can simply find out the number or value which is being repeated most of the time by using the MODE FUNCTION.
The steps are given below.
Follow the steps to find out the most repeated value:
- Select the cell where you want the result by single clicking it.
- Enter the formula as =MODE(COMPLETE RANGE)
- For our example, the formula will be
- =MODE(C3:I18)
The explanation follows the picture below.
EXPLANATION: FINDING OUT THE MOST FREQUENT VALUE IN THE GIVEN DATA SET
The function used for the solution is =MODE(C3:I18)
Let us analyze the formula used.
The formula simply contains the complete range of the data i.e. THE FIRST CELL TO THE TOP-LEFT OF THE SELECTION TO THE LAST CELL WHICH IS THE BOTTOM-RIGHT AREA OF THE SELECTION.
After putting the formula, press Enter and the result will appear.