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.
1. Click to Highlight an Empty Cell
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’.
2. Enter the formula: =ARRAYFORMULA(MEDIAN(IF(Cell Ref1:Cell Ref2=Cell Ref3,Cell Ref4:Cell Ref5)))
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)))
=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:
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.
3. Press Enter to See the Results
With the formula ready, press the Enter key. The median value will now display for the first comparison cell in E4.
4. Click and Drag the Blue Square to Fill The Formula Down the Column
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.