EXCEL FUNCTIONS- MODE.MULT

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.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 EXCEL

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 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.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 EXCEL 2007,2010,2013,2016,2019

Let us find out different examples to learn MODE.MULT FUNCTION in EXCEL.

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.

31313101981594
1571611616101284
289159753914
417141510123036
1539617920171217
1618111311141715314
1510119202050149
106084192171114
715106146119114
1468101515813719
2516121114199316
22112019161151013
71911774151011
13151314123822017
20310675112812
617016991246
11320171151612
81110556161586
1613416621212210
9201017181912718
9541066716185
3161592010204613

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 an array of the cells in a column which contains the number of the cells which is equal to the number of the MODES.
  • In a big data, we can’t find out the number of modes , so we need to take a guess here and select a good number so that our procedure doesn’t fail.
  • There is a method by which we can find out if the procedure is completed or not.
SELECT THE ARRAY FOR THE RESULT

  • After the array has been selected, enter the formula as =MODE.MULT(COMPLETE RANGE) BUT DON’T PRESS ENTER.
  • For our example, the formula will be =MODE.MULT(F8:O29)
  • As it is an array formula press CSE i.e. CONTROL+SHIFT+ENTER. [ PRESS CONTROL, KEEP IT PRESSED, PRESS SHIFT KEEP IT PRESSED AND PRESS ENTER ]
  • The result will appear.
  • The following picture shows the process.

The explanation follows the picture below. 

STEPS TO USE MODE.MULT FUNCTION

EXPLANATION: FINDING OUT THE MOST FREQUENT VALUES IN THE GIVEN DATA SET IN EXCEL 2007,2010,2013,2016,2019

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.

After putting the formula, we press CONTROL+SHIFT+ENTER as it is an array formula.

FINDING OUT IF ALL THE MODES HAS BEEN EVALUATED OR NOT USING MODE.MULT FUNCTION

Now, the tough question is the number of the cells to be selected before we apply the array formula.

It is obvious that we don’t have the time for searching the modes for such a large data.

For the sure solution, just look at our solution.

We can see #N/A error at the bottom which means that the formula could only find FOUR MODES and after that NA error appeared.

The presence of this error at the bottom provides us the assurance that there are no more modes available.

EXAMPLE 2: MODE.MULT FUNCTION IN OFFICE 365

Let us find out different examples to learn MODE.MULT FUNCTION in EXCEL.

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.

31313101981594
1571611616101284
289159753914
417141510123036
1539617920171217
1618111311141715314
1510119202050149
106084192171114
715106146119114
1468101515813719
2516121114199316
22112019161151013
71911774151011
13151314123822017
20310675112812
617016991246
11320171151612
81110556161586
1613416621212210
9201017181912718
9541066716185
3161592010204613

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. 

STEPS TO USE MODE.MULT FUNCTION

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. ]