excel functions if (if) and ifmuln (ifs) for multiple conditions. Using if with functions and, or and not In excel greater than or equal to

Function IF (IF) in Excel is a great tool for testing conditions for TRUE or FALSE. If the values ​​of your calculations are equal to the given function parameters as TRUE, then it returns one value, if FALSE, then another.

What does the function return

The value you specify when both conditions are TRUE or FALSE.

Syntax

=IF(logical_test, , )- English version

=IF(logical_expression, [value_if_true], [value_if_false]) - Russian version

Function Arguments

• logical_test (logical_test) is the condition you want to test. This function argument must be logical and defined as FALSE or TRUE. An argument can be either a static value or the result of a function, calculation;
• ([value_if_true])– (optional) is the value that the function returns. It will be displayed if the value you are testing is TRUE;
• ([value_if_false])– (optional) is the value that the function returns. It will be displayed if the condition you are testing is FALSE.

Additional Information

Function If in Excel examples with multiple conditions

Example 1: Checking a simple numeric condition using the IF (IF) function

When using the function IF (IF) in Excel, you can use different operators to check the status. Here is a list of operators you can use:

Below is a simple example of using the function when calculating student grades. If the total score is greater than or equal to “35”, then the formula returns “Passed”, otherwise it returns “Failed”.

Example 2: Using a nested IF (IF) function to test the condition of an expression

The function can accept up to 64 conditions at the same time. Although it's not practical to create long nested functions, in rare cases you can create a formula that has multiple conditions in series.

In the example below, we are checking two conditions.

• The first condition checks if the sum of points is not less than 35 points. If it is TRUE, then the function will return “Failed”;
• If the first condition is FALSE and the total score is greater than 35, then the function checks the second condition. If the sum of points is greater than or equal to 75. If this is true, then the function returns the value “Excellent”, in other cases the function returns “Passed”.
  

Example 3. Calculate the amount of commission on sales using the IF (IF) function in Excel

The function allows you to perform calculations with numbers. A good use case is calculating a sales commission for a sales representative.

In the example below, the Sales Representative:

• does not receive commissions if the sales volume is less than 50 thousand;
• receives a 2% commission if sales are between 50-100k
• receives a 4% commission if sales exceed 100,000 units.

=IF(B2<50,0,IF(B2<100,B2*2%,B2*4%)) - English version

=IF(B2<50;0;ЕСЛИ(B2<100;B2*2%;B2*4%)) - Russian version

In the formula used in the example above, the calculation of the commission amount is performed in the function itself IF. If the sales volume is between 50-100K, then the formula returns B2 * 2%, which is a 2% commission based on the sales volume.

Example 4. Using logical operators (AND / OR) (AND / OR) in the IF (IF) function in Excel

You can use logical operators (AND/OR) (AND/OR) inside a function to test multiple conditions at the same time.

For example, let's say you have to select students for scholarships based on grades and attendance. In the example below, a student is only eligible if they score over 80 points and have an attendance rate of over 80%.

You can use function along with function IF (IF) to first check if both of these conditions are met or not. If the conditions are met, the function returns “Eligible”, otherwise it returns “Not eligible”.

The formula for this calculation is:

=IF(AND(B2>80,C2>80%),”Yes”,”No”)- English version

=IF(AND(B2>80;C2>80%);”Yes”;”No”) - Russian version

Example 5: Converting Errors to “0” Values ​​Using the IF (IF) Function

With this function, you can also remove cells containing errors. You can convert error values ​​to spaces or zeros or any other value.

The formula for converting cell errors is as follows:

=IF(ISERROR(A1),0,A1)- English version

IF(ISERROR(A1),0,A1)- Russian version

The formula returns “0” if there is an error in the cell, otherwise it returns the value of the cell.

NOTE. If you are using Excel 2007 or later, you can also use the IFERROR function to do this.

Similarly, you can handle empty cells. In case of empty cells, use the ISBLANK function, in the example below:

=IF(ISBLANK(A1),0,A1)- English version

=IF(ISBLANK(A1),0,A1)- Russian version

One of the most popular features in the Excel application is IF. It's a boolean comparison value and result. To put it simply, the function might sound like this:

IF the condition is true, then we do it, otherwise we do something else

