How to use the MODE function
The MODE function calculates the most frequent number in a cell range.
Number 4 exists 3 times on picture above and number 2 exists only 2 times. Number 4 is the most frequent number in B3:B10.
Excel Function Syntax
MODE(number1,[number2],...)
Arguments
number1 | Required. A cell range you want to calculate the most frequent number. |
[number2] | Optional. Up to 254 additional arguments. |
How to get the most common text value
Formula in cell D3:
Explaining formula in cell D3
Step 1 - Create a number representing the position of each value in a sorted array
The COUNTIF function calculates the number of cells equal to a condition. In this case, the less than character compares all values and assigns a number to each value based on their position in a sorted array.
COUNTIF(range, criteria)
COUNTIF(B3:B12, "<"&B3:B12)
becomes
COUNTIF({"Emphasis"; "Basil"; "Boolean"; "Emphasis"; "Gift"; "Historian"; "Boolean"; "Pension"; "Emphasis"; "Yogurt"},"<"&{"Emphasis"; "Basil"; "Boolean"; "Emphasis"; "Gift"; "Historian"; "Boolean"; "Pension"; "Emphasis"; "Yogurt"})
and returns
{3; 0; 1; 3; 6; 7; 1; 8; 3; 9}
Repeated values have identical numbers, we can use this to extract the most common number, see next step.
For example, "Boolean" is displayed twice in B3:B12, shown in the image above. The corresponding number in the array is 1, 1 is shown twice in the array.
Step 2 - Get the most repeated number
The MODE function returns the most common number in a cell range or array.
MODE(COUNTIF(B3:B12, "<"&B3:B12))
becomes
MODE({3; 0; 1; 3; 6; 7; 1; 8; 3; 9})
and returns 3. Number three is the most common number in the array.
Step 3 - Find the position of most repeated number
The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.
MATCH(lookup_value, lookup_array, [match_type])
MATCH(MODE(COUNTIF(B3:B12, "<"&B3:B12)), COUNTIF(B3:B12, "<"&B3:B12), 0)
becomes
MATCH(3, COUNTIF(B3:B12, "<"&B3:B12), 0)
becomes
MATCH(3, {3; 0; 1; 3; 6; 7; 1; 8; 3; 9}, 0)
and returns 1. Three is the first value in the array.
Step 4 - Get value based on position
The INDEX function returns a value from a cell range, you specify which value based on a row and column number.
INDEX(array, [row_num], [column_num], [area_num])
INDEX(B3:B12, MATCH(MODE(COUNTIF(B3:B12, "<"&B3:B12)), COUNTIF(B3:B12, "<"&B3:B12), 0))
becomes
INDEX(B3:B12, 1)
becomes
INDEX({"Emphasis"; "Basil"; "Boolean"; "Emphasis"; "Gift"; "Historian"; "Boolean"; "Pension"; "Emphasis"; "Yogurt"}, 1)
and returns "Emphasis".
How to get the most common text value - Excel 365
Formula in cell D3:
Explaining formula in cell D3
Step 1 - Count how many times each value is repeated in the array
The COUNTIF function calculates the number of cells equal to a condition.
COUNTIF(range, criteria)
COUNTIF(B3:B12,B3:B12)
becomes
COUNTIF({"Emphasis"; "Basil"; "Boolean"; "Emphasis"; "Gift"; "Historian"; "Boolean"; "Pension"; "Emphasis"; "Yogurt"}, {"Emphasis"; "Basil"; "Boolean"; "Emphasis"; "Gift"; "Historian"; "Boolean"; "Pension"; "Emphasis"; "Yogurt"})
and returns {3; 1; 2; 3; 1; 1; 2; 1; 3; 1}.
Step 2 - Find the largest value
The MAX function returns the largest number in a cell range or array.
MAX(COUNTIF(B3:B12,B3:B12))
becomes
MAX({3; 1; 2; 3; 1; 1; 2; 1; 3; 1})
and returns 3.
Step 3 - Calculate the position of the largest value in array
The equal sign lets you compare value to value, it returns boolean values TRUE or FALSE.
MAX(COUNTIF(B3:B12,B3:B12))=COUNTIF(B3:B12,B3:B12)
becomes
3=COUNTIF(B3:B12, B3:B12)
becomes
3={3; 1; 2; 3; 1; 1; 2; 1; 3; 1}
and returns {TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}.
Step 4 - Filter corresponding value
The FILTER function extract values/rows based on a condition or criteria.
FILTER(array, include, [if_empty])
FILTER(B3:B12, MAX(COUNTIF(B3:B12,B3:B12))=COUNTIF(B3:B12,B3:B12))
becomes
FILTER(B3:B12, {TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE})
becomes
FILTER({"Emphasis"; "Basil"; "Boolean"; "Emphasis"; "Gift"; "Historian"; "Boolean"; "Pension"; "Emphasis"; "Yogurt"}, {TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE})
and returns {"Emphasis"; "Emphasis"; "Emphasis"}.
Step 5 - Extract unique distinct values
The UNIQUE function extracts both unique and unique distinct values and also compares columns to columns or rows to rows.
UNIQUE(array, [by_col], [exactly_once])
UNIQUE(FILTER(B3:B12, MAX(COUNTIF(B3:B12,B3:B12))=COUNTIF(B3:B12,B3:B12)))
becomes
UNIQUE({"Emphasis"; "Emphasis"; "Emphasis"})
and returns "Emphasis".
Step 6 - Shorten formula
The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.
LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])
The following formula can be shortened using the LET function, cell range B3:B12 is repeated many times in the formula.
Function COUNTIF(B3:B12,B3:B12) is also repeated in the formula, I have assigned z to B3:B12 and y to COUNTIF(B3:B12, B3:B12).
UNIQUE(FILTER(B3:B12, MAX(COUNTIF(B3:B12,B3:B12))=COUNTIF(B3:B12,B3:B12)))
becomes
LET(z, B3:B12, y, COUNTIF(z, z), UNIQUE(FILTER(z, MAX(y)=y)))
Functions in 'Compatibility' category
The MODE function function is one of many functions in the 'Compatibility' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form