Use MODE.MULT Function in Excel GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office
Use MODE.MULT Function to Find Multiple Modes in Data in Excel
Learn how to use the MODE.MULT function in Excel
By Ted French Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. lifewire's editorial guidelines Updated on March 1, 2020 Tweet Share Email ROBERT BROOK/SCIENCE PHOTO LIBRARY/Getty Images Tweet Share Email MS Office Excel Word Powerpoint Outlook Mathematically, there are several ways to find the average for a given set of data — 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. The MODE function finds the single most frequently occurring value, or mode, in a list of numbers. MODE.MULT, on the other hand, tells you if there are multiple values, or multiple modes, that occur most frequently in a range of data. These instructions apply to Excel for Microsoft 365, Excel 2019, 2016, 2013, and 2010. MODE MULT Function Syntax and Arguments
The MULTI.MODE function only returns multiple modes if two or more numbers frequently occur within the selected data range. 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. Only Number1 is required; Number2 and on are optional. Entering the 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. There are only two values that occur with equal frequency, but the function is in three cells. Because more cells were selected than there are modes, the third cell D4 returns the #N/A error. Options for entering the function and its arguments include: Typing the complete function into a worksheet cellSelecting the function and arguments using the Function Dialog Box Select the MODE MULT Function and Arguments Using the Dialog Box
For the MODE.MULT function to return multiple results, you must enter it as an array formula -- that is into multiple cells at the same time, since regular Excel formulas can return only one result per cell. For both methods, the last step is to enter the function as an array function using the Ctrl, Alt, and Shift keys as detailed below. Highlight cells D2 to D4 in the worksheet to select them. The results of the function will display in these cells. Select the Formulas tab. Choose More Functions > Statistical from the ribbon to open the function drop-down menu. Select MODE.MULT in the list to bring up the Function Dialog Box. Select the Number1 field. Highlight cells A2 to C4 in the worksheet to enter the range into the dialog box. Press and hold down the Ctrl and Shift keys on the keyboard. Press the Enter key on the keyboard to create the array formula and close the dialog box. MODE MULT Results and Errors
As a result of entering the MODE.MULTI function, and creating an array, as notated above, the following results should be present: The number 2 in cell D2The number 3 in cell D3The error #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 sample. Even 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 one of the modes for the data sample. Other important things to know about MODE.MULT include: If there is no mode or 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 output. The 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, you can nest the MODE.MULT function inside the TRANSPOSE function. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Combine the ROUND and SUM Functions in Excel Use the Excel RIGHT Function to Extract Characters How to Round Numbers Down in Excel With the ROUNDDOWN Function Ignore Error Values When Finding the Average in Excel How to Use the Google Spreadsheets AVERAGE Function Use Excel's EOMONTH Function to Add or Subtract Months How to Use Excel's MROUND Function How to Count Data in Selected Cells With Excel's COUNTIF Function How to Use the Round Function in Excel How to Create an Excel Lookup Formula With Multiple Criteria Excel SUMIFS: Sum Only Values Meeting Multiple Criteria How to Create an Excel Left Lookup Formula Using VLOOKUP Round up Numbers in Excel With the ROUNDUP Function How to Use the STDEV Function in Excel How to Use the Excel TRUNC Function How to Nest Multiple IF Functions in Excel Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies