Median in Google Sheets: Formula and Median If

In Google Sheets, calculate a median with =MEDIAN(B2:B7). For a “median if” calculation—finding the median only for rows that meet a condition—combine MEDIAN with FILTER, because Google Sheets does not have a built-in MEDIANIF function.

What the MEDIAN function does

The median is the middle value in a set of numbers after they are arranged from smallest to largest. It is a measure of the center of a data set, like the average, but it is less affected by unusually high or low values. In Google Sheets, MEDIAN returns that middle value from the numeric values you provide.

For example, imagine cells B2:B7 contain 12, 15, 18, 21, 24, and 30. With those values in B2:B7, enter =MEDIAN(B2:B7) in an empty cell. There are six values, so the result is the average of the two middle numbers: (18 + 21) ÷ 2 = 19.5.

The function works with a range, individual numeric arguments, or a combination of the two. For a range, the general form is =MEDIAN(value1, [value2, ...]); the square brackets indicate optional arguments, not characters to type. A practical use is to summarize response times, prices, test scores, or other measurements without letting an extreme value dominate the summary.

How the median is determined

To understand a result, first sort the eligible numbers mentally or in a helper column. If there is an odd number of values, the median is the one in the exact middle. With 4, 7, and 15, the median is 7. If there is an even number, the median is the mean of the two middle values. With 4, 7, 15, and 20, the median is (7 + 15) ÷ 2, or 11.

You do not need to sort the sheet before using MEDIAN. The function determines the middle position from the supplied numeric values, regardless of their order on the worksheet. Sorting can still help you inspect a data set, but it is not a requirement for the formula.

In a referenced range, empty cells and text entries are not treated as numeric observations by MEDIAN; numeric zeroes are included. This distinction matters: a blank cell is not the same as a cell containing 0. If zero means “no measurement” in your particular data, decide whether to exclude it rather than assuming the function will ignore it.

Calculate a median for rows that meet one condition

To find a median for a category or other condition, use FILTER to return only the matching values and pass those values to MEDIAN. Suppose column A contains region names and column B contains measurements. In rows 2 through 7, let the North measurements be 12, 18, 24, and 30, while the other two rows belong to South. To find the North median, enter:

=MEDIAN(FILTER(B2:B7,A2:A7="North"))

The result is 21: the matching North values in order are 12, 18, 24, and 30, and the two middle values, 18 and 24, average to 21. The values and condition ranges must cover the same rows. Here, B2:B7 supplies the numbers and A2:A7 identifies which rows qualify.

This is the usual Google Sheets approach to a “median if” question. There is no standard function named MEDIANIF in Google Sheets, so typing that name will produce a function error. FILTER is useful because it makes the qualifying subset explicit before the median is calculated.

Use two or more conditions

FILTER can require more than one condition. For instance, suppose column C records a status and you want the median for North rows marked Complete. With region in A2:A7, measurements in B2:B7, and status in C2:C7, use:

=MEDIAN(FILTER(B2:B7,A2:A7="North",C2:C7="Complete"))

Each condition in this formula must be true for a row to be included: the region must be North and the status must be Complete. Keep every condition range aligned with the measurements range. If one range starts on row 2 and another starts on row 3, the formula may compare the wrong records or return an error.

You can also exclude zeroes when they represent missing measurements. With the same layout, the following formula includes only North rows whose measurement is not zero:

=MEDIAN(FILTER(B2:B7,A2:A7="North",B2:B7<>0))

Use that condition only when zero is genuinely not a valid observation. If zero is a meaningful measurement, excluding it would change the answer incorrectly.

Make the criterion changeable

Instead of writing a category directly into the formula, put the desired category in a cell such as E2. With region names in A2:A7 and measurements in B2:B7, use:

=MEDIAN(FILTER(B2:B7,A2:A7=E2))

Now changing E2 from North to South recalculates the median for the selected region. This is convenient for a small report or dashboard: a reader can choose a category in a cell, and the formula can remain unchanged. You can use a dropdown in E2 if you want to restrict choices to known category names.

