How does the if formula work in excel. Using "if" statement in microsoft excel. We are ready to write the formula, we will do it in parts

Today we will look at the function IF.

The IF function is often used in Excel to solve many problems. Knowing her is very helpful. In this article, we will try to talk about its work using simple examples, it is enough to understand the construction of the IF function once and you can use it in the most complex cases.

IF function checks if the condition is true and returns one value if it is true and another value if not.

Syntax of the IF function very simple:

IF(log_expression ; [ value_if_true]; [value_if_false])

log_expression is any value or expression that evaluates to TRUE or FALSE.

What does it mean? An expression evaluates to TRUE if the expression is true.

In this part, you need to check for the compliance of the expression.

For example:

IF(A1=10 ; [value_if_true], [value_if_false]) - if A1 is equal to 10, then the expression A1=10 will give the value TRUE, and if not equal to 10, then FALSE

Another example

IF(A1>30 ; [value_if_true], [value_if_false]) - if the number in cell A1 is greater than 30, then A1>30 will return TRUE, and if less, then FALSE

Another example

IF(C1=”Yes” ; [value_if_true]; [value_if_false]) - if cell C1 contains the word “Yes”, then the expression will return TRUE, and if not, then C1=”Yes” will return FALSE

IF(log_expression ; [ value_if_true]; [value_if_false])

value_if_true, value_if_false- as you can see from their name, this is what needs to be done depending on what the expression log returned: TRUE and FALSE

An example of using the IF function in Excel

Consider using the IF function on practical example. We have an order table that we used when reviewing a job. We need to fill in the column for Bucket orders (the picture says “Table Orders” by mistake), that is, we need to select only orders with Buckets. This can be done in various ways, but we will use the IF function to show how it works with an example. (see picture)

To solve the problem, we write a formula using the IF function

IF(A3="Bucket",D3,"-")

As you can see, the IF function arguments are separated by a semicolon.

So, the first argument (expression log) A3="Bucket" checks whether cell A3 contains the word "Bucket", if it does, then the second argument of the IF function is executed ( value_if_true), in our case it is D3 (i.e., the cost of the order), if cell A3 is not equal to the word “Bucket”, then the third argument of the IF function is executed ( value_if_false), in our case it is “-” (that is, a dash will be written).

Thus, the value D3 will appear in cells E3, i.e. the number 240.

Microsoft Excel has powerful tools that can help you solve difficult computational problems. One of the most used tools in this set is the "IF" function.

Function value

When it is necessary to understand the meaning of the "IF" function in order to construct correct syntax queries. Thanks to its algorithm, some logical comparison is performed, depending on the results of which one of two actions will be performed.

Speaking more in simple words, the function "IF" in case of true value of some expression, performs one action, in case of false - another. At the same time, both an explicit value and a specific function, including "IF", can be used as actions. Due to this, in "IF" it allows a branch when performing a certain algorithm of actions when solving various problems.

Syntax "IF"

A simple description of most syntactic constructions is one of the main advantages that Excel is famous for. The "IF" function is also one of them - after the keyword in brackets, the condition is alternately indicated, the action for a true value, and then for a false one. In schematic form, it looks like this:

IF(logical_expression, [value_if_true], [value_if_false]);

Nesting

One of the features that distinguishes the "IF" function is nesting. That is, inside one construction, there may be another one, on the value of which the overall result of the query execution depends. In addition to the function itself, there may be others inside the "IF" function. But in the first case, this component can be located in any of the three parts of the syntactic construction.

Multiple Conditions

When working with complex problems, the "IF" function with several conditions is used, however, at this stage, most users have a problem. This is due to the specific problem of the multiconditionality of the algorithm. In Excel, the "IF" function checks only one comparison operation in a logical expression, that is, it will not work to use conjunction or disjunction. To check multiple conditions, you need to use the nesting property.

To understand how to specify multiple conditions in "IF", it is convenient to use an example. Let it be necessary to check whether the number in cell "A1" is in the given range - from 5 to 10. As you can see, in this case, it is required to test two conditions by checking for truth the comparison with two values - 5 and 10. To implement this example in Excel, you need to write the function in the following form:

