We can use a formula that combines the **MEDIAN** and **IF functions** to find the **median **of a set of data if the values meet a **criteria**. The steps below will walk through the process.

* Figure 1- How to Find the Median if Data Meets Certain Criteria*

**Syntax**

**=MEDIAN(IF(****logical_test****,value_if_true,value_if_false))**

**logical_test**: This is the criteria that must be met for the**median function**to operate**value_if_true**: This is the returned result by the**IF Function**that tells the**median function**that the criteria is met and**median function**can operate**value_if_false**: This is the opposite of**value_if_true**

**Formula**

**=MEDIAN(IF($B$5:$B$10=$E$5,$C$5:$C$10))**

**Setting up the Data**

Contractors have placed their bids for two separate projects in a company. The manager intends to find the median bid for **Project A** and **Project B**

**We will insert the Project names and Bids in Column B and Column C**

- Our
**result**will be returned in**Column F**

*Figure 2 – Setting up the Data*

**Median if Data Meets Certain Criteria**

- We will click on
**Cell F5** - We will insert the formula below into
**Cell F5**

**=MEDIAN(IF($B$5:$B$10=E5,$C$5:$C$10))** - Because this is an
**array formula**, we will press**CTRL + SHIFT + ENTER**

* Figure 3- Median if Data Meets Certain Criteria*

- We will click on
**Cell F5**again - We will
**double click**on the fill handle tool which is the small plus sign you see at the bottom right of**Cell F5.**Select and drag down to copy the formula to**Cell F6**.

* Figure 4- Result of Median if Data Meets Certain Criteria*

**Instant Connection to an Expert through our Excelchat Service**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment