Microsoft Excel offers several useful functions that make navigating through different kinds of data easier. If you know how to use these functions properly, you can cut down on your work time and improve productivity levels by a significant margin. One of the most popular and commonly used functions is the IF function. It is versatile, easy to use, and highly effective, which is why it is one of the first functions students learn. Here’s a detailed introduction to If function in Excel:
What is the IF Function?
IF is one of the Logical Functions available in Excel and is a Worksheet Function, which means it can be used in a cell’s formula if needed. This formula returns a different value based on whether a particular condition is met or not. Here’s a small example illustrating the function:
- The condition is to decide whether a sale’s target has been met or not and the target is set at 500 units.
- The user can utilize the IF function to check a large pool of data and set values for TRUE and FALSE.
- The Return will be ‘Met’ if the target cell’s value is more than 500 and ‘Unmet’ if the value is less than 500.
- The IF function will evaluate the data and automatically mark areas where you haven’t reached the target sales volume with ‘Unmet.’
How Does the IF Function Work?
There are different ways to use the IF function as it can be used in conjunction with different kinds of logical operators. Here’s a look at some of the factors that influence how this function works:
1. Syntax
The Syntax for the IF Function is:
=IF (logical_test, [value_if_true], [value_if_false])
The Arguments for the IF function are:
Logical_test is the value that can be either TRUE or FALSE based on the parameters you set.
Value_if_true is the value that's returned if the condition is true and meets your parameters.
Value_if_false is the value that’s returned if the condition is false and doesn’t meet your parameters.
2. Nested IF Functions
It is possible to include more than one IF functions in one formula to ensure you have multiple results. For example, if you want to assign grades to a set of student test results, you can include multiple IF functions to assign grades like ‘A’ ‘B’ ‘C’ and so on. Here’s a look at an example to consider:
Suppose the D column in the spreadsheet consists of all the student scores. You need to assign a grade to every student based on these scores. This is the formula you can use for every student:
= IF (D2<70,“F”,IF(D2<75,”D”,IF(D2<85,”C”,IF(D2<95,”B”,”A”))))
‘D2’ refers to the cell D2 that contains a particular student’s score on the test. ‘F, D, C, B, A’ refer to the grades assigned. When you use this formula, the IF function will check the information provided in the D column to see if the student has scored less than 70, 75, 85, and 95 before assigning a particular grade.
3. Logical Operators
Users can include different kinds of Logical Operators in their IF function formulations to calculate a particular data set. Here’s a look at the operators commonly used:
- = equal to
- > greater than
- < less than
- >= greater than or equal to
- <= less than or equal to <> not equal to
- IF(A3=10,”Complete”)
- IF(A3>75,“Pass”)
- IF(A3<75,”Fail”)
- IF(A3>=60,“Stop”)
- IF(A3<=30,”Continue”)
- IF(A2<>21,”Denied”)
It is also possible to combine the IF function with the AND and OR functions in Excel. That allows you to carry out more complex calculations.
How Do You Use the IF Function in Excel?
Using the IF function is easy if you understand how it works. Here are some ways to insert the formula into your spreadsheet:
• Click on the Cell where you want to add the function.
• Go to the Formulas tab on Excel and open the ribbon.
• Click on the Logical button. That will show a dropdown menu of all Logical Functions, including IF.
• Select IF and enter the Arguments as explained earlier in the article.
• You need to enter data for the logical_test, value_if_true, and value_if_false.
• Once all values are entered, check the formula for accuracy before clicking on OK.
You can also type a formula directly into the Formula Bar in Excel. Most advanced users learn to do it eventually because it is easier and less time-consuming. As you can see, there's no reason to be intimidated by the IF function in Excel.
