The “IF” operator in Microsoft Excel: application and examples

Excel, of course, has a very rich functionality. And among the many different tools, the “IF” operator occupies a special place. It helps in solving completely different tasks, and users turn to this function much more often than others.

In this article, we will talk about what the “IF” operator is, and also consider the scope and principles of working with it.

Definition of the “IF” function and its purpose

The “IF” operator is an Excel program tool for checking a certain condition (logical expression) for execution.

That is, imagine that we have some kind of condition. The task of “IF” is to check whether the given condition is met and output a value based on the result of the check to the cell with the function.

1. If the logical expression (condition) is true, then the value is true.
2. If the logical expression (condition) is not met, the value is false.

The function formula itself in the program is the following expression:

=IF(condition, [value if condition is met], [value if condition is not met])

Using the “IF” Function with an Example

Perhaps the above information may not seem so clear. But, in fact, there is nothing complicated here. And in order to better understand the purpose of the function and its operation, consider the example below.

We have a table with the names of sports shoes. Imagine that we will soon have a sale, and all women’s shoes need to be discounted by 25%. In one of the columns in the table, the gender for each item is just spelled out.

Our task is to display the value “25%” in the “Discount” column for all rows with female names. And accordingly, the value is “0”, if the “Gender” column contains the value “male”

Filling in the data manually will take a lot of time, and there is a high probability of making a mistake somewhere, especially if the list is long. It is much easier in this case to automate the process using the “IF” statement.

To complete this task, you will need to write the following formula below:

=IF(B2=”female”,25%,0)

• Boolean expression: B2=”female”
• Value in case, the condition is met (true) – 25%
• The value if the condition is not met (false) is 0.

We write this formula in the topmost cell of the “Discount” column and press Enter. Don’t forget to put the equal sign (=) in front of the formula.

After that, for this cell, the result will be displayed according to our logical condition (don’t forget to set the cell format – percentage). If the check reveals that the gender is “female”, a value of 25% will be displayed. Otherwise, the value of the cell will be equal to 0. As a matter of fact, what we needed.

Now it remains only to copy this expression to all lines. To do this, move the mouse cursor to the lower right edge of the cell with the formula. The mouse pointer should turn into a cross. Hold down the left mouse button and drag the formula over all the lines that need to be checked according to the specified conditions.

That’s all, now we have applied the condition to all rows and got the result for each of them.

Applying “IF” with multiple conditions

We just looked at an example of using the “IF” operator with a single boolean expression. But the program also has the ability to set more than one condition. In this case, a check will be carried out first on the first one, and if it is successful, the set value will be displayed immediately. And only if the first logical expression is not executed, the check on the second one will take effect.

Let’s take a look at the same table as an example. But this time, let’s make it harder. Now you need to put down a discount on women’s shoes, depending on the sport.

The first condition is a gender check. If “male”, the value 0 is immediately displayed. If it is “female”, then the second condition is checked. If the sport is running – 20%, if tennis – 10%.

Let’s write the formula for these conditions in the cell we need.

=ЕСЛИ(B2=”мужской”;0; ЕСЛИ(C2=”бег”;20%;10%))

We click Enter and we get the result according to the specified conditions.

Next, we stretch the formula to all the remaining rows of the table.

Simultaneous fulfillment of two conditions

Also in Excel there is an opportunity to display data on the simultaneous fulfillment of two conditions. In this case, the value will be considered false if at least one of the conditions is not met. For this task, the operator “AND”.

Let’s take our table as an example. Now the 30% discount will be applied only if these are women’s shoes and are designed for running. If these conditions are met, the value of the cell will be equal to 30% at the same time, otherwise it will be 0.

To do this, we use the following formula:

=IF(AND(B2=”female”;C2=”running”);30%;0)

Press the Enter key to display the result in the cell.

Similar to the examples above, we stretch the formula to the rest of the lines.

OR operator

In this case, the value of the logical expression is considered true if one of the conditions is met. The second condition may not be satisfied in this case.

Let’s set the problem as follows. 35% discount applies to men’s tennis shoes only. If it’s a men’s running shoe or any women’s shoe, the discount is 0.

In this case, the following formula is needed:

=IF(OR(B2=”female”; C2=”running”);0;35%)

After pressing Enter, we will get the required value.

We stretch the formula down and discounts for the entire range are ready.

How to define IF functions using the Formula Builder

You can use the IF function not only by writing it manually in a cell or formula bar, but also through the Formula Builder.

Let’s see how it works. Suppose we again, as in the first example, need to put down a discount on all women’s shoes in the amount of 25%.

1. We put the cursor on the desired cell, go to the “Formulas” tab, then click “Insert Function”.
2. In the Formula Builder list that opens, select “IF” and click “Insert Function”.
3. The function settings window opens. In the field “logical expression” we write the condition by which the check will be carried out. In our case it is “B2=”female”.

   In the “True” field, write the value that should be displayed in the cell if the condition is met.

   In the “False” field – the value if the condition is not met.

4. After all the fields are filled in, click “Finish” to get the result.

Conclusion

One of the most popular and useful tools in Excel is the function IF, which checks the data for matching the conditions we set and gives the result automatically, which eliminates the possibility of errors due to the human factor. Therefore, knowledge and ability to use this tool will save time not only for performing many tasks, but also for searching for possible errors due to the “manual” mode of operation.