Statistical Analysis
List Most Frequently Occurring Numbers & Bimodal Ties (UNIQUE, COUNTIF & SORTBY)
Extract every tied numeric mode, rank distinct numbers by occurrence count, and compute modal frequency share without MODE.SNGL hiding bimodal clusters.
Why MODE.SNGL misleads on bimodal defect sizes and order quantities
When an order-quantity log contains three 12-packs and three 15-packs, MODE.SNGL returns only whichever number appears higher in the sheet, concealing the second peak entirely.
Computing per-row frequency COUNTIF($A$2:$A$10, A2) lets MAX(B2:B10) identify the modal peak count while SORT(UNIQUE(FILTER(A2:A10, B2:B10 = MAX(B2:B10)))) extracts every tied numeric mode in ascending order.
| Pack Size | Occurrences in 9 Orders | Frequency Share | Modal Status |
|---|---|---|---|
| 12 | 3 | 33.33% | Tied Top Mode #1 |
| 15 | 3 | 33.33% | Tied Top Mode #2 |
| 18 | 1 | 11.11% | Singleton |
| 20 / 25 | 1 each | 11.11% each | Singleton |
Count & Extract All Tied Numeric Modes
Filter the numeric column where row frequency equals MAX(B2:B10), deduplicate with UNIQUE, and sort ascending.
=ROWS(UNIQUE(FILTER(A2:A10, B2:B10 = MAX(B2:B10))))Retrieve Nth Tied Numeric Mode & Modal Share %
Index into the sorted modal array to display Mode #1 and Mode #2 side by side.
=INDEX(SORT(UNIQUE(FILTER(A2:A10, B2:B10 = D3))), 2)Try the example
Use the original values or change them to test your understanding.