Syntax this function is quite simple:

IF (boolean_condition; value_in_case_TRUE; value_in_case_FALSE)

Let's take a closer look:

• boolean_condition- the value to be checked
• Value_in_case_TRUE- action when the requirement is fulfilled
• Value in case_FALSE– action when Boolean_condition is not met

How to use the function

Example #1

The screenshots below show the simplest use case. We first checking given condition A1>25. IF this is a requirement performed, then output to the cell " over 25", otherwise " less than or equal to 25».

Example #2

Now for a more difficult task. Below we will consider an example of recertification of employees of an enterprise. Initially, the table looks like this:

US necessary in column C, display the recertification results, which can take a binary value: PASSED or DID NOT PASS. Our criteria will be as follows: who scored over 45 points, that is considered passed exam, well, everyone else is not.

To complete a task necessary:

We use several conditions

If necessary, the function IF can invest into each other. Thus, we will expand the options for solutions.

For example, let's take the recertification of employees, which was discussed earlier. Change the result criteria and give each one a rating: Bad, Good and Excellent. Fine we will bet when the points exceed 60. Estimate Ok can be obtained by typing from 45 to 60 points. Well, in other cases we put Badly.

As you can see from the example, instead of the second and third values ​​of the function, you can substitute a condition. In this way, we add the required number of attachments. but it is worth noting that after adding 3-5 attachments, it will become almost impossible to work with the formula, because it will be very bulky.

Other Uses for the Function

Finding the sum of columns or cells

For example, there is a need to sum every second cell of a column. In this case, using the operator will help SUMIF. It will allow you to work only with those columns that fall under our requirements. We will consider the case when it is necessary to sum elements in odd rows.

To solve the problem, we need insert additional column and number rows 1 and 0. It is these data that we will use in our formula. The example shows which formula we used.

Syntax: SUMIF(cell_range, condition_used, [sum_range])

• Range is a required argument. Here the range is set, which is evaluated for compliance with the condition.
• Used_condition- here we indicate which cells need to be summarized.
• sum_range- specify which range to use for summation.

Number of repetitions of elements in a worksheet

In this case, we can count how many times a given element occurs on a sheet. For this we use the function COUNTIF. She counts the number of cells that match with a given value.

In this example, we count how many customers are in each city. In the formula, we set the first range, and the second the values ​​​​to be searched for.

Using COUNTIF and SUMIF to Count Data

In the example, we will try to define average income from customers in each city. To do this, we divide the total income of the city by the number of customers.

Finding multiple occurrences of an element in a list

If we have a constantly updated list of data, in which, as it grows, duplicates, then finding occurrences in this list can be difficult. The easiest way to solve this problem is to use the function VLOOKUP And COUNTIF.

To start, add an optional column that appends the sequence number of the occurrence to the customer's name. As a result, the first occurrence of the element will return Name1, the next Name2, and so on.

We use Search by client Christina Aguilera. Using the formula VLOOKUP("Christina Aguilera3", search_range, 2, FALSE), we get the third number of this customer. In the formula, we set the last value to FALSE, because the list is not sorted, and we need an exact match of the elements.

How to reduce the number of investments IF

Versions up to and including EXCEL 2003 supported up to 7 levels nesting IF. Starting with Excel 2007, this limitation removed. But it is worth noting that few people fall below 3-4 levels of nesting.

To reduce the number of IF nestings, you can use the function CHOICE. It operates on values ​​or actions from a given list by index number.

Syntax: SELECT(index_number; index_value1; index_value2; [index_value3];…)

For example, the function SELECT(1; "Third", "Second", "First"), will return the word " The third” if we add it to the cell.

It should be noted that there are some limitations. In particular, the index can be only numbers.

Boolean function IF in Excel is one of the most popular. It returns a result (value or other formula) depending on the condition.

IF function in Excel

The function has the following syntax.

IF( log_expression; value_if_true; [value_if_false])

log_expression is the test condition. For example, A2<100. Если значение в ячейке A2 действительно меньше 100, то в памяти эксель формируется ответ ИСТИНА и функция возвращает то, что указано в следующем поле. Если это не так, в памяти формируется ответ ЛОЖЬ и возвращается значение из последнего поля.

