MODE when nothing repeats
No repeated value means no mode — and an #N/A to handle.
Task:
Each week's top-selling pack size goes on the dashboard. Some weeks no size sells more often than another. In B2:B4 put the mode of each week's sales from C to G, or No clear winner when there is none.
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 | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Week | Mode | Sale 1 | Sale 2 | Sale 3 | Sale 4 | Sale 5 |
| 2 | Week 1 | 6 | 12 | 6 | 24 | 6 | |
| 3 | Week 2 | 6 | 12 | 24 | 36 | 48 | |
| 4 | Week 3 | 12 | 24 | 12 | 24 | 36 |
What this exercise teachesMay contain the answer
Week 2 has five different sizes, so there is no mode and MODE says so with #N/A. Week 3 is a tie between 12 and 24; MODE returns the one that appears first, which is worth knowing before a dashboard calls it a winner.