Excel IF Statement

What is Excel IF Statement?

IF Statement[1] is one of the popular Excel instructions that can be used as a decision-making statement. It is one of the foundational concepts in programming, and it gives the required intelligence to a program so that the program implements decisions based on the criteria set by the user.

In Excel, the IF statement is used in evaluating a logical or mathematical expression and getting the desired output based on the specified criteria. The IF statement works by checking the expression to see whether a condition is met and returns a value based on the output obtained.

For example, based on the criteria, it returns one predetermined value if the condition is found to be true and a different predefined value if the statement is found to be false. The IF Statement is sometimes referred to as the IF THEN ELSE statement.

IF Function in Excel vs. IF Statement in VBA

IF Statements are designed to execute the same functions, but they differ in several ways in how they work. The Excel IF statement works by checking if the condition is met and returns a value (TRUE). Otherwise, it returns the value FALSE.

Unlike the Excel IF Statement, the VBA IF statement does not return any value when it evaluates a condition. If the condition evaluates to TRUE, the program executes the instructions inside the IF block. However, if the condition evaluates to FALE, the program skips to the next block after the IF blocks and evaluates them sequentially until a condition evaluates to TRUE.

Syntax

Below is sample syntax for the If-Then-Else Statement:

If condition_1 Then

   statement_1

ElseIf condition_2 Then

  statement_2

ElseIf condition_n Then

   statement_n

Else

   Statement_else

End If

The code evaluates the conditions according to the order listed. The conditions are evaluated to a Boolean value, which should be either TRUE or FALSE. If the condition is found to be TRUE, the corresponding code will be executed, and there will be no other conditions to be evaluated.

The keyword “Then” is a directive that shows the program control that the instructions that follow immediately after the IF statement.

Once the program evaluates the condition to TRUE, the statement, i.e., statement_1, statement_2…..statement_n, are the codes to be executed. Statement__else is the code that will be executed if condition_1, condition_2,…condition_n are evaluated to be false.

The last line of the syntax ends with the code “End If.” The code tells the program that this is the last line of the IF function and that there are no further conditions to be evaluated.

How IF Statement Works

All the specified conditions, along with their IF statements, are evaluated sequentially, which means that the program evaluates conditions starting with Condition_1. If the program evaluates the first condition to TRUE, then all other conditions in the sequence will be skipped.

However, if condition_1 is evaluated to false, the program will move to evaluate the second condition in the sequence, which is condition_2. If condition_2 is evaluated to TRUE, the program will execute all the instructions contained in the ELSEIF block. All the other conditions following condition_2 in the sequence will be skipped. If condition_2 is evaluated to FALSE, the program will move to evaluate condition_n.

If condition_n evaluates to TRUE, the program will execute instructions in the ELSEIF block and skip the ELSE block. However, if condition_n evaluates to FALSE, then the program will move to execute the ELSE Block.

When evaluating the conditions sequentially, only a single code block can be executed at a time. The “End If” statement notifies the program that the conditional statements have reached an end.

How to Write IF Statement in VBA (Example)

One of the applications of the IF statement is checking whether a number is positive or negative. It can be achieved by writing a program that checks whether a number is less than or greater than 0 to determine if it is a positive or negative number.

The VBA code should be written as follows:

If number < 0 Then

  MsgBox “Value is negative!”

Else

  MsgBox “Value is positive!”

End If

The VBA code above allows users to input random numbers, and the program then checks whether the entered value is greater than zero or less than zero. If the user enters a value that is less than zero, the program will display the result “Value is negative.” A value greater than zero will display the result “Value is positive.”

Using AND and OR Operators in IF Statement

When there are multiple dependent conditions, users can use logical operators such as AND and OR inside a single IF statement.

The syntax of using AND/OR conditions with the IF statement is as follows:

If condition_1and condition_2 Then

True_code

Else

False-code

End IF

In the system above, the true_code is executed when condition_1 AND condition_2 are met. If either condition_1 or condition_2 is false, then the program will execute the false_code. It means that the logical operator “AND” only returns a true value when both conditions are met/True.

With the operator “OR,” if one of the conditions is met/True, the program will execute the true_code. If both conditions are false, the program will execute the false_code. It means that OR only returns TRUE if at least one of the conditions is met.

Related Readings

Thank you for reading CFI’s guide to the Excel IF Statement. To keep learning and developing your knowledge base, please explore the additional relevant resources below:

• Excel VBA
• How to Debug Code
• Transitioning from Excel to Python
• VBA Macros
• See all Excel resources