value_if_true – value or formula that is returned when the event specified in the first parameter occurs.

value_if_false is an alternative value or formula that is returned if the condition is not met. This field is optional. In this case, when an alternative event occurs, the function will return FALSE.

A very simple example. You need to check if the sales of individual products exceed 30 pcs. or not. If they exceed, then the formula should return "OK", otherwise - "Delete". The calculation with the result is shown below.

Sales of the first item are 75, i.e. the condition that it is greater than 30 is met. Therefore, the function returns what is indicated in the next field - "OK". Sales of the second item are less than 30, so the condition (>30) is not met and the alternative value specified in the third field is returned. This is the whole point of the IF function. Stretching the calculation down, we get the result for each product.

However, this was a demo. More often, the Excel IF formula is used for more complex checks. Let's say there are average weekly sales of goods and their balances at the moment. The buyer needs to make a forecast of the balance in 2 weeks. To do this, you need to subtract twice the average weekly sales from current stocks.

So far, everything is logical, but the cons are embarrassing. Are there negative balances? Of course not. Stocks cannot be below zero. For the forecast to be correct, you need to replace negative values ​​with zeros. This is where the IF formula comes in handy. It will check the value obtained according to the forecast, and if it turns out to be less than zero, it will forcibly give the answer 0, otherwise it will return the result of the calculation, i.e. some positive number. In general, the same logic, only instead of values ​​we use the formula as a condition.

There are no more negative values ​​in the forecast of stocks, which is generally very good.

Excel IF formulas are also actively used in array formulas. Here we will not delve too far. I recommend that those interested read the article about. True, the calculation in that article is no longer relevant, because. Excel 2016 introduces the MINESLI and MAXESLI functions. But for example, it is very useful to get acquainted - it will come in handy in another situation.

Excel IF Formula - Examples of Multiple Conditions

Quite often, the number of possible conditions is not 2 (checked and alternative), but 3, 4 or more. In this case, you can also use the IF function, but now you have to nest it into each other, specifying all the conditions in turn. Consider the following example.

Several sales managers need to accrue a bonus depending on the implementation of the sales plan. The motivation system is as follows. If the plan is fulfilled by less than 90%, then no bonus is due, if from 90% to 95% - a 10% bonus, from 95% to 100% - a 20% bonus, and if the plan is overfulfilled, then 30%. As you can see there are 4 options. To specify them in one formula, the following logical structure is required. If the first condition is met, then the first option occurs, otherwise, if the second condition is met, then the second option occurs, otherwise, if ... etc. The number of conditions can be quite large. At the end of the formula, the last alternative is indicated, for which none of the conditions listed above is satisfied (like the third field in the usual IF formula). As a result, the formula has the following form.

The combination of IF functions works in such a way that if any specified condition is met, the following ones are no longer checked. Therefore, it is important to list them in the correct order. If we started checking from B2<1, то условия B2<0,9 и B2<0,95 Excel бы просто «не заметил», т.к. они входят в интервал B2<1 который проверился бы первым (если значение менее 0,9, само собой, оно также меньше и 1). И тогда у нас получилось бы только два возможных варианта: менее 1 и альтернативное, т.е. 1 и более.

It's easy to get confused when writing a formula, so it's a good idea to look at the tooltip.

At the end, you must close all the brackets, otherwise Excel will give an error

The Excel IF function generally does its job well. But the option when you need to write down a long chain of conditions is not very pleasant, because, firstly, it’s not always possible to write the first time (you specify the condition incorrectly, then you won’t close the bracket); secondly, it can be difficult to figure out such a formula if necessary, especially when there are many conditions, and the calculations themselves are complex.

MS Excel 2016 introduces a function ESLIM for which this entire article was written. This is the same IF, only sharpened specifically to check for many conditions. Now you do not need to write IF a hundred times and count open brackets. It is enough to list the conditions and close one parenthesis at the end.

Works as follows. Let's take the example above and use the new Excel formula IIFS.

As you can see, writing the formula looks much simpler and clearer.

