In this article we will show you how to use the IF and MEDIAN functions to create a MEDIAN IF formula. Example formulas and images included.
For the purpose of the below demonstration an example sheet was created, access this by clicking here and follow along at home.
In our example below, we will use the MEDIAN IF function with an array formula to determine the median performance score of workers within separate branches of a business.
Click to highlight an empty cell in your sheet. This cell is where the Median IF calculation will be made.
In our example we have selected cell F4, below the header of ‘Median Score’.
In an empty cell enter the MEDIAN IF formula in the following format:
=ARRAYFORMULA(MEDIAN(IF(Cell Ref1:Cell Ref2=Cell Ref3,Cell Ref4:Cell Ref5)))
Formula Breakdown:
=ARRAYFORMULA: This is a function that allows you to execute multiple calculations across a range of cells. In our example, it will calculate the unique median values for all cell references in column E
(MEDIAN: The Median function measures and calculates the median in a numerical set of values.
(IF: The IF function returns values based on a true or false condition
(Cell Ref1: This is the first cell parameter in the formula. It can be a single cell, entire column or row (example C1 or C)
Cell Ref2: This is the second cell parameter. This can also be a singular cell, entire column or row.
=Cell Ref3: This will be the cell that the previous selections will be compared to and if the value is equal to. In our example this will be cell E4 which contains the Unique formula.
,Cell Ref4: The first of the second set of parameter cells to be compared by the formula. This can be a singular cell, entire column or row.
:Cell Ref5)): The final cell parameter is for comparison by the Median formula.
In the example below, we have used the formula to return a median if value result by entering:
=ARRAYFORMULA(MEDIAN(IF(B4:B13=E4,C4:C13)))
This IF formula states that IF any values in B4:B13 = the value in E4 (Brown), get the corresponding values in C4:C13.
The MEDIAN function then gets the median of these values and returns a general answer based on the entire selected data range.
The array formula returns a value specific to the unique data in our example table.
With the formula ready, press the Enter key. The median value will now display for the first comparison cell in E4.
Click and hold on the small blue square in the corner of the highlighted cell (like the one displayed in the example below in F4) and drag down to copy the formula down the other cells. The cell references in the formula will automatically be updated to reflect the change in cell.
The median results for all of your unique values will now be automatically generated like in our example below.
If you enjoyed this article, you might also like our article on how to use the UNIQUE IF in Google Sheets or our article on how to use the count function in Google Sheets.
If you want to learn how to mail merge in Google Sheets, we also suggest checking out our detailed guide.
