Condition if in Excel. How to calculate a sales bonus in Excel using nested if() functions. Preventing division by zero

Function IF() is one of the most powerful tools in the world of functions. If you can master it in your work, you will move to the next level of creating formulas.

Let's start with the simplest possible use of the formula. Syntax: IF (logical_expression; value_if true), Where log_expression- a logical expression, that is, an expression returning the value TRUE or FALSE (or equivalent numerical values: 0 if FALSE, and any positive value is TRUE, and where value_if true— the value returned by the function if log_expression has the value TRUE.

For example, let's look at the following formula: =IF(A1>1000,"many") . The logical expression A1>100 0 is used for verification. Let's say you add this formula to cell B1. If the Boolean expression is true (it is true when the number in A1 is greater than 1000), the function returns the value "many", which is what you will see in cell B1 (if A1 is less than 1000, you will see the value FALSE).

Another simple use of the function is to check for warnings. For example, if you have a book with the results of increasing sales of various products as a percentage. It will be useful to mark products with decreased sales in a specific way. The basic formula for this mark is: =IF(cell

Here cell- address of the cell to be checked, and mark- a certain kind of text to display on the screen to indicate a negative value. Here is an example: =IF(B1

A slightly improved version of the mark, in which it will depend on the magnitude of the negative value, looks like this: =REPEAT("

Handling a false result

As you can see in Fig. 4.15, if in function IF() Boolean expression takes a negative value, the function returns FALSE as a result. This is not a critical drawback, but it makes the worksheets seem “heavier” than if the result were to return, for example, an empty line.

To do this, you need to use the extended function syntax: IF(logical_expression; value_if_true; value_if_false). We already know the first two arguments, but the last argument value_if_false specifies the return value of the function IF(), when the result of evaluating a log_expression is false.

For example, consider the following formula: =IF(A1>1000,"many","little") . If this time cell A1 contains a number less than or equal to 1000, the formula will output the string "few". For the example with marking negative sales volumes (see Fig. 4.15 above), you must use the following formula: =IF(B1

As you can judge from Fig. 4.16, the worksheet now looks much better.

Preventing division by zero

As we indicated earlier, the function returns an error #DIV/0!, if the formula tries to divide some value by 0. To eliminate even the possibility of making such an error, you can use the function IF() to check division.

For example, the simplest calculation of the percentage of gross profit using the formula (Revenue - Expenses) / Income. To make sure that the Income value is not equal to zero, use the following formula (it is assumed that the words Income and Expenses will be replaced with the necessary values): =IF(Revenue 0; (Revenue - Expenses)/Revenue; "Revenue is 0!" ) . If a logical expression Income 0 is correct, which means the calculation will take place according to the formula. Otherwise, the function will return a warning message instead of an error.

The important objectives of the educational course at school are the development of basic mental operations in students (analysis, synthesis, comparison, generalization), the normalization of the relationship between their activities and speech, the formation of mental work techniques: analysis of initial data, activity planning, implementation of step-by-step and final self-control, for which lesson planning of the learning process is used. Great importance is attached to the ability to talk about the work performed with the correct use of appropriate terminology and the establishment of logical connections in the material presented.

The IF function belongs to the logical functions and is one of the most frequently used in work. Using this operator, you can perform various tasks when you need to compare some data and produce the result. The IF function in Excel makes it possible to use branching algorithms, build a decision tree, etc.

Video on using the IF function in Excel

Examples of using the IF operator

The IF function looks like this:

IF (expression; true; false).

And now a little more detail:

For example, you can enter the number 8 in field C1, and write this in field D1: =IF(C1<10; 1; 2). То есть программа будет сравнивать значение в поле C1 с цифрой 10, и когда оно окажется меньше 10 (C1<10), то в поле D1 будет написана единица. В противном случае программа выдаст число 2.

The following example: there is a list of students, as well as their grades that they received on the exam: 5, 4, 3 and 2. The condition of the task is that for each student you need to write a text comment “passed” or “failed”. That is, if a student received a grade of 3 or higher, then he passed the exam, otherwise he did not pass. To solve this problem you can write this: =IF(C1<3; «не сдал»; «сдал»). Excel будет сравнивать оценку каждого студента, и когда она будет меньше 3 (т.е. двойка), то в соответствующем поле будет написан комментарий «не сдал». Если же оценка будет 3 или выше, то в ячейке будет отмечено, что этот студент сдал экзамен. The logical IF function works in Excel according to this simple principle. It is worth noting that text comments must be placed in quotation marks.

When using the IF function in Excel, you can use the following comparison operators:

more (>);
less (<);
greater than or equal to (>=);
less or equal (<=);
equals(=);
not equal (< >).

Examples of AND, OR functions

Also, when using the IF function in Excel, it is possible to combine it with simple comparison operators (like AND, OR). For example, you can write the following condition: =IF(AND(A1<=5; A1>3); “passes”, “does not pass”). The following condition is obtained: if the student’s grade is less than or equal to 5 and more than 3, then the “pass” comment will be shown, otherwise the “fail” comment will be shown (only those students whose grades are fours and fives pass).

A more complex example of using the IF function - using AND or OR conditions

The following condition with an example of the OR operator: =IF(OR(A1=5; A1=10); 100; 0). It turns out that if the number in cell A1 is 5 or 10, then the program will output 100, otherwise - 0.

In addition, you can use these simple operators to solve more complex problems. For example, in the database it is necessary to select debtors who owe more than 10 thousand rubles and have not paid for more than 6 months. If the program finds such a person, then let it issue the “problem borrower” criterion.

Let's say cell A1 contains information about the duration of the debt (in months), and field B1 contains the amount of debt. Then the formula will look like this: =IF(AND(A1>=6; B1>10000); “problem borrower”; “”). If a person is found who meets the specified condition, the program will write the comment “problem borrower” next to his last name, otherwise the cell will remain empty.

If one of the parameters is considered critical, then you can create a formula like this: =IF(OR(A1>=6; B1>10000); “critical situation”; “”). If the program finds matches for at least one parameter (either the term or the amount of debt), the user will see a message stating that the situation is critical. The difference with the previous formula is that in the first case, the “problem borrower” message was issued only when both conditions were met.

Other examples of using the IF statement

Very often in Excel an error such as “DIV/0” occurs, i.e. division by 0. As a rule, it appears in those cases when the formula “A/B” is copied, and the number B in some cells is equal to zero. This can be avoided by using the IF statement. To do this, you need to write this: =IF(B1=0; 0; A1/B1). It turns out that if there is a zero in cell B1, then Excel will immediately display zero, otherwise the program will divide A1 by B1 and display the result.

Another situation that occurs quite often in practice is the calculation of a discount depending on the total purchase amount. To do this you will need something like this matrix:

up to 1000 - 0%;
from 1001 to 3000 - 3%;
from 3001 to 5000 - 5%;
over 5001 - 7%.

For example, Excel has a notional database of customers and information about how much they spent on purchases. The task is to calculate a discount for them. To do this, you can write this: =IF(A1>=5001; B1*0.93; IF(A1>=3001; B1*0.95;..). The essence is clear: the total amount of purchases is checked, and when it is for example, more than 5001 rubles, then multiplied by 93% of the cost of the product (cell B1*0.93), when more than 3001 rubles, then multiplied by 95% of the cost of the product, etc. can easily be used in practice: level of sales volume and The level of discounts is set at your discretion.

Thus, you can use the IF function in almost any situation, the functionality allows it. The main thing is to formulate the formula correctly so that the result does not turn out to be erroneous.

A common Excel question is “How to write multiple conditions in one formula?” It is especially common to use two or more conditions when using the IF function. Making several conditions in the IF formula is quite simple, the main thing is to know the basic principles. We discuss them below.

In my opinion, it is instructive to consider an example of solving a system of conditions. Such tasks are often given in institutes, in Excel classes.

For example, there is this rather cluttered formula:

Let's look at an example of how to transfer it to Excel

It is clear that this formula will consist of at least 3 parts:

SIN(B1)^2 =COS(B1) =EXP(1/B1)

But how can we write several of these functions into one, also by condition? To understand this, let's take a closer look at the IF function.

Its composition is as follows:

IF(Condition;if condition = YES (TRUE);if condition = NO (FALSE))

Those. if we write down a simple formula, what do we end up with in cell B2?

Correct - 100 will be displayed. If A1 contains any other value other than 1, then B2 will display 0.

Let's return to our system of conditions. Now we need to understand how to write two conditions at once before the first semicolon. We have empty B1, which means = 0, and only if both conditions A1=1 and B1=0 (sign *) are met will the value of the formula be equal to 100.

Let's take a closer look at the * between brackets

The And operator * means that both conditions must be met simultaneously, A1=1 and B1=0.

If you put + (or) between the brackets, then one of the conditions will be sufficient. For example, only if A1=1, then 100 will be displayed.

We are ready to write the formula, we will do it in parts

Let's write down the first condition

IF((B1>-2)*(B1<9);SIN(B1)^2);

If the condition is met, then the first formula with sine is executed
If not, the second condition

IF((B1>-2)*(B1<9);SIN(B1)^2;IF((B1>=9)*(B1<=19);COS(B1)

In all other cases, the formula =EXP(1/B1) will be satisfied
The total is:

IF((B1>-2)*(B1<9);SIN(B1)^2;ЕСЛИ((B1>=9)*(B1<=19);COS(B1);EXP(1/B1)))

Writing several formulas in one

If there is text in cells B1, the formula will generate an error. That's why I often use the formula.

Let's imagine that our entire formula from the previous paragraph is one conditional argument A

Then =IFERROR(A;"")

Or for our example

IFERROR(IF((B1>-2)*(B1<9);SIN(B1)^2;ЕСЛИ((B1>=9)*(B1<=19);COS(B1);EXP(1/B1)));"")

The example can be downloaded

General information about IF

The IF function is one of the most popular functions in Excel. In English Excel, as well as in Google Sheets, LibreOffice, OpenOffice, this function is called IF. IF (IF) refers to logical functions.

Difficulty level on the BRP ADVICE scale - 2 out of 7. Each nested IF doubles the complexity of the formula.

IF (IF) allows you to build a decision tree, that is, if a condition is met, perform one action, and if not fulfilled, another. In this case, the condition must be a question with answer options “yes / no” or “true / false” (in terms of Excel, Google Sheets, LibreOffice, OpenOffice this is “TRUE / FALSE”).

To understand the IF function, you first need to understand what logical functions are.

What are logical functions

In Excel, Google Sheets, LibreOffice, OpenOffice and other spreadsheet documents, the operation of logical functions is based on the existence of logical parameters. There are two logical parameters: the first is TRUE, the second is FALSE.

Based on the use of these logical parameters, a decision tree can be constructed. The simplest version of this tree will ask a question that can be answered TRUE or FALSE, and give instructions on what to do in each of these two cases. Such a decision tree is shown schematically in the figure below.

Drawing. The simplest decision tree

Logical functions allow you to either build such a decision tree, or ask a question and receive a logical parameter. The first include, for example, IF (IF), IFERROR. The second ones are ISNUMBER, AND (AND), OR (OR).

Excel, Google Sheets, LibreOffice, OpenOffice, and most other software allow you to use the logical parameters TRUE and FALSE when performing mathematical operations. Most often, TRUE takes the value 1, FALSE takes the value 0. Although sometimes TRUE and FALSE take other values, for example, when programming in VBA, TRUE is -1, and not 1.

By the way, logical parameters are also called Boolean parameters in honor of the English mathematician and logician George Boole.

IF function

So, the IF function allows you to build a decision tree. This decision tree has one input question and two options. The question necessarily has two possible answers: yes/no, true/false, or in terms of the logical parameters TRUE/FALSE.

A question and two options for action are the three arguments of the IF function.

The first argument of the IF function is a logical question. In Excel it is called "log_expression". Excel, Google Sheets, LibreOffice, OpenOffice automatically find the answer to this question, and this answer must be TRUE / FALSE. What can such an answer give? The simplest options are classical equalities and inequalities. For example, the expression 12=12 will return the logical parameter TRUE, and the inequality 12>40 will return the logical parameter FALSE.

In a logical question, you can use equalities (the left and right sides are compared using the “=” sign), inequalities (more - “>”, less - “<», больше или равно - «>=", less than or equal to "<=»), а также просто не равно - «<>».

More complex logical questions can be asked using nested functions. As a result of calculating such nested functions, the same logical parameter TRUE or FALSE should be obtained. Such functions include, for example, ISNUMBER, ISTEXT, ISNA, AND, OR, in complex cases - another IF.

The second and third arguments are the IF function to determine when the answer to the question is TRUE and when it is FALSE. The IF function evaluates either only the second argument (if TRUE) or only the third argument (if FALSE).

Let's look at examples of using the IF function with one or more conditions.

Using IF with one condition

You can download example file No. 1.

Suppose the company has a sales plan: each manager must sell at least 1 million rubles per month. The salary of a sales manager is 20 thousand rubles. When the plan is fulfilled, the manager receives a salary and a bonus of 5% of actual sales volume. If the sales plan is not met - only salary.

At the end of each month, a table is generated containing information about the sales of each manager. This table might look, for example, like the figure below.

Drawing. Sales by sales manager for the reporting month

Using the IF function, this table can quickly be transformed from a simple set of sales data for the month into a report that will show who met the plan, who did not, and what the salary of each manager will be. Such a report may look like the figure below.

Drawing. Report on the performance of sales managers

In order to automatically fill in the column “Plan implementation” and “Salary for the month, rub.” (columns E and F, respectively), you can use the IF function.

Example 1.1 - text substitution using IF

In the “Plan Execution” column in cell E4 we use the following formula:

IF(D4>=1000000;"Well done!";"Plan not fulfilled:(")

IF(D4>=1000000;"Well done!";"Plan not fulfilled:(") .

By the way, in some versions of Excel, instead of ";" "," must be used.

After this, the cell can be copied down to the end of the column, and the program will write in each line who did well and who did not fulfill the plan.

What do all the IF arguments mean?

1. Logical expression: D4>=1000000. In our example, the logical question is a comparison of the actual result and the sales plan. D4 is a link to the cell with the actual sales of this manager. Excel, Google Sheets, LibreOffice, OpenOffice substitute the value from this cell for D4 and check whether the specified inequality is true. As a result of checking the formula, an intermediate result is obtained; it is used to select the desired branch in the decision tree.

2. Value_if_true. In our diagrams, this is the left branch of the decision tree. In the current example, the value of the argument is "Well done!". This argument indicates what the IF function should do when the first argument evaluates to TRUE. In the current example, you just need to write the text “Well done!”

By the way, "Well done!" we have it written in quotes, because any text inside the formula must be written in quotes. The only exceptions are the names of functions and named ranges. Otherwise, always put the text in quotation marks.

3. Value_if_false. In our diagrams, this is the right branch of the decision tree. In the current example, the value of the argument is "Plan failed:(". This argument indicates what the IF function should do when the first argument evaluates to FALSE. In the current example, you simply write the text "Plan failed :(".

Here we also put the text in quotes because if you don't put quotes around the text inside a formula, the error #NAME? (#NAME?). The only exceptions are the names of functions and named ranges.

First, the IF function answers a logical question (evaluates the first argument). Secondly, it goes to the corresponding branch of the decision tree. According to Alexandrov P.F. it turns out like this:

1. D4>=1000000, therefore we check 1000329>

2. Go to the Value_if_true argument. You just need to insert the text “Well done!” Specify the text in the cell. End of calculations.

Drawing. How does the IF function work when a Boolean expression returns TRUE?

1. D5>=1000000, therefore we check 848880>

2. Go to the Value_if_false argument. You just need to insert the text “Plan not completed:(”. We indicate the text in the cell. End of calculations.

Schematically, the calculations look like the figure below.

Drawing. How does the IF function work when a Boolean expression returns FALSE?

Example 1.2 - calculating different formulas using IF

You can download example file No. 1.

IF(D4>=1000000;20000+D4*5/100;20000)

or for English Excel, Google Sheets, LibreOffice, OpenOffice:

IF(D4>=1000000;20000+D4*5/100;20000) .

After this, the cell can be copied down to the end of the column, and the program will write the salary of each manager in each line.

What exactly does the IF function do in this example?

The IF function answers the logical question (evaluates the first argument) and moves to the appropriate branch of the decision tree. According to Alexandrov P.F. it turns out like this:

1. D4>=1000000, therefore we check 1000329>=1000000, the expression is true, which means the logical parameter is TRUE.

2. Go to the Value_if_true argument. You need to calculate 20000+D4*5/100 (that is, the salary is 20 thousand and the same bonus is 5% of sales). We get 70016, indicate this value in the cell. End of calculations.

The Value_if_false argument is ignored in this case by the IF function.

According to Ilyin M.A. it turns out like this:

1. D5>=1000000, therefore we check 848880>=1000000, the expression is not true, which means the logical parameter is FALSE.

2. Go to the Value_if_false argument. You just need to put 20000. We indicate the number in the cell. End of calculations.

The argument Value_if_true in this case is ignored by the IF function.

As always, our exercises work in Excel 2007-2013, and the advanced functionality can be used in Excel 2010 and 2013. With it, you can start any exercise from the beginning with just one “Start over” button on the BRP ADVICE tab that appears in Excel when you open our exercises. Just don't forget to enable macros.

Using IF with Multiple Conditions

Example 2 - different conditions in a logical expression

You can download example file No. 2.

In the previous example, both managers and senior managers had the same sales plan for the month. Let's complicate the task: we will set an increased plan for senior managers - 1 million 200 thousand per month. The report will then look like the figure below.

In this case, in the “Plan Execution” column in cell E4, we use the following formula:

IF(IF(C4="Senior Manager";D4>=1200000;D4>=1000000);"Well done!";"Plan not fulfilled:(")

or for English Excel, Google Sheets, LibreOffice, OpenOffice:

IF(IF(C4="Senior Manager";D4>=1200000;D4>=1000000);"Well done!";"Plan not fulfilled:(") .

What exactly does the IF function do in this example?

According to Alexandrov P.F. it turns out like this:

2. The nested IF function checks the logical expression: the manager's position is senior manager or not. Alexandrov P.F. - this is not a senior manager. Therefore, the logical expression in the nested IF (IF) returns FALSE.

3. The nested IF function goes to the Value_if_false argument and compares actual sales to the sales manager's plan. 1,000,329 is greater than 1,000,000, so the nested IF returns the Boolean parameter TRUE.

According to Ilyin M.A. it turns out like this:

1. The IF function begins its calculation with a logical expression and sees a nested IF function there. Excel, Google Sheets, LibreOffice, OpenOffice first calculates the nested function.

2. The nested IF function checks the logical expression: the manager's position is senior manager or not. Ilyin M.A. - this is not a senior manager. Therefore, the logical expression in the nested IF (IF) returns FALSE.

3. The nested IF function goes to the Value_if_false argument and compares actual sales to the sales manager's plan. 848,880 is less than 1,000,000, so the nested IF returns the Boolean parameter FALSE.

See the constructed decision tree in the diagram below.

Drawing. Decision tree for an IF function with multiple conditions

According to Nezenetsev A.A. it turns out like this:

1. The IF function begins its calculation with a logical expression and sees a nested IF function there. Excel, Google Sheets, LibreOffice, OpenOffice first calculates the nested function.

2. The nested IF function checks the logical expression: the manager's position is senior manager or not. Nezenetsev A.A. - This is a senior manager. Therefore, the logical expression in the nested IF (IF) returns TRUE.

3. The nested IF function goes to Value_if_true and compares actual sales to the senior sales manager's plan. 1,204,346 is greater than 1,200,000, so the nested IF returns the Boolean parameter TRUE.

4. The result of the nested IF function calculation is passed to the main function. The main IF function sees the Boolean argument TRUE and goes to its (not its nested) argument Value_if_true. This argument is simply the text “Well done!”

A formula with multiple conditions, that is, with nested IF functions, returns the text “Well done!” in the cell.

See the constructed decision tree in the diagram below.

Drawing. Decision tree for an IF function with multiple conditions

According to Sokolova N.I. it turns out like this:

1. The IF function begins its calculation with a logical expression and sees a nested IF function there. Excel, Google Sheets, LibreOffice, OpenOffice first calculates the nested function.

2. The nested IF function checks the logical expression: the manager's position is senior manager or not. Sokolova N.I. - This is a senior manager. Therefore, the logical expression in the nested IF (IF) returns TRUE.

3. The nested IF function goes to Value_if_true and compares actual sales to the senior sales manager's plan. 1,046,625 is less than 1,200,000, so the nested IF returns the Boolean parameter FALSE.

4. The result of the nested IF function calculation is passed to the main function. The main IF function sees the Boolean parameter FALSE and passes to its (not its nested) argument Value_if_false. This argument is simply the text "Plan failed:(".

A formula with multiple conditions, that is, with nested IF (IF) functions, returns the text “Plan failed:(” in the cell.

See the constructed decision tree in the diagram below.

Drawing. Decision tree for an IF function with multiple conditions

Formula for calculating wages in example 3

In the column “Salary per month, rub.” in cell F4 we use this formula:

IF(IF(C4="Senior Manager";D4>=1200000;D4>=1000000);20000+D4*5/100;20000)

or for English Excel, Google Sheets, LibreOffice, OpenOffice:

IF(IF(C4="Senior Manager";D4>=1200000;D4>=1000000);20000+D4*5/100;20000) .

Don't forget, in some versions of Excel, instead of ";" "," must be used.

In this case, the IF function works exactly the same as in cell E4.

Example 4 - different conditions both in the logical expression and in the branches of the decision tree

You can download example file No. 3.

So we have managers, we have senior managers. Senior managers have a higher plan than regular managers. For this model to work, additional incentives for senior managers are often necessary. For example, the senior manager's bonus increases to 6%. That is, we have several conditions at once:

1. The bonus is paid only if the plan is fulfilled.

2. If the position is senior manager, the plan is 1 million 200 thousand, otherwise - 1 million.

3. If the position is senior manager, the bonus is 6%, otherwise - 5%.

The result is a report like the one below.

Drawing. Report on the performance of managers and senior managers

How to solve such a problem using the IF function?

In cell F4 you can write the following formula:

IF(C4="Senior Manager";D4>=1200000;D4>=1000000);

20000+D4*IF(C4="Senior Manager";6;5)/100;

or for English Excel, Google Sheets, LibreOffice, OpenOffice:

IF(C4="Senior Manager";D4>=1200000;D4>=1000000);

20000+D4*IF(C4="Senior Manager";6;5)/100;

Don't forget, in some versions of Excel, instead of ";" "," must be used.

The figure below schematically shows the constructed decision tree.

Drawing. Example of a decision tree with multiple conditions in both the logical expression and other arguments of the IF function

Common mistakes when working with the IF function

1. For the IF function, the first argument must always be specified - a logical expression and the second argument - the value if true. The third argument is optional. Users often forget to specify the third argument, especially when working with complex formulas, because of this, in some cases, instead of the desired result, a logical parameter FALSE appears in the cell.

2. Formula complexity increases very quickly when using nested IFs. Because of this, very often users forget to close the brackets of nested calculations and do not set the argument separator (“;” or “,”). Depending on the error, the formula either cannot be written into the cell or it is calculated incorrectly.

3. In complex formulas with IF (IF), it is very difficult to track the correctness of the calculations: each nested IF (IF) function adds one question and at least two branches to your decision tree. On average, a person holds up to 7 objects in his mind, it turns out that with three nested IFs (IF), he needs to keep 3 questions and 6 branches of the decision tree in his mind. The controllability and reliability of the formula is rapidly declining.

How to avoid these mistakes when working with the IF function? Minimize the use of IFs with other functions and especially with nested IFs. It is better to do intermediate calculations in adjacent cells.

Tip: Working with complex formulas

Often we have to work with complex formulas, formulas in which others are nested within one function. How not to make mistakes when creating such a formula? Follow the following algorithm:

1. Determine the ultimate goal of your calculations: what result you should get in the end.

2. Define a function that allows you to do this.

3. Start creating a formula with this function, specify it and move on to working with arguments.

5. If you need to do intermediate calculations, then determine the final goal of these calculations, the function, and so on. Typically, the task of intermediate computations is to obtain an argument for the main function. Keep this in mind, since sometimes you need to get an argument of a certain type (text, number, logical parameter, or something else).

6. Always watch the parentheses: Once you have finished describing the function, close the parentheses.

And remember, if the formula is too complex, it is better to do an intermediate calculation in the next cell.

How to supplement and replace the IF function

Instead of constants, you can use named ranges in a formula.

Solving a problem with multiple conditions can be greatly simplified by using nested AND (AND) and OR (OR) functions.

The IF function can sometimes be replaced by a VLOOKUP, HLOOKUP, LOOKUP, IFERROR, SUMIF, or COUNTIF function.

Example file No. 1 “Using the IF function with one condition” you can download .

Example file No. 2 "Using the IF function with multiple conditions".

Still have questions? Write to us in the feedback form and sign up for Excel intensive or Excel functions course.

Function IF one of the most popular and frequently used Excel functions. Using it together with comparison operators and other logical functions of Excel, 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 due to which the formula can make decisions. The condition is checked first and can return only two values - TRUE or FALSE. If the condition is true, then the formula will return the second argument, otherwise the third.

To learn how to set conditions in Excel, read the articles: How to set a simple logical condition in Excel and Using 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, 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 a variety of situations. Let's look at another example. The table below shows the results of recertification of the company's employees:

In column C we need to put the exam result, which should contain only two options: Passed or Did not pass. Those who scored more than 45 points passed the exam, the rest did not.

IF function and several conditions

Functions IF can be nested if you need to expand your decision making options in Excel. For example, for the previously discussed case of employee recertification, it is required to give not a result, but a rating from the series: Excellent, Good and Bad. Grade Great given when the number of points is more than 60, the score Fine for more than 45 and score Badly in other cases.

As you can see, instead of the second and third arguments of the function IF you can add new functions IF, thereby expanding the number of conditions that the formula can process. This way you can create the required number of attachments. True, there is an obvious drawback of this 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 VLOOKUP or VIEW.

So, in this lesson we looked at the logical function IF in all its glory and examples, and also analyzed 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!