For a growing data set, you may choose larger ranges, such as rows 2 through 1000, rather than changing the formula each time a record is added. Avoid using unnecessarily huge ranges in many formulas if the sheet becomes slow; a defined, realistic data area is easier to maintain. Also check that new records are inside the ranges referenced by both FILTER and MEDIAN.

Common errors and data checks

A conditional median can fail or mislead for a few predictable reasons. Check these points before changing the formula:

No matching rows: FILTER returns a #N/A error when no rows meet the condition. Check spelling, extra spaces, and whether the criterion cell contains the expected value. If a no-match result is normal, you can show a clearer message with =IFERROR(MEDIAN(FILTER(B2:B7,A2:A7=E2)),"No matching values").
Numbers stored as text: Values that look like numbers may have been imported as text. A median can then omit them from the numeric calculation. Check the source cells and convert the data to actual numbers where appropriate.
Misaligned ranges: The values and every condition range should cover corresponding rows and have compatible dimensions. A one-row offset can quietly select the wrong records.
Unexpected zeroes or blanks: A zero is a real number and affects the median. A blank is not a numeric observation in a referenced range. Decide what each means in the context of the data.
Category inconsistencies: North, north, and North followed by an extra space may not behave as you expect in a comparison. Standardize labels or inspect the source cells if some rows seem to be missing.

The IFERROR version is helpful for presentation, but it can also hide an error other than “no matches.” While building a sheet, consider leaving the underlying error visible so you can find a typo or broken range. Add error handling after confirming the formula works and deciding what message is appropriate for a genuinely empty result.

Median versus average

The median and average answer different questions. The arithmetic average adds the values and divides by their count; the median identifies the center after ordering them. For values 10, 12, 13, 15, and 100, the median is 13, while the average is 30. The unusually large 100 pulls the average upward, but it does not change which value sits in the middle.

Neither measure is always better. The average is useful when the total of all values matters, while the median can give a more representative center when a distribution is skewed or contains outliers. For example, a median may describe a typical home price more clearly than an average when a few exceptionally expensive homes are included. In a report, showing both can reveal that the data are uneven rather than forcing one summary to do all the work.

Google Sheets and Excel differences

Google Sheets and current Excel versions both provide a MEDIAN function. Excel also does not provide a standard MEDIANIF function. In Microsoft 365 Excel, a similar conditional formula can combine MEDIAN and FILTER, but availability and formula behavior can differ in older Excel editions. This article’s examples use Google Sheets in a web browser; in Sheets, type the formula into a cell and press Enter.

For a straightforward Google Sheets workflow, there is no special menu command required to calculate the median. The formula updates when referenced values or criteria change. If a spreadsheet is shared with people using other software, keep the formula simple and verify that the recipient’s application supports the functions used—especially when conditional filtering is involved.

Frequently Asked Questions

What is the formula for median in Google Sheets?
Use =MEDIAN(range), replacing range with the cells you want to summarize. For example, =MEDIAN(B2:B7) returns the median of the numeric values in B2:B7.

Does Google Sheets have a MEDIANIF function?
No. Google Sheets does not have a built-in MEDIANIF function. For a median based on a condition, use MEDIAN with FILTER, such as =MEDIAN(FILTER(B2:B7,A2:A7="North")).

How do I find the median if a cell matches a value?
Filter the measurement range using the matching range, then take the median of the filtered values. For example, if A2:A7 contains categories and B2:B7 contains measurements, =MEDIAN(FILTER(B2:B7,A2:A7=E2)) uses the category entered in E2.

Why does my conditional median return #N/A?
The most common reason is that no rows match the condition; Google Sheets’ FILTER returns #N/A when it has no results. Check the criterion and source data. If no matches are an expected outcome, wrap the formula in IFERROR to display a message.

Does MEDIAN count zero and blank cells?
A numeric zero is included and can affect the result. Empty cells in a referenced range are not numeric observations. If zero means missing data in your sheet, exclude it with an additional FILTER condition—but keep zero if it is a legitimate measurement.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.