If you want to look up the most frequently appearing number in a range, this article is for you.
Generic Formula
For a single value.
| =MODE.SNGL(range) |
For multiple frequently occurring number.
| =MODE.MULT(range) |
Range: This is the range from which you want to lookup a frequently appearing number. It can be an array, range or 2D array or range.
Let's see an example to see how we can retrieve frequently appearing numbers in Excel.
So, we roll two dices together 12 times and recorded the outcomes in an excel table. Now we need to get the first most frequently appearing number.
I write this formula
| =MODE.SNGL(B2:B13) |
This returns the number 4. The number 4 appears 3 times in the dataset.
You can use outdated function MODE too which is available for Excel 2007 compatibility only. Excel recommends to not use this function.
| =MODE(B2:B13) |
To get all the numbers that repeat by the same numbers as with the highest frequency, we use the MODE.MULT function.
In the above data set, the numbers 4 and 12 both occur 3 times in the dataset. To get both of them as most frequently appearing number in data, we use the formula:
| =MODE.MULT(B2:B13) |
When you use this function, select multiple cells and hit CTRL+SHIFT+ENTER to get all the frequently appearing numbers.
If you have Excel 2019 or 365 than you don't need to use CTRL+SHIFT+ENTER. The formula will spill the outcomes down the cells itself..
How does it work?
The MODE.SNGL and MODE.MULT both statistical functions are designed to return the most frequently appearing number.
The MODE.SNGL returns the first number that occurs most times.
The MODE.MULT function returns all the numbers that repeat most times.
So yeah guys, this is how you get the most frequently appearing number in a range. I hope it was explanatory and useful. If you have any doubts regarding this article or any other excel topic, ask me in the comments section below.
Related Articles:
How to Get the most frequently appearing text in Excel| Using INDEX MATCH and MODE function together, we can retrieve the text that occurs most frequently in an Excel range.
How to Lookup Frequently Appearing Text with Criteria in Excel | To get the most frequently appearing text in excel with criteria you just need to put an if condition before match function.
How to Retrieve the First Value in a List that is Greater / Smaller than a Specified Value | Learn how to lookup first value that is greater than a specified value. The generic formula to lookup is...
How to Retrieve Latest Price in Excel | To look up the latest price from a range you must have your data sorted. Once sorted use this formula...
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to Use The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to Use COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