=IF(A1>5,IF(A1<10;"входит в диапазон"; "не входит в диапазон");"не входит в диапазон")

To avoid repeated repetition of the output phrase, it is worth applying the nesting principle again, choosing as arguments the check of the return of the value of the functions, depending on which the output is performed, or at the very beginning use the "AND" function, combining all the conditions in it at once. This approach will complicate the understanding of the written construction with a small level of nesting, but under a significant number of conditions, this approach will be more optimal.

Special Feature Options

It is worth noting that the "IF" function allows you to leave one or more of its parameters blank. In this case, the results will depend on which arguments were omitted by the user.

If a void is left in place of the logical expression, then the result of the function will be the execution of the action responsible for the false execution of the algorithm. The reason for this is the fact that the program associates empty space with zero, which means "FALSE" in logical language. If one of the values responsible for execution in case of true or false is left empty, then when it is selected, the result will be "0".

Separately, it is worth noting the case when instead of a logical expression, not a construction is introduced that returns the value "TRUE" or "FALSE", but a certain set of characters or a reference to a cell. In the event that an expression containing something other than a numeric value or logical words is written as a parameter, this will cause an error when executing the function. If you specify the address of the cell or write some number / boolean value, then the result will determine this content. When a cell or condition contains the number 0, the word "FALSE" or emptiness, the result will be a false execution of the function. In all other cases, the true action script will be executed.

When working with the English version of Excel, you must take into account the fact that all functions are also written in English. In this case, the "IF" function will be written as IF, but otherwise the syntactic construction and operation algorithm will remain the same.

What you should pay attention to

Excel allows you to use up to 64 nested "IF" functions - this number is enough to solve almost all tasks, however, even this small number often becomes a problem for the user. There are several reasons for this: when creating a query, it is quite easy to make a mistake with writing a formula - according to statistics, every slightest inaccuracy in 25% of cases leads to an incorrect result, which is a fairly large indicator.

Another disadvantage of nesting "IF" heavily is poor readability. Despite the color highlighting by the program of some parts of the query, even a few nested functions, which are very difficult to parse. Thus, if after some time you have to return to the construction or start working with someone else's request, it will take a lot of time to understand the record. In addition, each function has its own pair of brackets, and if you accidentally put it in the wrong place, you will have to look for an error for a long time.

Examples

To consolidate understanding, it is worth considering in practice how the "IF" function works in Excel. The examples below show all the main ways to use it.

The simplest example for parsing how a function works is comparing two numbers. For the presence of variability, we will set the values of two numerical variables in cells A1 and B1, which we will compare with each other. To solve this problem, you should use the record of the following form:

=IF(A1=B1, "the numbers are equal", "the numbers are not equal").

In this case, if there are identical values in both cells, the result will be "numbers are equal", in all other cases - "numbers are not equal".

To consider working with several conditions, as an example, you can use finding the number of solutions. In this case, the check is performed by the discriminant - if it is less than zero, then there are no solutions, if it is equal to zero - it is one, in all other cases - there are two roots. To write this condition, it is enough to write a query of the following form:

For those who want to better understand all the possibilities that the function has, they are in the help section, which describes in detail the process of solving each of them.

There are many different functions in Excel that work based on checking logical conditions. For example, these are the functions IF, COUNTIF, SUMIF, etc. Also, logical conditions can be set in the usual formulas, if you need to get an affirmative answer: Yes or Not. For example, by asking simple logical conditions, you can answer questions like:

5 more than 8?
Is the content of cell A5 less than 8?
Maybe equal to 8?

Comparison Operators in Excel

Excel has a number of standard operators that are used to set simple logical conditions. All six possible comparison operators are shown in the table below:

How to set a condition in Excel

Comparison operators allow you to specify conditions that return the boolean values TRUE or FALSE. Examples of the use of logical conditions are presented below:

=A1=B1– This condition will return TRUE if the values in cells A1 and B1 are equal, or FALSE otherwise. By specifying this condition, you can compare text strings case-insensitively. For example, comparing "JANUARY" and "January" the formula will return TRUE.

