Center With MEDIAN and MODE

When you summarize data, the average is not the only "middle." Two other measures of center matter: the median and the mode.

• MEDIAN is the middle value when numbers are sorted.
• MODE is the value that appears most often.

Each tells a different story about your dataset, and together they paint a clearer picture than the average alone.

The average is easily pulled around by extreme values. If most salaries are near 50,000 but one person earns 5,000,000, the average jumps far above what a typical person earns.

The median ignores this. It simply asks: what value sits exactly in the middle? That makes it a robust measure of "typical" for skewed data like income, house prices, or response times.

MEDIAN takes a range and returns the middle value after sorting. You do not need to sort the data yourself.

Here we find the median of ten values in column A. If there is an odd count, MEDIAN returns the exact middle item. If there is an even count, it averages the two middle items.

Suppose your sorted values are 2, 4, 6, 10. There is no single middle item, so MEDIAN averages the two central numbers: (4 + 6) / 2 = 5.

This is why a median can be a value that does not actually appear in your data. It still represents the center of the distribution.

A quick trick: compare the average and the median side by side.

• If they are close, your data is fairly symmetric.
• If the average is much higher than the median, you have high outliers pulling it up.
• If the average is lower, low outliers are dragging it down.

This single comparison is a fast health check for any dataset.

The mode is the most frequently occurring value. In modern Excel and Google Sheets you use MODE.SNGL (single mode); the older MODE still works for one result.

Here we find the single most common value in a range. This is handy for finding the most-ordered product size, the most common rating, or a typical quantity.

MODE only works if at least one value repeats. If every number in the range is unique, MODE.SNGL returns the #N/A error.

To avoid an ugly error in a report, wrap it with IFERROR and provide a friendly fallback message.

Sometimes two or more values tie for most frequent. MODE.MULT returns all of them as a spilled array.

In modern Excel and Google Sheets, typing this in one cell lets the results spill down into the cells below automatically.

Imagine star ratings from 1 to 5 in A2:A21. You want the typical rating.

• MEDIAN tells you the middle rating.
• MODE tells you the rating people chose most.

If MEDIAN is 4 but MODE is 5, most people gave 5 stars yet a chunk gave lower scores, pulling the middle down to 4. Two numbers, two insights.

Both MEDIAN and MODE.SNGL look only at numbers. Blank cells and text labels inside the range are simply ignored, not counted as zero.

That means you can point these functions at a whole column even if it has a header or stray notes. Just be careful: a number stored as text (left-aligned) is ignored too, which can quietly change your result.

There is no built-in MEDIANIF, but you can compute a median for one group using an array. In modern Excel and Sheets, MEDIAN combined with IF gives the median of only the East region.

This returns the middle order value for just the rows where the region equals East, ignoring everything else.

Test your understanding of center measures.

You learned the two non-average measures of center:

• MEDIAN(range) returns the middle value, ignoring outliers.
• MODE.SNGL(range) returns the most frequent value; MODE.MULT returns all ties.
• MODE returns #N/A with no repeats, so wrap it in IFERROR.
• Comparing average to median reveals skew in your data.

Use these together for a fuller picture than the average alone.