Statistical
MODE.SNGL and MODE: find the most frequent number in a dataset
Identify the most common pack size or order quantity with MODE.SNGL, and handle all-unique ranges where no value repeats (#N/A).
When the most common value matters more than the average
If customers order carton quantities of 12, 24, and 36, an average of 20 cartons does not match any physical carton configuration. MODE.SNGL(number1, [number2], ...)—and its legacy alias MODE—returns the numeric value that appears most often.
Text cells and blank cells inside the range are ignored, while numeric zeros participate as valid observations.
| Order Rows (A2:A7) | Values | MODE.SNGL(A2:A7) |
|---|---|---|
| Initial batch | 12, 24, 12, 36, 12, 24 | 12 (appears 3 times) |
| Updated batch | 12, 24, 24, 36, 24, 24 | 24 (appears 4 times) |
| All unique batch | 10, 20, 30, 40, 50, 60 | #N/A (no duplicate value) |
Find the modal order quantity
When two different numbers tie for the highest frequency, MODE.SNGL returns whichever tied number appears earliest in the range.
=MODE.SNGL(A2:A7)Wrap all-unique ranges in IFNA
If every number in A2:A7 occurs only once, no mode exists and MODE.SNGL returns #N/A. Wrapping the call in IFNA displays a friendly label instead.
=IFNA(MODE.SNGL(A2:A7), "No repeating value")Try the example
Use the original values or change them to test your understanding.