=A1>B1– The following formula will return TRUE if the value of cell A1 is greater than that in B1. Otherwise, the formula will return FALSE. Such comparisons can also be set when working with text.

For example, if cell A1 stores the value "Orange" and cell B1 stores "Watermelon", then the formula will return FALSE because "Watermelon" is alphabetically lower than "Orange". The lower, the more.

=A1<=B1 – The formula will return TRUE if the value of cell A1 is less than or equal to the value in cell B1. Otherwise, the result will be FALSE.

=A1<>B1– The formula will return TRUE if the values of cells A1 and B1 are not equal. Otherwise, FALSE.

Excel has logical functions TRUE() and FALSE(), which have no arguments. These functions exist primarily for compatibility with other spreadsheets. You can enter TRUE and FALSE values directly into cells or formulas without using the function entry form, Excel will understand everything perfectly.

If you are sure that you have already mastered this topic well enough, you can refer to the article Use Excel Boolean Functions to Specify Complex Conditions to learn how to set conditions using various Boolean functions, for example AND() or OR().

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.

If the logical expression (condition) is true, then the value is true.
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.

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, the first check will be carried out first, 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.

=IF(B2=”male”,0, IF(C2=”run”,20%,10%))

We click Enter and we get the result according to the specified 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.

For 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.

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 fulfilled 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.

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%.

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.

Hello, friends! How often do you have to make a choice? For example, you want to buy a new phone, but you don’t have the full amount of money. Take out a loan or save? So today we will analyze how to make a choice in Excel spreadsheets. This allows you to make a conditional IF() function.

Conditional function IF()

In this function, the truth of one or more logical expressions is checked and a further action is selected. In logical expressions, the signs of the operations of the relationship between the compared values are used.

Relational operations in logical expressions

simple condition

What does the IF() function do? Look at the diagram. Here is a simple example of how the function works when determining the sign of a number a.

Flowchart "Simple Condition". Definition of negative and non-negative numbers

Condition a>=0 defines two possible options: non-negative number (zero or positive) and negative. Below is a diagram of how to write a formula in Excel. After the condition, separated by a semicolon, options for actions are listed. If the condition is true, the cell will display the text "non-negative", otherwise - "negative". That is, the record corresponding to the branch of the scheme "Yes", and then - "No".

Text data in a formula is enclosed in quotation marks, while formulas and numbers are written without them.

If the result should be the data obtained as a result of calculations, then we look at the following example. Let's increase a non-negative number by 10, and leave the negative number unchanged.

Flowchart "Simple Condition". Data calculation

The diagram shows that when the condition is met, the number increases by ten, and in Excel formula the calculation expression is written А1+10(highlighted in green). Otherwise, the number does not change, and here the calculation expression consists only of the designation of the number itself A1(highlighted in red).

This was a short introductory part for beginners who have just begun to comprehend the basics of Excel. Now let's look at a more serious example using a conditional function.

Exercise:
The progressive tax rate depends on income. If the company's income is more than a certain amount, then the tax rate is higher. Use the IF function to calculate the tax amount.

Solution:

The solution to this problem is shown in the figure below. But let's bring some clarity to this illustration. The main initial data for solving this problem are in columns A and B. Cell A5 indicates the threshold value of income at which the tax rate changes. The corresponding rates are indicated in cells B5 and B6. The income of firms is indicated in the range of cells B9:B14. The tax calculation formula is written in cell C9: =IF(B9>A$5,B9*B$6,B9*B$5). This formula needs to be copied into the lower cells (highlighted in yellow).

In the calculation formula, cell addresses are written as A$5, B$6, B$5. The dollar sign fixes the part of the address it precedes when copying the formula. Here, a prohibition is set on changing the line number in the cell address.

Compound condition

A compound condition consists of simple ones connected by logical operations AND () and OR ().

AND()- a logical operation that requires the simultaneous fulfillment of all the conditions associated with it.
OR()- a logical operation that requires the fulfillment of any of the listed conditions associated with it.

