Produce Multiple Outputs in an IF Statement in Excel

The Excel IF function checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.

The syntax of the function is:

IF(logical_test, [value_if_true], [value_if_false])

By default, the function has only two outcomes but there are some situations when we may want multiple or more than two outcomes.

We can achieve multiple outputs using the IF statement by nesting the IF functions and using the IF statement together with other Excel functions such as the AND logical function and the OR logical function.

In this tutorial, we will use five examples to explain how we can produce multiple outputs in an IF statement.

Example 1: Use the multiple nested IF functions

In this example, we will use two datasets. The first dataset is a school’s grading system.

The second dataset is a student’s scores in a certain subject. Letter grades need to be assigned to the scores according to the school’s grading system.

To assign letter grades to the scores, we do the following:

1. Select cell C2 and type in the formula:

2. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column.

The letter grades are assigned to the various scores.

Explanation of the formula

• The IF statement checks if the score in cell B2 is less than or equal to 79. If that is true it returns grade letter D and stops checking.
• If cell B2 has a score that is greater than 79 but equal to or less than 87, the grade letter C is returned.
• If cell B2 has a score that is greater than 87 but equal to or less than 93, the grade letter B is returned.
• If the score is greater than 93, the grade letter A is returned.

Example 2: Use the IF statement and the AND function

In this example, we use the combination of the IF and AND functions. The AND function checks whether all arguments are TRUE and returns TRUE if all the arguments are TRUE. It returns FALSE if any or all the arguments are FALSE.

In this example, a candidate is only promoted if he/she scores at least 60 on the theory and practical tests.

To check if a candidate qualifies for promotion or not we do the following:

1. Select cell D3 and type in the formula:

2. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column.

The formula returns Promoted if both conditions are true and return Not Promoted otherwise.

Example 3: Use the IF and OR functions

In this example, we use both the IF function and the OR function to produce multiple outputs.

The OR function is a logical function that checks whether any of the arguments are TRUE and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE.

In the following dataset, we will determine if a candidate qualifies for promotion or not based on whether the candidate got a score of at least 60 in either of the tests:

We use the following steps:

1. Select cell D3 and type in the formula:

2. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column.

The formula returns Promoted if either of the conditions is true and returns Not Promoted if both conditions are false.

Example 4: Apply IF, OR, and AND functions

We will use the dataset we have used in the previous example to determine the promotion of candidates using the IF, OR, and AND functions.

We use the following steps:

1. Select cell D3 and type in the formula:

2. Press the Enter key or double-click or drag down the fill handle to copy the formula down the column.

Explanation of the formula

• The formula returns Promoted if the value in cell B3 is >=50 and the value in the cell is C3>=60.
• The formula returns Promoted if the value in cell B3 is >=40 and values in the cell is C3>=45.
• The formula returns Not Promoted if both of the above are not true.

Example 5: Use the IF and AVERAGE function

In this example, we will use both the IF and AVERAGE functions to determine if the performance of the student is Excellent, Good, Poor, or Satisfactory based on their average scores.

We will use the following dataset to show how this is done:

We use the following steps:

1. Select cell B7 and type in the formula:

2. Press the Enter key and drag the fill handle to cell D7 to copy the formula to those cells.

Explanation of the formula

• The formula returns Excellent if the average score of the student is 95 and above.
• The formula returns Good if the average score of the student is 90 and above but less than 95.
• The formula returns Satisfactory if the average score of the student is 70 and above but less than 90.
• The formula returns Poor if the average score of the student is below 70.

Conclusion

The IF function by default returns only two outcomes but there are some situations when we may want multiple or more than two outcomes.

This can be achieved by using nested IF functions and using the function together with other Excel functions.

In this tutorial we have looked at 5 ways we can use the IF statement to produce multiple outputs. The examples given involve the use of nested IF functions, the use of IF function together with the AND logical function, the use of IF function together with the OR logical function, the use of the combination of IF function and OR and AND functions, and the application of the IF function together with the AVERAGE function.