When working in Google Sheets there may be times when you want to find the median value of cells that meet certain criteria.
This can be done by combining the MEDIAN function with the IF function to create a Median IF Formula.
There is not a built-in Median IF function in Google Sheets, but by knowing how to use both these functions, we can create one ourselves.
In this tutorial, I will show you how to use a MEDIAN IF formula in Google Sheets.
Table of Contents
Median IF Syntax
To combine the MEDIAN and IF functions in Google Sheets to create a MEDIAN IF formula, this is the syntax:
=ArrayFormula(MEDIAN(IF(criteria_range=criteria,median_range)))
- criteria_range – this is the range that contains the criteria that you want to check for
- criteria – this is the criteria that you want to use to run the MEDIAN on if the values in your criteria_range are matching this
- median_range – this is the range that contains the numbers that you will use to find the median
Using the Median IF Formula
Now that we know the syntax, I will show you how to use the formula with this example data:
In this example data, I have the sales for various products belonging to different departments. I will use the MEDIAN IF formula to get the median sales values of each department.
Here are the steps:
1. First create 2 new columns where you will create a unique list of the criteria you will use for calculating the median and a column for calculating the median. In this example, my criteria is the department and I am calculating a median for the sales.
2. You can either manually type each unique value, for your criteria or you can use the UNIQUE function on the cell range to return a list of unique values. In this example, my formula is =UNIQUE(B2:B13)
3. Now that we have our file set up, we can begin using our MEDIAN IF formula. In this example my formula is =ArrayFormula(MEDIAN(IF($B$2:$B$13=E2,$C$2:$C$13))). The first cell range in the formula is the criteria_range ($B$2:$B$13) and I want to find the median if the value in this range equals my criteria (E2). This will then run the MEDIAN function on cell range $C$2:$C$13 for lines that meet my criteria
4. After you have your formula entered into your first cell, you can copy the formula and paste it down your column if you have additional values for which you want to return the median
Closing Thoughts
Learning to create a MEDIAN IF function can seem challenging at first, but once you have tried it a few times, you will find it is actually quite simple.
It helps if you already have an understanding of MEDIAN and IF, so if you are struggling, learn to use those functions first, and make sure to watch the video!
More Google Sheets Tutorials:
How to Find Quartiles
How to Do Multiple IF Statements
How to Use ARRAYFORMULA