Excel track · Exercise 13 of 86 Paid
MODE: the most common value
Find the value that repeats most often, which is neither the biggest nor the middle.
Intermediate
Support ticket sizes and box counts per store
Read-only data · screen 1 of 2 · answer in C2
Put the ticket size that occurs most often into C2.
| A | B | C | |
|---|---|---|---|
| 1 | Ticket | Size | Most common |
| 2 | T-1 | 3 | |
| 3 | T-2 | 5 | |
| 4 | T-3 | 3 | |
| 5 | T-4 | 7 | |
| 6 | T-5 | 3 | |
| 7 | T-6 | 5 |
Put the most common box count into C2, and notice it is not the middle of the list.
| A | B | C | |
|---|---|---|---|
| 1 | Store | Boxes | Most common |
| 2 | S-1 | 2 | |
| 3 | S-2 | 9 | |
| 4 | S-3 | 2 | |
| 5 | S-4 | 4 | |
| 6 | S-5 | 7 | |
| 7 | S-6 | 2 | |
| 8 | S-7 | 9 |
Reference answer
What it does
MODE looks for the value that occurs most often and gives back one of the values on the sheet, not a figure built from them. That makes it the summary for numbers that are really categories, such as shoe sizes and pack counts. Blank cells and text are skipped.
In the first grid, 3 appears three times and 5 twice, so the answer is 3. The second grid has 2 three times and 9 twice, so the answer is 2, while the median of that same column is 4.
Two values with the same count leave a choice. Classic MODE returns whichever appears first in the data, and MODE.MULT returns both, so decide which one the report means. COUNTIF supplies the count itself when the frequency belongs on the page.
Syntax
=MODE(range)Worked example
On Support ticket sizes and box counts per store, =MODE(B2:B7) returns 3.
=MODE(B2:B7)Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | No value repeats, which leaves MODE nothing to return. A list of unique ticket sizes always lands here. |
#VALUE! | A word was passed as an argument in its own right, as in =MODE("S"), where a range would have ignored it. |
Tie at the top | Two sizes share the highest count, and MODE reports whichever it met first, so the number moves when the rows are reordered. |
At work
A support desk sizing its ticket categories, and a warehouse stocking the box count each shop orders most often.