Logical operation AND ()

For example: Let's consider the spreadsheet "Record of entrance exams". To enroll an applicant in a university, he needs to overcome the passing score, and in mathematics the mark must be above 70 points.
Look carefully at the picture below.

In this example, the function IF() uses a compound condition linked by a logical operation AND(). Please note: the applicant Petrov is not enrolled, although the sum of his points is equal to the passing one.

Why did it happen? Let's take a closer look at the condition in our formula =IF(AND(E6>=D2,B6>70),"enrolled","not enrolled"). Boolean operation AND() requires all conditions to be met, but we only have one. Second condition B6>70 is not satisfied, so the compound condition evaluates to false. And the message “not enrolled” is displayed on the screen (remember the scheme - the “no” branch).

Exercise:
A trading company has arranged a holiday sale before the New Year. Calculate the amount of sales, taking into account the discount assigned during the sale period.

2. Determine the discount (in percent) using the IF() function. If the sale date falls within the holiday sale period, then a discount is assigned, otherwise the discount is zero. When specifying conditions, use the logical AND () function.

3. Determine the amount of the sale, taking into account the discount. Sale amount including discount = Total* (1- Discount%).

Solution:

In cell E7: =B7*C7
In cell F7: =IF(AND(D7>=D$4,D7<=E$4);B$4;0)
In cell G7: =E7*(1-F7)

and copy along the corresponding columns up to line 16 inclusive.

Logical operation OR()

Let's look at an example with a logical operation OR() in the same table. Let's just change the condition of the problem a little. To enroll an applicant, it is enough to have more than 60 points in mathematics or a total score of at least a passing one. Picture below.

Applicant Sidorov was enrolled, although he did not score a passing score. Here is the formula =IF(OR(B7>60;E7>D2;);"enrolled";"not enrolled"). Operation used here OR(), so at least one condition is sufficient. What happened, the first condition B7>60 true. It led to the output of a message about the enrollment of the applicant.

Exercise:
In a trading company, sales days are set - the last days of the month. Calculate the amount of sales, taking into account the discount assigned on the days of the sale.

1. Calculate the total cost of sales. Total = Cost* Quantity.

2. Determine the discount (in percent) using the IF() function. If the sale date coincides with the sale dates, then a discount is assigned, otherwise the discount is zero. When specifying conditions, use the logical OR() function.

3. Determine the amount of the sale, taking into account the discount. Sale amount including discount = Total* (1- Discount%)

Solution:

For calculations, you must enter the following formulas:

In cell E7: =B7*C7
In cell F7: =IF(OR(D7=D$4,D7=E$4,D7=F$4),B$4,0)
In cell G7: =E7*(1-F7)

and copy according to the corresponding columns up to line 15 inclusive.

Nested Conditions

If the use of compound conditions is not enough to solve the problem, then a more complex construction of nested conditions is used. See diagram.

AT this example the second IF() function is nested and is written at the site of the action that is called when the condition of the first function is not met. In Excel latest versions up to 64 attachments are allowed.

Exercise:
In order to reduce staff turnover, the administration decided to pay a bonus for continuous work experience at its enterprise. Calculate the allowance for continuous service in accordance with the Allowance table.

1. Determine the length of service of employees. Experience = 2018- Year of employment.

2. Using nested functions If, calculate the allowance for employees. Supplement (rub.) = Supplement (%) * Salary

Solution:

For calculations, you must enter the following formulas:

In cell D9: =2018-B9
In cell E9: =IF(D9>=B$6,C9*C$6,IF(D9>=B$5,C9*C$5,0))

and copy by corresponding columns up to and including line 19.

In this article, I tried to explain the use of a conditional function in Excel as detailed and intelligibly as possible. Of course, there are other conditional functions in Excel, but we will talk about them in the next article. Friends, if you were interested and useful this information don't forget to share it on in social networks. And also I will be glad to read your comments.

PS: Surprising facts

Dear reader! You have read the article to the end.
Did you get an answer to your question? Write a few words in the comments.
If no answer is found, indicate what you are looking for.