Excel Essentials: Excel IF Formula
Excel IF Formula.
The Excel if formula or more correctly the IF Function checks a condition that must be either true or false.
If the condition is true, the Excel if formula carries out whatever instructions are in the true section.If the condition is false, the Excel if formula carries out whatever instructions are in the False section
The IF function has three arguments: the condition you want to check, the instructions if the condition is true, and the instructions if the condition is false.
Here is the Excel IF()Syntax:
IF(Logical_test, Action_if_true, Action_if_false)
The logical_test evaluates an expression to see if it passes the test, i.e. is TRUE or does not pass the test, i.e. is FALSE
Action_if_true can be a value or an operation. Whichever, the result is placed in the cell that contains the IF ( ) Function if the logical_test is true.
Action_if_false can be a value or an operation. Whichever, the result is placed in the cell that contains the IF ( ) Function if the logical_test is false.
Excel IF Formula Example.
Let's look at an example for calculating bonuses based on total sales.
A company offers it's salesman a 10% bonus if the value of the total sales is over 4,000, otherwise the sales reps get no bonus. We will put the Bonus breakpoint in cell C1 and the Bonus amount in C2.
When translated into the IF ( ) function,the Excel IF formula formula will look like the following:
Note the importance of using cell References for the variables. If the commission rate changes, I only have to change the value in cell reference C2 and not change maybe hundreds of individual formulas.
Also note the importance of making C1 and C2 absolute references. For an understanding of different types of cell references , see our essential primer on Relative & Absolute references
You can build this Excel IF Function and other Functions using the Function help box. Type in the name of the Function into a cell. Make sure you include the first ( and then click the Fx symbol to the left of the Formula bar.
This Function helps box assist you in giving the correct data or arguments to the Function.
Nested IF Function Example:
We have placed the following IF function in cell G15
=IF (F15=1,1.5, IF(F15=2, 1.4,1))
In this example, the Excel if formula checks if F15 is equal to 1, if it is , then G15 is set to 1.5.
But if it is not, then the second Excel if formula is evaluated. If F15 is equal to 2, then G15 is set to 1.4, otherwise G15 is set to 1
In Excel 2003, you can have up to a maximum of 7 nested if statements. From Excel 2007, that has risen to over 100.