MODE Function
The value that actually comes up most, which an average will never give you.
Task:
You are deciding which pack size to keep on the shelf. Put the pack size customers order most often in B8.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of MODE
0/3The same formula in the other shapes it takes at work.
- MODE Function
The value that actually comes up most, which an average will never give you.
CoreIntermediate - The most common value, and how often it appears
MODE gives the value; COUNTIF with MODE inside gives its frequency.
Intermediate - MODE when nothing repeats
No repeated value means no mode — and an #N/A to handle.
Intermediate
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | |
|---|---|---|
| 1 | Order | Pack size |
| 2 | O-1 | 12 |
| 3 | O-2 | 24 |
| 4 | O-3 | 12 |
| 5 | O-4 | 48 |
| 6 | O-5 | 12 |
| 7 | O-6 | 24 |
| 8 | Most common |
Functions used here
What this exercise teachesMay contain the answer
The mean order is 22, and 22 is not a pack size anybody can order. That is what MODE is for: when the numbers are really categories, the average lands in the gap between them and describes nothing that exists. Stocking the 12 is a decision you can act on; stocking 22 is not.