It is worth paying attention to the following. We still list the conditions in the correct order so that unnecessary range overlap does not occur. The last alternative condition, unlike the usual IF, must also be specified. IF specifies only an alternative value that occurs if none of the listed conditions is met. Here you also need to specify the condition itself, which in our case would be B2>=1. However, this can be avoided by writing TRUE in the condition field, indicating that if the previously listed conditions are not met, TRUE occurs and the last alternative value is returned.

Excel for Office 365 Excel for Office 365 for Mac Excel for the web Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 Excel Web App Excel 2007 for Developers Excel 2010 for Developers Excel 2013 for Developers Excel for Windows Phone 10 Excel Starter 2010 Less

The IF function allows you to perform logical comparisons of values ​​and expected results. It checks the condition and returns the result depending on whether it is true.

IF(this is true, then do this, otherwise do something else)

But what if you need to check multiple conditions, where, say, all conditions must be TRUE or FALSE ( AND), only one condition should have such a value ( OR) or you want to make sure the data NOT match the condition? These three functions can be used on their own, but are much more common in combination with the IF function.

Technical details

Use the IF function along with the AND, OR, and NOT functions to evaluate multiple conditions.

Syntax

IF(AND()): IF(AND(log_expression1, [log_expression2], ...), value_if_true; [value_if_false]))

IF(OR()): IF(OR(log_exp1, [log_exp2], ...), value_if_true, [value_if_false]))

IF(NOT()): IF(NOT(logical_expression1), value_if_true; [value_if_false]))

For an overview of how to use these functions individually, see the following articles: , OR , NOT . When combined with the IF operator, they are decoded as follows:

AND:=IF(AND(condition; other condition); value if TRUE; value if FALSE)

OR:=IF(OR(condition; other condition); value if TRUE; value if FALSE)

NOT:=IF(NOT(condition); value if TRUE; value if FALSE)

Examples

The following are examples of common use cases for nested IF(AND()), IF(OR()), and IF(NOT()) statements. The AND and OR functions support up to 255 individual conditions, but only a few conditions are recommended because highly nested formulas are difficult to create, test, and modify. A NOT function can only have one condition.

Note that all of the examples have a closing parenthesis after the conditions. The arguments TRUE and FALSE refer to the outer IF statement. Also, you can use text or numeric values ​​instead of the TRUE and FALSE values ​​returned in the examples.

Here are some examples of using the AND, OR, and NOT operators to evaluate dates.

Below are the formulas with a decoding of their logic.

Using AND, OR, and NOT Operators with Conditional Formatting

You can also use AND, OR, and NOT operators in conditional formatting formulas. However, you can omit the IF function.

On the tab home select Conditional Formatting > Create Rule. Then choose an option Use a formula to determine which cells to format, enter the formula, and apply the format.

Here's what the formulas for the date examples would look like:

Note: The most common mistake is to enter a formula in conditional formatting without an equal sign (=). If you do this, you will see that the Conditional Formatting dialog adds an equal sign and quotes to the formula = = "or (a4>B2; a4 , so the quotes must be removed before the formula reacts correctly.

additional information

You can always ask the Excel Tech Community a question, ask for help in the Answers community, or suggest a new feature or improvement on the website

The article will focus on the function of the spreadsheet editor Excel "If". It is related to the logical capabilities of the application. This function can be attributed to the most common, which is often used during operation.

Key features

In Excel, the "If" formula allows for various kinds of tasks that require you to compare certain values ​​​​and get the result. This solution makes it possible to use branching algorithms and create a decision tree.

Application examples

The function looks like: =IF(task; true; false). The first component is a logical expression. It can act as a phrase or a number. For example, "10" or "without VAT". This parameter must be filled in. True is the value displayed as the result if the expression is true. False is the data that will be returned when the task is false.

Equality of parameters of two cells

To better understand the possibilities of the "If" function in Excel, it is necessary to give examples. Therefore, it is worth starting to consider them further. You need to enter the value 8 in the cell C1. After that, in the field at the address D1 you need to enter a formula like: =IF(C1<10; 1; 2). В результате программа самостоятельно начинает сравнение параметров из клетки C1 со значением 10. Когда оно достигнет десяти, в поле по адресу D1 можно увидеть единица. В противном случае редактор отобразит 2.

