How To Software The Excel MODE.MULT Function Share Pin Email Print Software Spreadsheets Documents Presentations Desktop Publishing Graphic Design Databases Animation & Video by Ted French Updated January 09, 2018 Mathematically, there are a number of ways of measuring central tendency or, as it is more commonly called, the average for a set of values. The average being the center or middle of a group of numbers in a statistical distribution.In the case of the mode, middle refers to the most frequently occurring value in a list of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is the number 3.To make it easier to measure central tendency, Excel has a number of functions that will calculate the more commonly used average values. These include:The MODE.SNGL function - finds the mode or most frequently occurring value in a list of numbers.The MODE.MULT function - used to determine if the data contains multiple modes - more than one value occurring most often in a list of numbersThe MEDIAN function - finds the median or middle value in a list of numbersThe AVERAGE function - finds the arithmetic mean for a list of numbers 01 of 05 How the MODE.MULT Function Works Using the MODE.MULT Function to Find Multiple Modes. © Ted French In Excel 2010, the MODE.MULT function was introduced to expand upon the usefulness of the MODE function found in previous versions of Excel.In those previous versions, the MODE function was used to find the single most frequently occurring value - or mode - in a list of numbers.MODE.MULT, on the other hand, will tell you if there are multiple values - or multiple modes - that occur most frequently in a range of data.Note: the function only returns multiple modes if two or more numbers occur with equal frequency within the selected data range. The function does not rank the data. 02 of 05 Array or CSE Formulas In order to return multiple results, MODE.MULT must be entered as an array formula - that is into multiple cells at the same time, since regular Excel formulas can only return one result per cell.Array formulas are entered by pressing the Ctrl, Shift, and Enter keys on the keyboard at the same time once the formula has been created.Because of the keys pressed to enter the array formula, they are sometimes referred to as CSE formulas. 03 of 05 The MODE.MULT Function's Syntax and Arguments A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.The syntax for the MODE.MULT function is:= MODE.MULT ( Number1, Number2, ... Number255 )Number - (required) the values (to a maximum of 255) for which you want to calculate the modes. This argument can contain the actual numbers - separated by commas - or it can be a cell reference to the location of the data in the worksheet.Example Using Excel's MODE.MULT Function:The example shown in the image above has two modes - the numbers 2 and 3 - that occur most often in the selected data.Even though there are only two values that occur with equal frequency, the function has been entered into three cells.Because more cells were selected than there are modes, the third cell - D4 - returns the #N/A error. 04 of 05 Entering the MODE.MULT Function Options for entering the function and its arguments include:Typing the complete function: =MODE.MULT(A2:C4) into a worksheet cellSelecting the function and arguments using the function's dialog boxFor both methods, the last step is to enter the function as an array function using the Ctrl, Alt, and Shift keys as detailed below.The MODE.MULT Function Dialog BoxThe steps below detail how to select the MODE.MULT function and arguments using the dialog box.Highlight cells D2 to D4 in the worksheet to select them - these cells are the location where the results of the function will be displayedClick on the Formulas tabChoose More Functions > Statistical from the ribbon to open the function drop down listClick on MODE.MULT in the list to bring up the function's dialog boxHighlight cells A2 to C4 in the worksheet to enter the range into the dialog box 05 of 05 Creating the Array Formula Press and hold down the Ctrl and Shift keys on the keyboardPress the Enter key on the keyboard to create the array formula and close the dialog boxFormula ResultsThe following results should be present:the number 2 in cell D2;the number 3 in cell D3;and #N/A in cell D4.These results occur because only two numbers - 2 and 3 - appear most often and with equal frequency in the data sampleEven though the number 1 occurs more than once - in cells A2 and A3 - it does not equal the frequency of the numbers 2 and 3 so it is not included as one of the modes for the data sampleWhen you click on cell D2, D3, or D4 the complete array formula{ = MODE.MULT (A2:C4) }can be seen in the formula bar above the worksheetNotes:If there is no mode - or, in other words, the data range contains no duplicate data - the MODE.MULT function will return a #N/A error in each cell selected to display the function's outputThe range of cells selected to display the results of the MODE.MULT function must run vertically. The function will not output the results to a horizontal range of cells.If a horizontal output range is required, the MODE.MULT function can be nested inside the TRANSPOSE functionThe form of the equation would be: {=TRANSPOSE(MODE.MULT(A2:C4)} Was this page helpful? Thanks for letting us know! Share Pin Email Tell us why! Other Not enough details Hard to understand Submit Continue Reading