Excel IF Statement

What is the Excel IF Function?

The IF function is a logical function to analyze a condition. If the condition met the criteria (TRUE) return value_if_true argument, otherwise (FALSE) return value_if_false argument.

IF Syntax

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

logical_test (required), the condition you want to analyze, are in accordance with the criteria. The result is a TRUE / FALSE value.

value_if_true (required), returned value if logical test is TRUE

value_if_false (optional), returned value if logical test is FALSE. value_if_false argument is optional, if omitted then return FALSE.

How to Use IF Function in Excel

IF Then Statements Examples (One Criteria)

For example, there are data such as the image below.

Who has an overweight body? According to the BMI, overweight body has a BMI value greater than or equal to 25.

To find out anyone who has overweight body, no need to use IF function. Simply use the >= operator, you can find out who has overweight body, but the result is not informative, showing only TRUE/FALSE value, for the more informative result you can use Excel IF Statement

There are only 1 criteria, i.e. BMI>=25, showing “Overweight” text. If there are BMI value less than 25, IF function return nothing. Here are the steps how to use the IF function in excel.

• Place the cursor in cell E2
• Type the formula for IF function
• logical_test argument, type criteria #1 D2>=25
• value_if_true argument, type “Overweight”
• value_if_false argument, type nothing/blank space “”

If the formula is typed correctly, it will look like below

=IF(D2>=25,"Overweight","")

Do a copy in E2 then do a paste in range E3:E9. The results are as shown below.

More informative result, not just TRUE/FALSE values.

Excel IF AND Statements Examples (Two Criteria, ALL Must be TRUE)

With the same data as the previous example. The question is who has the ideal body weight?

The Ideal body weight has BMI value greater than or equal 18.5 AND less than or equal 24.9. Look like there are many criteria, but essentially there are only 2 criteria, i.e. ideal and no ideal body weight. The ideal body weight is determined by 2 criteria (BMI>=18.5 and BMI<=24.9) both must be TRUE. Use the AND function to evaluate both criteria.

Here are the steps using the IF function with the AND function in the logical_test argument.

• Place the cursor in cell E2
• Type the formula for the IF function
• The logical_test argument, fill with AND function and both criteria, AND(D2>=18.5,D2<=24.9)
• The value_if_true argument, Type “Ideal”
• The value_if_false argument, Type nothing/blank space “”

If the formula is typed correctly, it will look like below

=IF(AND(D2>=18.5,D2<=24.9),"Ideal","")

Do a copy in E2 then do a paste in range E3:E9. The results are as shown below.

Excel IF OR Statements Examples (Two Criteria, One Must be TRUE)

With the same data as the previous example. The question is who has no ideal body weight?

The No Ideal body weight has BMI value less than 18.5 OR greater than 24.9. There are 2 criteria, i.e. ideal and no ideal body weight, same as the previous example but with different questions. The no ideal body weight is determined by 2 criteria (BMI<18.5 or BMI>24.9) one of them must be TRUE. Use the OR function to evaluate both criteria.

Here are the steps using the IF function with the OR function in the logical_test argument.

• Place the cursor in cell E2
• Type the formula for the IF function
• The logical_test argument, fill with OR function and both criteria, OR(D2<18.5,D2>24.9)
• The value_if_true argument, Type “No Ideal”
• The value_if_false argument, Type nothing/blank space “”

If the formula is typed correctly, it will look like below

=IF(OR(D2<18.5,D2>24.9),"No Ideal","")

Do a copy in E2 then do a paste in range E3:E9. The results are as shown below.

Multiple IF Statements in Excel

With the same data as the previous example, does the IF function able to determine the weight categories of each name according to BMI value?

Of course, IF function able to do it. There are 6 criteria, because the IF function is only able to analyze 2 criteria, then it takes more than 1 IF function know as Multiple IF Statements or Nested IF Statements.

The number of required IF function is the number of criteria minus 1. There are 6 criteria, so it takes 5 IF function to determine the weight category for each name.

Another Logical Function