Troubleshoot a blocked array result
When a dynamic-array formula cannot place its results in neighboring cells, clear the intended output area after checking that those cells contain no needed data. Do not replace MODE.MULT with a single-mode function merely to remove a display problem; doing so changes the requested result. Keep the source observations separate from the spill area. A result with two values needs room for both even when a previous version of the data had only one mode.
Repeat the numeric-count check after pasting new observations. An apparently empty cell can contain a formula returning text, and an imported separator can turn a numeric-looking entry into text. Inspect questionable cells instead of assuming visual alignment proves their type. Preserve the measured precision too: rounding observations before counting frequencies can create new ties. A mode calculated after deliberate rounding should be labeled with that rounding rule, not presented as the mode of the original higher-precision measurements.
Choose the data range before choosing a function
Place one raw numeric observation per cell. Suppose A2:A9 contains 2, 2, 4, 4, 7, 9, 10, 12. The median is the middle of the ordered observations, while modes are the values sharing the highest frequency. These are different summaries, so give them separate output labels. A frequency table with counts in a second column is not the same as a raw-value column; MEDIAN does not automatically apply those counts.
Calculate and verify the median
Enter =MEDIAN(A2:A9). The eight observations are already ordered here, so the central values are the fourth and fifth, 4 and 7. Their arithmetic mean is 5.5. Sorting the worksheet is unnecessary for MEDIAN itself, but a sorted copy helps verify the ranks. Do not sort just one column of a multi-column record table if it would disconnect measurements from their record identifiers.
Return every repeated peak with MODE.MULT
Use =MODE.MULT(A2:A9) to return the repeated frequency leaders 2 and 4. Each appears twice; the remaining values appear once. MODE.SNGL returns one mode and cannot by itself display the complete tie. In Excel versions with dynamic arrays, leave enough empty cells below the formula for the result to spill. Older array-formula workflows can require selecting an output range and confirming it as an array formula; check the version's instructions.
Distinguish no repetitions from an error in the sheet
For a list such as 1, 3, 5, 7, every value appears once. Excel's mode functions report no repeated mode with #N/A. That does not mean the median is unavailable: it remains 4. A blank return from IFERROR can conceal both expected no-mode cases and unrelated formula errors. Prefer an explicit note and inspect the input range before suppressing an error whose cause has not been established.
| List | Median | Repeated modes |
|---|---|---|
| 2,2,4,4,7,9,10,12 | 5.5 | 2 and 4 |
| 1,3,5,7 | 4 | None; Excel #N/A |
| 6,6,6 | 6 | 6 |
The median and mode use different definitions; absence of a repeated mode does not invalidate the median.
Count numeric observations and retain zeros
Compare =COUNT(A2:A9) with the expected eight records. MEDIAN on a referenced range ignores blank and text cells, whereas a zero is a numerical observation and participates. A value imported as text can therefore disappear from the calculation without looking missing on screen. Convert or flag it deliberately. Do not replace every blank with zero to increase the count: that invents observations and can shift the central ranks.
Check frequencies independently
For this small example, =COUNTIF($A$2:$A$9,2) and the equivalent formula for 4 both return 2. A frequency table is useful when checking larger tied outputs. The order of the returned modes is less important than the set and each mode's count. A constant list such as 6,6,6 has one repeated numerical value; it is different from a list of several distinct values tied once each.
Compare Excel with the site's stated convention
Paste the same eight raw observations into the Median and Mode Calculator and confirm median 5.5 with modes 2 and 4. The website makes its mode convention explicit, including all-tied cases, so inspect that wording before equating every no-mode label across products. The tool does not evaluate an Excel formula or expand a frequency table pasted as two columns. Transfer observations themselves and preserve their repetitions.
Keep the worksheet reproducible
Record the source range, expected count, missing-value policy and selected mode function. Add a test with an odd count, a central pair, a repeated tie and a no-repetition list. When new records are appended, verify that formulas include them rather than relying on a fixed old range. Formula separators and localized function names can differ between installations. A transparent sheet makes the selected observations and conventions visible alongside the numerical result.