Find The MEDIAN IF Your Formula Meets Criteria in Excel

Combine the MEDIAN and IF Functions in an Array Formula

Focused young woman working at laptop in office
Hero Images / Getty Images

This tutorial example uses a MEDIAN IF array formula to find the middle tender for two different projects. The nature of the formula permits us to search for multiple results simply by changing the search criterion - in this case, the project name.

The job of each part of the formula is:

  • The MEDIAN function finds the middle value for a project.
  • The IF function allows us to choose which project we want a tender for by setting a condition using the project names
  • The array formula lets the IF function test for multiple conditions in a single cell, and, when the condition is met, the array formula determines what data (project tenders) the MEDIAN function will examine to find the middle tender

Excel CSE Formulas

Array formulas are created by pressing the Ctrl, Shift, and Enter keys on the keyboard at the same time once the formula has been typed in. Because of the keys pressed to create the array formula, they are sometimes referred to as CSE formulas.

MEDIAN IF Nested Formula Syntax and Arguments

The syntax and arguments for the MEDIAN IF formula are as follows:

=MEDIAN ( IF ( logical_test, value_if_true, value_if_false ) )
Screenshot of Excel showing the MEDIAN IF function

Since the IF function is nested inside the MEDIAN function, the entire IF function becomes the sole argument for the MEDIAN function.

The arguments for the IF function are:

  • logical_test (required): A value or expression that is tested for a boolean value of TRUE or FALSE.
  • value_if_true (required): The value that is displayed if logical_test is true.
  • value_if_false (optional): The value that is displayed if logical_test is false.

Excel's MEDIAN IF Array Formula Example

As mentioned, the following example searches tenders for two different projects to find the middle or median tender. The arguments for the IF function accomplish this by setting the following conditions and results:

  • The logical test tries to find a match for the project name typed into cell D10 of the worksheet.
  • The value_if_true argument will be, with the help of the MEDIAN function, the middle tender for the chosen project.
  • The value_if_false argument will be omitted since it is not needed and its absence will shorten the formula. If a project name that is not in the data table — such as Project C — is typed into cell D10 the formula will return a zero value.

Entering the Tutorial Data in Excel

Screenshot of Excel showing the project data
  1. Enter the example data, as shown above, into your spreadsheet.

  2. In cell D10 type Project A — the formula will look in this cell to find which project to match.

Entering the MEDIAN IF Nested Formula

Since we are creating both a nested formula and an array formula, we will need to type the entire formula into a single worksheet cell. Once you have entered the formula DO NOT press the Enter key on the keyboard or click on a different cell with the mouse as we need to turn the formula into an array formula.

If you recieve a #VALUE! error, it might be because the formula was not entered correctly as an array in the given cell.

  1. Click on cell E10 — the location where the formula results will be displayed.

  2. Type the following formula in the cell:

    = MEDIAN ( IF( D3:D8=D10, E3:E8 ) )
  3. Press and hold down the Ctrl and Shift keys on the keyboard.

  4. Press the Enter key on the keyboard to create the array formula.

  5. The answer 15875 ( $15,875 with formatting ) should appear in cell E10 since this is the middle tender for Project A.

Test the Formula

Test the formula by finding the middle tender for Project B. Type Project B into cell D10 and press the Enter key on the keyboard.

Screenshot of Excel showing Project B data

The formula should return the value of 24365 ( $24,365 ) in cell E10.