One more example can be considered. There are several students and their marks that are earned for the exam. The data are as follows: 5, 4, as well as 3 and 2. According to the condition of the task, it is provided for the creation of a text comment “passed” or “failed” for each student. Thus, if a student gets a mark of three or more, he is considered to have passed the exam. If his score is below 3, the student has not coped with the task. To solve such a problem, you should write the following formula: =IF(C1<3; «не справился»; «сдал»).

The program begins to compare the results of each student. If the indicator is less than three, the inscription "failed" will appear in the corresponding cell. When the mark is 3 and above, in the required column you can see a comment that the student did not pass the exam. It is worth noting that text comments should always be written in quotation marks. When using this function, you can use the following comparison operators:< >, =, >, <, >=, <=.

Examples using "OR", "AND" conditions

It is necessary to further consider the logical possibilities of this application. It is possible to connect the "If" function with comparison operators. These are the following options:

"OR";
"AND".

It is necessary to specify an important condition in Excel: when the student's grade is equal to or less than 5, but greater than 3. In this case, a comment should be displayed: "passes" or "no". Thus, only those students who have earned fives and fours pass. To record this task in a spreadsheet editor, you should apply a special formula. It looks like this: =IF(AND(A1<=5; A1>3); "pass", "no").

If we consider a more complex example, you will need to use "OR" or "AND". Thus, you can familiarize yourself with the application of the formula in Excel if there are several conditions in the task. For example: =IF(OR(A1=5; A1=10); 100; 0). In this case, we can conclude that when the value in cell A1 is 5 or 10, the program displays the result 100. Otherwise, it is 0. It is possible to apply these operators to find a solution to more complex problems.

For example, in the database it is required to calculate debtors who need to pay more than 10,000 rubles. You can set the condition that the loan has not been repaid for more than six months, that is, six months. Thanks to the "If" function of the Excel spreadsheet editor, it is possible to automatically get the "problem client" mark next to the corresponding names. Let's say cell A1 contains data that indicates the period of debt (months). Field B1 contains the amount.

In this case, the formula is presented as follows: =IF(AND(A1>=6; B1>10000); "problem client", ""). Thus, if a person is identified that meets the specified conditions, the program displays the corresponding comment opposite his name. For other members of the list, this cell will remain empty.

You can also see an example when the situation is critical. An appropriate comment must be entered. As a result, the formula will be as follows: =IF(OR(A1>=6; B1>10000); "critical situation"; ""). However, if the program matches at least one of the parameters, a corresponding note is displayed.

Difficult tasks

The Excel "If" function is used to avoid built-in errors when dividing by zero. In addition, it is used in some other situations. The first case is designated as "DIV/0". It can be found quite often. This usually happens when the "A/B" formula needs to be copied. At the same time, the indicator B in individual cells is 0. To avoid such a situation, it is worth using the capabilities of the operator in question. Thus, the required formula is as follows: =IF(B1=0, 0, A1/B1). So, if cell B1 is filled with the value "zero", the editor will display "0". Otherwise, the program will divide the indicator A1 by the data B1 and give the required result.

A discount

As practice shows, situations often arise, which are discussed below. You will need to calculate discounts, taking into account the total amount of funds spent on the purchase of a particular product. The matrix that is used is as follows: less than 1000 - 0%; 1001-3000 - 3%; 3001-5000 - 5%; more than 5001 - 7%. You can familiarize yourself with the situation where Excel has a database of visitors and information about the amount spent on a purchase. The next step is to calculate the discount for each customer. To do this, you need to apply the following expression: =IF(A1>=5001; B1*0.93; IF(A1>=3001; B1*0.95;..).

The system checks the total amount of purchases. If it exceeds the value of 5001 rubles, the product price is multiplied by 93 percent. If the mark of 3001 units is exceeded, a similar action occurs, but 95% is already taken into account.

We recommend reading

Adviсe

How to check the keyboard using online services - an overview of solutions

Windows 10

Testing flash drives: how to find out the real speed and volume How to determine which usb port 3

Internet

usb 3 0 on my computer

Security

Testing flash drives: how to find out the real speed and volume Determine which usb 2

Programs

Color in styles can be set in different ways: by hexadecimal value, by name, in RGB, RGBA, HSL, HSLA RGB co

Gadgets

Parsing users who have recently joined VKontakte groups