IF statement in Excel. All about the operator – application, examples

The set of functions in the Excel program, of course, is truly huge. In particular, it is possible to program data processing to a certain extent. Responsible for this, among other things, the function IF. It makes it possible to implement almost any task. That is why this operator is used much more often than others. Today we will try to explain what it does and how it can be used.

IF function – definition and scope

Using the function IF the user can instruct the program to check if a particular cell matches a given criterion. If we have a condition under which we only need to carry out the task, then Excel first checks, after which it displays the calculation result in the cell in which this function is written. But this is only if this function is used in conjunction with another function. The operator himself IF produces two results:

1. TRUE. This is if an expression or cell matches a certain criterion.
2. FALSE. This operator is shown if there is no match.

The syntax of the formula is as follows (in a universal form): =IF(condition; [value if condition is met]; [value if condition is not met]). The function can be combined with others. In this case, other operators must be written in the corresponding arguments. For example, you can make it so that it checks if the number is positive, and if so, find the arithmetic mean. Of course, there is one function that does the same, but this example demonstrates quite clearly how the function works. IF. As for the applications in which the function can be used IF, then there are a huge number of them:

1. Climatology.
2. Sales and business.
3. Marketing.
4. Accounting.

And so on. Whichever area you name, and there will be an application for this function.

How to use the IF function in Excel – examples

Let’s take another example of how we can use the function IF in Excel. Suppose we have a table containing the names of sneakers. Let’s say there’s a big sale on women’s shoes that calls for a 25 percent discount on all items. To make this check, there is a special column indicating the gender for which the sneaker is intended.

Accordingly, the condition of this task will be the equality of the sex to the female. If, as a result of the check, it is found that this criterion is true, then in the place where this formula is displayed, you need to write the discount amount – 25%. If it is false, specify the value 0, since the discount is not provided in this case.

Of course, you can fill in the required cells manually. But this can take a huge amount of time. In addition, the human factor, due to which misprints and distortions of information may occur, has also not been canceled. The computer doesn’t make mistakes. Therefore, if the amount of information is too large, then it is better to use the function IF.

To achieve the goal set at the first stage, it is necessary to select the cell where the resulting value will be displayed and write the following formula: =IF(B2=”female”,25%,0). Let’s decode this function:

1. IF is directly an operator.
2. B2=”feminine” is the criterion to be met.
3. This is followed by the value that will be displayed if the sneakers are created for women and the value shown if it is found that the sneakers are men’s, children’s or any other that do not meet the condition specified in the first argument.

Where is the best place to write this formula? In general, the place can be chosen arbitrarily, but in our case, these are the cells under the heading of the “Discount” column.

It is important not to forget to put the = sign in front of the formula. Otherwise, Excel will read it as plain text.

After the formula is entered, you need to press the enter key, after which the table will automatically be filled with the correct value. In the table below, we can see that the first check turned out to be correct. The program automatically determined the gender of these sneakers and assigned them a discount of a quarter of the price. The result has been achieved.

Now it remains to fill in the remaining lines. To do this, you do not need to copy the formula into each cell individually. It is enough to find the square in the lower right corner, move the mouse cursor over it, make sure that it has turned into a cross icon and drag the marker to the very bottom row of the table. Then Excel will do everything for you.

Using the IF Function with Multiple Conditions

Previously, the simplest case of using the function was considered IF, in which there is only one logical expression. But what if you need to check a cell against several conditions? This can also be done using the built-in functionality of Excel.

One of the special cases of checking for several conditions is to check for compliance with the first and if it turns out to be false, check for the second, third, and so on. Or, if the value is true, check another criterion. Here, as the user wants, the logic of actions will be approximately the same. If you thoughtfully read what is written above, then you might already have guessed how to do it. But let’s add more visibility.

To do this, let’s make the task more difficult. We now need to assign a discount exclusively to women’s sneakers, but depending on the sport for which they are intended, the size of the discount should be different. The formula at first glance will be somewhat more complicated, but in general, it will fall into the same logic as the previous one: =ЕСЛИ(B2=”мужской”;0; ЕСЛИ(C2=”бег”;20%;10%)).

Next, we perform the same actions as in the previous case: press Enter and fill in all the following lines. We get such a result.

