### MEDIAN IF Array Formula Overview

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

### 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 for the MEDIAN IF formula is:

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

- 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 to see if it is 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 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 ( 0 )

### Entering the Tutorial Data

- Enter the following data into cells D1 to E9 as seen in the image above:
Project Tenders Project Tender Project A $15,785 Project A $15,365 Project A $16,472 Project B $24,365 Project B $24,612 Project B $23,999 Project Middle Tender

- In cell D10 type " Project A " (no quotes). The formula will look in this cell to find which project we want it to find the middle tender for

### 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.

- Click on cell E10 - the location where the formula results will be displayed
- Type the following:
### = MEDIAN ( IF( D3:D8=D10, E3:E8 ) )

### Creating the Array Formula

- Press and hold down the
**Ctrl**and**Shift**keys on the keyboard - Press the
**Enter**key on the keyboard to create the array formula - The answer
**15875**( $15,875 with formatting ) should appear in cell E10 since this is the middle tender for Project A - The complete array formula
### { = MEDIAN ( IF( D3:D8=D10, E3:E8 ) ) }

can be seen in the formula bar above the worksheet

### 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.

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