INTRODUCTION
GOOGLE SHEETS 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.mult is an extension to the standard and older MODE FUNCTION.
As we know
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.
but what if there are more than one modes in the given data.
In that case, we can use MODE.MULT which is an array function and will return the complete list of all the modes present in the given data.
Mode.mult 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.MULT FUNCTION IN GOOGLE SHEETS
MODE.MULT FUNCTION returns the most frequently occurring values [If more than one mode is present] in a given data set.
It simply means that mode.mult function will return the value which is repeated most of the time in a given data set. [Two values will be returned if both are repeated the same number of times in the given data set which is higher than the repetition of any of the other values ].
e.g. suppose, we have a data set like
1,2,3,4,2,2,1,1
The mode of this data set will be 1 and 2 as we can see that both of the numbers are repeated thrice in the data set given.
We’ll take a few examples later to find out more practical ways to use this function.
PREREQUISITES TO LEARN MODE.MULT 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 GOOGLE SHEETS.
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.MULT FUNCTION
The Syntax for the MODE.MULT FUNCTION is
MODE.MULT( VALUE 1, VALUE 2, VALUE 3 ,…..UPTO 255 VALUES)
VALUE 1, VALUE 2, VALUE 3… are the values which will be returned as per the index number given.
AT LEAST ONE VALUE IS REQUIRED OTHERWISE ERROR WILL EMERGE.
EXAMPLE 1: MODE.MULT FUNCTION IN GOOGLE SHEETS
Let us find out different examples to learn MODE.MULT FUNCTION in GOOGLE SHEETS.
FIND OUT THE MOST FREQUENT VALUE OR VALUES IN THE GIVEN DATA SET.
The data consist of 220 values.
Let us find out the values which are most frequent or is being repeated most of the time.
| 3 | 13 | 1 | 3 | 10 | 19 | 8 | 15 | 9 | 4 |
| 15 | 7 | 16 | 1 | 16 | 16 | 10 | 12 | 8 | 4 |
| 2 | 8 | 9 | 15 | 9 | 7 | 5 | 3 | 9 | 14 |
| 4 | 17 | 14 | 15 | 10 | 12 | 3 | 0 | 3 | 6 |
| 15 | 3 | 9 | 6 | 17 | 9 | 20 | 17 | 12 | 17 |
| 16 | 18 | 11 | 13 | 11 | 14 | 17 | 15 | 3 | 14 |
| 15 | 10 | 11 | 9 | 20 | 20 | 5 | 0 | 14 | 9 |
| 10 | 6 | 0 | 8 | 4 | 19 | 2 | 17 | 11 | 14 |
| 7 | 15 | 10 | 6 | 14 | 6 | 1 | 19 | 1 | 14 |
| 14 | 6 | 8 | 10 | 15 | 15 | 8 | 13 | 7 | 19 |
| 2 | 5 | 16 | 12 | 11 | 14 | 19 | 9 | 3 | 16 |
| 2 | 2 | 11 | 20 | 19 | 16 | 1 | 15 | 10 | 13 |
| 7 | 1 | 9 | 1 | 17 | 7 | 4 | 15 | 10 | 11 |
| 13 | 15 | 13 | 14 | 12 | 3 | 8 | 2 | 20 | 17 |
| 20 | 3 | 10 | 6 | 7 | 5 | 1 | 12 | 8 | 12 |
| 6 | 17 | 0 | 16 | 9 | 9 | 1 | 2 | 4 | 6 |
| 1 | 13 | 2 | 0 | 1 | 7 | 11 | 5 | 16 | 12 |
| 8 | 11 | 10 | 5 | 5 | 6 | 16 | 15 | 8 | 6 |
| 16 | 13 | 4 | 16 | 6 | 2 | 12 | 12 | 2 | 10 |
| 9 | 20 | 10 | 17 | 18 | 19 | 12 | 7 | 1 | 8 |
| 9 | 5 | 4 | 10 | 6 | 6 | 7 | 16 | 18 | 5 |
| 3 | 16 | 15 | 9 | 20 | 10 | 20 | 4 | 6 | 13 |
DATA TABLE
SOLUTION:
We can simply find out the numbers or values which is being repeated most of the time by using the MODE.MULT FUNCTION.
The steps are given below.
Follow the steps to find out the most repeated value:
- Select the cell where you want the result.
- Enter the formula as =MODE.MULT(COMPLETE RANGE).
- For our example, the formula will be =MODE.MULT(F8:O29)
- [ As OFFICE 365 HAVE BUILT IN SUPPORT FOR ARRAY FORMULAS, WE NEED NOT USE CSE STYLE OF ENTERING ARRAY FORMULAS ]
- The result will appear.
- The following picture shows the process.
The explanation follows the picture below.
EXPLANATION: FINDING OUT THE MOST FREQUENT VALUES IN THE GIVEN DATA SET IN OFFICE 365
The function used for the solution is =MODE.MULT(F8:O29)
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.
We don’t need to press CONTROL+SHIFT+ENTER in OFFICE 365.
DIFFERENCE BETWEEN MODE , MODE.MULT AND MODE.SNGL FUNCTIONS
The difference is very simple to understand.
MODE FUNCTION IS THE OLDER FUNCTION WHICH HAS BEEN KEPT FOR THE COMPATIBILITY WITH THE OLDER VERSIONS OF EXCEL
MODE.SNGL FUNCTION IS SAME AS MODE FUNCTION BUT HAVE PRESENCE IN ALL THE NEWER VERSIONS AND RETURNS THE SINGLE VALUE OF THE MODE. IT MEANS IF THERE ARE MULTIPLE MODES, IT WON’T RETURN ALL.
MODE.MULT WILL RETURN ALL THE MODE VALUES. [ IF WE ARE USING MODE.MULT IN THE PREVIOUS VERSIONS OF EXCEL THAN OFFICE 365, IT’LL ACT AS MODE AND MODE.SNGL IF WE DON’T USE IT AS AN ARRAY FUNCTION USING CSE STYLE. ]