The most commonly used logical equation, the IF Function has two results based on a single logical test. It checks the logical test and if that is TRUE it yields one value but if it is FALSE it has a completely different value. The IF Function can be located in the FORMULAS Tab under Logical. The syntax or formula for the IF Function is =IF(logical_test,value_if_true,value_if_false)
The logical_test can be in many formats
- Text (looking to see if the cell contains a word or series of text data)
- Nested IF Functions
- Logical Operators (Greater than, Less Than or Equal To)
- OR Functions
- Time and Date Functions
There are so many ways to use the IF Function and it is one of the most powerful Functions to use in Excel.
Tip: If one of the value_if elements are left blank [IF(logical_test,,value_if_false)] Excel will return a 0 as the value if the logical_test is TRUE, however, if you enter quotation marks Excel will leave the cell blank for all TRUE logical_tests.read more about cell locking in excel at https://sgrdimsr.org/lock-cells-excel/
In a text logical_test, Excel searches for a specific word or other text and provides a value if the cell has that text and another if the cell doesn’t have that text. In the following example, we are selling a new software program. There are six orders submitted but more are coming in each day. Rather than fill out the entire worksheet we utilize the IF Function to auto-fill as orders are entered. The first set of data we want pre-filled is the price. The software program can be downloaded online for $150.00 or a desktop version can be purchased for $250.00.
- In cell B3, enter =IF(B2=”Online”,150,250), saying if the Item is downloaded online return a value of $150.00 if not then it must be Desktop so return a value of $250.00. (If you want you can format the cells in Row 3 to be dollar value by clicking the dollar sign in the number formatting box in the HOME Tab).
- Copy the formula across Row 3 and the price will auto-fill. Note, anything to the right of Column G will have a value of $250.00 even though the cell is blank because the IF statement clearly says that if it doesn’t equal Online return a value of $250. We can rectify this using nested IF Functions.
Nested IF Functions:
As in the above example it may become necessary to use several IF Functions within one cell to get the proper values returned. Leading off that example we want to tell Excel that if Row 2 has a value of
“Online” then yes, we want $150, we also still want $250 returned for the Desktop value but if the cell is blank we want $0 returned.
- Change the formula in cell B3 to =IF(ISBLANK(B2),0,IF(B2=”Online”,150,250))
- We used the ISBLANK Function (found in More Functions and Select the Information menu), this function serves to check the cell to see if it is empty. The formula reads if Cell B2 is empty, don’t enter a value, if it filled then check to see if it equals “Online” if it does return a 150, if not return a 250.
- When copied across the row it will now leave an empty cell in Row 3 for every empty cell in Row 2. When Order 106 is placed the only information that needs to be entered is the item and price will auto-fill.
Note: To have an actual $0.00 returned for a true ISBLANK value put quotations around the 0 making the function =IF(ISBLANK(B2),”0”,IF(B2=”Online”,150,250))
Logical Operators in logical_test:
Examining whether one value is less than, greater than or equal to another value. The Company offers a 20% discount on all orders that have a quantity of 5 or more.