What is Excel IF Statement?
IF Statement 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.
- The IF statement is a decision-making statement that guides a program to make decisions based on specified criteria.
- The IF statement executes one set of code if a specified condition is met (TRUE) or another set of code evaluates to FALSE.
- It is a built-in function in Excel, and it can be used as a VBA function in Excel.
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.
Below is sample syntax for the If-Then-Else Statement:
If condition_1 Then
ElseIf condition_2 Then
ElseIf condition_n Then
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!”
MsgBox “Value is positive!”
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
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.
CFI offers the Financial Modeling & Valuation Analyst (FMVA)™ certification program for those looking to take their careers to the next level. To keep learning and developing your knowledge base, please explore the additional relevant resources below: