How to Combine the MIN and IF Functions in an Excel Array Formula

Find the Smallest Value for a Range of Data Meeting a Specific Criterion

excel-2013-max-if-array-formula.jpg
MIN IF Array Formula in Excel. © Ted French

In this tutorial example, we have heat times for two events from a track meet -- the 100 and 200 meter sprints.

Using a MIN IF array formula will allow us to find, in turn, the fastest heat time for each race with one formula.

The job of each part of the formula is:

  • the MIN function finds the fastest or smallest time for the event chosen
  • The IF function allows us to choose the race by setting a condition using the race 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 (race times) the MIN function will examine to find the fastest time

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.

MIN IF Nested Formula Syntax and Arguments

The syntax for the MIN IF formula is:

=MIN( IF ( logical_test, value_if_true, value_if_false ) )

  • Since the IF function is nested inside the MIN function, the entire IF function becomes the sole argument for the MIN 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

In this example:

  • the logical test tries to find a match for the race name typed into cell D10 of the worksheet
  • The value_if_true argument will be, with the help of the MIN function, the fastest time for the chosen race
  • The value_if_false argument will be omitted since it is not needed and its absence will shorten the formula. If a race name that is not in the data table - such as the 400 meters - is typed into cell D10 the formula will return a zero ( 0 )

    Excel's MIN IF Array Formula Example

    Entering the Tutorial Data

    1. Enter the following data into cells D1 to E9 as seen in the image above:
       Race Times	
       Race	 Time (sec)
      100 meters	11.77
      100 meters	11.87
      100 meters	11.83
      200 meters	21.54
      200 meters	21.50
      200 meters	21.49
       Race	 Fastest Heat (sec)
      
    2. In cell D10 type " 100 meters " (no quotes). The formula will look in this cell to find which of the races we want it to find the fastest time for

    Entering the MIN 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:

      = MIN( 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 11.77 should appear in cell F10 since this is the fastest (smallest) time for the three 100 meter sprint heats
    4. The complete array formula

      { = MIN( 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 fastest time for the 200 meters

      Type 200 meters into cell D10 and press the Enter key on the keyboard.

      The formula should return the time of 21.49 seconds in cell E10.