How does this formula work? First function first IF checks if the footwear is male. If not, then the second function is executed. IF, which first checks if the shoes are designed for running. If yes, then a 20% discount is assigned. If not, the discount is 10%. As you can see, other functions can be used as function arguments, and this gives additional possibilities.

How to use the IF function to fulfill 2 conditions at once

In addition, using Excel, you can check for compliance with two conditions at once. For this, another function is used, which is called И. This logical operator combines two conditions and does it not only in a function IF. It can be used in many other functions as well.

Let’s go back to our table. Now the discount should be larger, but only apply to women’s running shoes. If, after checking, it turns out that both conditions have been met, then the discount amount of 30% will be recorded in the “Discount” field. If it is found that at least one of the conditions does not work, then the discount does not apply to such a product. The formula in this case would be: =IF(AND(B2=”female”;C2=”running”);30%;0).

Further, all actions performed further repeat the two previous examples. First, we press the enter key, and then we drag the value to all the other cells that are in this table.

The syntax of the AND function, as we see, consists of several arguments. The first is the first condition, the second is the second, and so on. You can use more than two arguments and check for multiple conditions at once. But in practice, such situations rarely happen. More than three conditions at the same time – almost never happens. The sequence of actions performed by this function is as follows:

1. First, the formula checks the first condition – whether the shoes are women’s.
2. Excel then analyzes the second criterion – whether the shoes are designed for running.
3. If, as a result of the test, it turns out that both criteria return a value TRUE, then the result of the function IF turns out to be true. Therefore, the action programmed in the corresponding argument is performed.
4. If it turns out that at least one of the checks returns a result LYING, it and a function И will return this result. Therefore, the result written in the third argument of the function will be displayed IF.

As you can see, the logic of actions is very simple and easy to understand on an intuitive level.

OR operator in Excel

The OR operator works in a similar way and has a similar syntax. But the type of verification is slightly different. This function returns a value TRUE if at least one check returns a result TRUE. If all checks give a false result, then, accordingly, the function OR returns the value LYING.

Accordingly, if the function OR  returns result TRUE for at least one of the values, then the function IF will write the value that was specified in the second argument. And only if the value does not meet all the criteria, the text or number specified in the third argument of this function is returned.

To demonstrate this principle in practice, let’s use an example again. The problem is now the following: the discount is given either on men’s shoes or tennis shoes. In this case, the discount will be 35%. If the shoes are women’s or designed for running, then there will be no discount for such a heading.

To achieve such a goal, you need to write the following formula in the cell, which is located directly under the inscription “Discount”: =IF(OR(B2=”female”; C2=”running”);0;35%). After we press the enter key and drag this formula to the rest of the cells, we get the following result.

How to define an IF function using the Formula Builder

Of course, to a certain extent, writing a formula by hand is even more convenient than using other tools. But if you are a beginner, then the situation changes dramatically. In order not to get confused in entering arguments, as well as to indicate the correct name of each of the functions, there is a special tool called the Function Entry Wizard or Formula Builder. Let’s take a look at the detailed mechanism of its work. Suppose we were given the task by management to analyze the available range of products and assign a 25% discount to all women’s sneakers. The sequence of actions in this case will be as follows:

1. We open the function entry wizard by clicking the corresponding button on the Formulas tab (it is highlighted with a red rectangle in the screenshot).
2. Next, a small formula builder panel opens, in which we select the function that we need. It can be selected directly from the list or searched through the search field. We already have it in the list of 10 of those that have been used recently, so we click on it and click on the “Insert Function” button.
3. After that, a window for setting the function arguments will open before our eyes. At the bottom of this panel, you can also see what the selected function does. Each of the arguments is signed, so you don’t have to remember the sequence. We first enter a logical expression that includes a number or cell, as well as a value to check for compliance with. Next, values ​​are entered if true and value if false.
4. After all the steps are completed, click on the “Finish” button.

Now we get the result. With it, we perform the same actions as in the previous case, namely, we point the mouse at the square in the lower right corner and drag the formula to all the remaining cells. So the function IF is truly the most popular and important operator among all that exist. It checks the data against certain criteria and performs appropriate actions if the check returns a result. TRUE or LYING. This allows you to greatly simplify the processing of big data and not to do a huge number of actions, delegating this dirty work to the computer.