Find the MEDIAN IF Your Formula Meets Criteria in Excel

Combine the MEDIAN and IF functions in an array formula

By
Ted French
Ted French
Writer
Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs.
lifewire's editorial guidelines
Updated on June 7, 2020
Reviewed by
Ryan Perian
Ryan Perian headshot
Reviewed by Ryan Perian
  • Western Governors University
Ryan Perian is a certified IT specialist who holds numerous IT certifications and has 12+ years' experience working in the IT industry support and management positions.
lifewire's editorial guidelines

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 tutorial example, the project name).

The information in this article applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, and Excel for Mac.

About the MEDIAN and IF Functions

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. 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+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 displays if logical_test is true.
  • value_if_false (optional): The value that displays if logical_test is false.

Excel's MEDIAN IF Array Formula Example

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 finds a match for the project name typed into cell D10 of the worksheet.
  • The value_if_true argument is, with the help of the MEDIAN function, the middle tender for the chosen project.
  • The value_if_false argument is omitted since it is not needed and its absence shortens the formula. If a project name that is not in the data table (such as Project C) is typed into cell D10, the formula returns a zero value.

Enter the Tutorial Data in Excel

Screenshot of Excel showing the project data

  1. Enter the example data, as shown above, into a blank Excel worksheet.

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

Enter the MEDIAN IF Nested Formula

When you create both a nested formula and an array formula, the entire formula must be typed into a single worksheet cell. When the formula is complete, do not press the Enter key or select a different cell because the formula will be turned into an array formula.

A #VALUE! error means that the formula was not entered correctly as an array.

  1. Select cell E10. This is where the formula results will display.

  2. Type the following formula in the cell: 

    =MEDIAN(IF(D3:D8=D10,E3:E8))

  3. Press and hold the Ctrl and Shift keys.

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

  5. The answer 15875 ($15,875 with formatting) appears 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.

Screenshot of Excel showing Project B data

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

Was this page helpful?