1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Excel MEDIAN IF Array Formula Tutorial

Combine the MEDIAN and IF Functions in an Array Formula

By

Excel MEDIAN IF Array Formula

Excel MEDIAN IF Array Formula

© Ted French

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

  1. 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
    
  2. 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.

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

  2. Type the following:

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

Creating the Array Formula

  1. Press and hold down the Ctrl and Shift keys on the keyboard

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

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

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

©2014 About.com. All rights reserved.