Function IF one of the most popular and frequently used Excel functions. Using it in conjunction with comparison operators and other logical Excel functions, you can solve quite complex problems. In this lesson, we will try to analyze its action using simple examples, and also learn how to use several functions at once. IF in one formula.
Briefly about the syntax
Function IF has only three arguments:
=IF(specified_condition; value_if_TRUE; value_if_FALSE)
The first argument is the condition by which the formula can make decisions. The condition is checked in the very first place and can only return two values - TRUE or FALSE. If the condition is true, then the formula will return the second argument, otherwise the third.
For how to set conditions in Excel, read the articles: How to set a simple logical condition in Excel and Use Excel logical functions to set complex conditions.
Example 1
Let’s look at the example shown in the figures below. In this example, the function IF first check the condition A1>25. If so, then the formula will return the text string “greater than 25”, in any other case – “less than or equal to 25”.
Example 2
Function IF is very flexible and can be used in different situations. Let’s consider one more example. The table below shows the results of the recertification of the company’s employees:
In column C, we need to put the result of the exam, which should contain only two options: Handed over or Did not pass. Those who scored more than 45 points passed the exam, the rest did not.
- Select the cell where you want to enter the formula. In our case, this is cell C3.
- Enter an expression into it: =IF(B3>45, Passed, Failed) and press Enter.
- This formula compares the value in cell B3 with the number 45, if the value is greater than 45, then returns the string “Passed”, otherwise “Failed”.
- By copying the formula to the remaining cells of the table, you can see that 2 out of 5 people did not pass the recertification.
IF function and multiple conditions
functions IF You can nest within each other if you need to expand your decision options in Excel. For example, for the case of re-certification of employees considered earlier, it is required to put down not the result, but an assessment from the series: Excellent, Good and Bad. Grade Fine is given when the number of points is more than 60, the score Well at more than 45 and score Poorly in other cases.
- To solve this problem, enter the following formula in cell C3:=IF(B3>60,”Great”,IF(B2>45,”Good”,”Bad”)) and press Enter.
- This formula handles two conditions at once. First, the first condition is checked: B3>60. If it is true, then the formula returns the value “Excellent”, and the remaining conditions are not processed. If the first condition is false, then the function IF goes to the second one: B2>45. If the second condition is true, then the formula returns the value “Good”, and if false, then “Bad”.
- By copying the formula to the rest of the cells of the table, you can see that one person passed with excellent marks, and two people each passed good and bad marks.
As you can see, instead of the second and third arguments of the function IF you can substitute new functions IF, thereby expanding the number of conditions that the formula can handle. Thus, you can create the desired number of attachments. True, there is an obvious drawback of such a design, after 3-5 investments, the formula will become unreadable and cumbersome, and it will be impossible to work with it.
In Excel, there are more noble tools for handling a large number of conditions, for example, the function VPR or VIEW.
So, in this lesson we have considered the logical function IF in all its glory and examples, and also dismantled a simple example using several functions at once IF in one formula. I hope this information was useful to you. Good luck and great success in learning Microsoft Excel!