How to make an if function in excel. Sumif function in excel: examples of use

Testing the truth of conditions and logical comparisons between expressions, which are common to many problems. To create conditional formulas, you can use the " And", "," And " If ".

For example, the function If uses the following arguments.

Formula using the if function

Logical expression: the condition to be tested.

Value_if_true: Return value if the condition is true.

Value_if_false: Return value if the condition is false.

For more information about how to create formulas, see Create and delete formulas.

In this article

Creating a conditional formula that results in a Boolean value (true or false)

To accomplish this task, use the and functions and operators. or And Not

Example

Copying an example

Important:

Formulas in Group Formula dependencies click the button Show formulas.

For more information on how to use these features, see Features and Features rather than Features.

Creating a conditional formula that results in a different calculation or values ​​other than true or false

To accomplish this task, use functions and operators If, and and or, as shown in the following example.

Example

To make this example easier to understand, copy it onto a blank sheet of paper.

Copying an example

Highlight the example provided in this article.

Important: Do not highlight row or column headings.

Highlighting an example in help

Press CTRL+C.

In Excel, create a blank workbook or sheet.

Select cell A1 on the sheet and press CTRL+V.

Important: For the example to work correctly, it must be inserted into cell A1.

To switch between viewing the results and viewing the formulas that return those results, press CTRL+` (accent mark) or on the tab Formulas in Group Formula dependencies click the button Show formulas.

By copying the example onto a blank sheet, you can customize it to suit your needs.

For more information about using these functions, see the articles Function and Function.

Today we will talk about the “If” function of the Excel spreadsheet editor. It has to do with the logical capabilities of the application. It can be considered one of the most popular functions during work.

Key Features

In Excel, the “If” formula helps you perform various types of tasks when you need to compare certain data and get a result. This solution makes it possible to use branching algorithms and also create a decision tree.

Examples of using

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

Equality of parameters of two cells

To understand the capabilities of the “If” function in Excel, examples are simply necessary, and then we will move on to their consideration. Enter the indicator 8 in cell C1. Next, in the field at address D1, enter the following formula: =IF(C1<10; 1; 2). Таким образом, программа начнет из клетки C1 с параметром 10. Когда оно будет меньше десяти, в поле по адресу D1 появится единица. В обратном случае редактор укажет число 2. Рассмотрим еще одну задачу. Есть перечень студентов и их оценки, полученные по экзамену. Данные таковы: 5, 4, а также 3 и 2. Условие задачи предполагает создание для каждого из студентов текстового комментария «сдал» либо «не сдал». Другими словами, если учащийся получил оценку три и выше, он с экзаменом справился. Если его бал ниже 3 - студент не сдал. Чтобы решить эту задачу пишем такую формулу: =ЕСЛИ(C1<3; «не справился»; «сдал»). Excel начнет сравнивать результаты каждого из студентов. Когда показатель будет меньше трех (то есть двойка), в соответствующей клетке будет указан комментарий «не справился». Если оценка - 3 и выше, в необходимой графе появится отметка о том, что студент экзамен сдал. Текстовые комментарии необходимо всегда указывать в кавычках. Согласно подобному несложному принципу функционирует в Excel формула «Если». Во время ее использования, можно применять такие операторы сравнения: < >, =, >, <, >=, <=.

Examples using the conditions “OR”, “AND”

We continue to practice and master the logical capabilities of the application. Excel's "If" spreadsheet editor function can be combined with comparison operators. These include the following parameters: “OR”, “AND”. Let’s indicate the necessary condition in Excel: if the student’s grade is equal to or less than 5, but greater than 3. The comment “passes” should be displayed. Otherwise - “no”. So, only those students who received A's and B's pass. You can write a similar task in a spreadsheet editor using a special formula. It will look like: =IF(AND(A1<=5; A1>3); “passes”, “no”). A more complex example would be the use of “OR” or “AND”. So let's see how to apply a formula in Excel if there are several conditions in the problem. An example of such an expression: =IF(OR(A1=5; A1=10); 100; 0). It follows from this that if the indicator in cell A1 is 5 or 10, the program will display the result 100, otherwise - 0. You can use these operators to solve more complex problems. For example, in the database it is necessary to calculate debtors who need to pay more than 10,000 rubles. However, they did not repay the loan for more than six months. The “If” function of the Excel spreadsheet editor allows you to automatically receive a “problem client” mark next to the corresponding names. Suppose cell A1 contains data indicating the duration of the debt (months). Field B1 displays the amount. In this case, the formula will look like this: =IF(AND(A1>=6; B1>10000); “problem client”; “”). It follows that if a person is found who meets the specified conditions, the program will indicate the required comment next to his name. For all other participants in the list, a similar cell will remain empty. Let's consider an example for the case when the situation is critical. Let's enter the appropriate comment. As a result, the formula will have the following form: =IF(OR(A1>=6; B1>10000); “critical situation”; “”). In this case, if the program finds a match for at least one of the parameters (term, amount of debt), the user will see a corresponding note. In the first situation, the “problem client” message was issued only when both specified conditions were met.

High-level tasks

Excel's "If" spreadsheet editor function is used to work around built-in errors in as well as in several other cases. The first situation is designated by the editor as “DEL/0” and occurs quite often. As a rule, it occurs in cases where the “A/B” formula is to be copied, while the B indicator in individual cells is equal to zero. This can be avoided through the capabilities of the operator we are considering. So, the necessary formula will look like this: =IF(B1=0; 0; A1/B1). It follows that if cell B1 is filled with the “zero” parameter, the editor will display “0,” otherwise Excel will divide the A1 indicator by the B1 data and display the result.

Discount

In practice, a situation often occurs that will be discussed below. We are talking about calculating a discount based on the total amount of money spent on purchasing a certain product. The matrix used in this case can have the following form: less than 1000 - 0%; 1001-3000 - 3%; 3001-5000 - 5%; more than 5001 - 7%. Let's consider a situation where Excel contains a database of visitors, as well as information about the amount of money they spent on purchases. Now you need to calculate the discount for each client. For this purpose, we use the following expression: =IF(A1>=5001; B1*0.93; IF(A1>=3001; B1*0.95;..). The system checks the total amount of purchases. When it exceeds 5001 rubles, multiplication occurs by 93 percent of the cost of the product. If the mark of 3001 units is exceeded, a similar action takes place, but taking into account 95%. The above formula can easily be applied in practice. Sales volume and discount rates are set at the discretion of the user.

The described capabilities can be used to solve various types of problems. The main stage is to correctly compose the formula so as not to get an erroneous result. Now you know how to use the operator in Excel if there are several conditions in the problem.

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

Among the many functions that Microsoft Excel works with, we should highlight the function "IF". This is one of those operators that users resort to most often when performing tasks in a program. Let's figure out what this function is and how to work with it.

"IF" is a standard feature of Microsoft Excel. Its tasks include checking that a specific condition is met. When the condition is met (true), then one value is returned to the cell where this function is used, and if it is not met (false), another value is returned.

The syntax of this function is as follows: “IF(boolean expression; [function if true]; [function if false])”.

Example of using "IF"

Now let's look at specific examples where a formula with the operator is used "IF".

Example of a function with multiple conditions

To function "IF" You can also enter multiple conditions. In this situation, single statement nesting is used "IF" to another. When the condition is met, the specified result is displayed in the cell; if the condition is not met, then the displayed result depends on the second operator.

Example with two conditions fulfilled simultaneously

In function "IF" you can also use the operator "AND", which allows you to consider true only the fulfillment of two or more conditions at the same time.

An example of using the "OR" operator

In function "IF" the operator can also be used "OR". It implies that a value is true if at least one of several conditions is true.

As we can see, the function "IF" can be a good assistant for the user when working with data in Microsoft Excel. It allows you to display results that meet certain conditions.

This article will focus on the “If” function of the Excel spreadsheet editor. It is related to the logical capabilities of the application. This function can be considered the most common, which is often used during work.

Key Features

In Excel, the “If” formula allows for various types of tasks that require comparing certain values ​​and obtaining a 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 appear as a phrase or number. For example, “10” or “excluding VAT”. This parameter must be filled in. True is the value displayed as the result if the expression is true. False represents the data that will be returned when the task is incorrect.

Equality of parameters of two cells

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

We can consider another example. There are several students and their marks that are earned in the exam. The data is as follows: 5, 4, as well as 3 and 2. According to the terms of the task, it is necessary to create a text comment “passed” or “failed” for each student. Thus, if a student receives a grade of three or higher, he is considered to have passed the exam. If his score is below 3, the student failed the test. 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 message “failed” will appear in the corresponding cell. When the mark is 3 or higher, 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. The following comparison operators can be used when using this function:< >, =, >, <, >=, <=.

Examples using the conditions “OR”, “AND”

Further consideration of the logical capabilities of this application is necessary. It is possible to combine the If function with comparison operators. These are the following parameters:

"OR";
"AND".

It is necessary to indicate 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: “pass” or “fail”. Thus, only those students who earned A's and B's pass. To write this task in a spreadsheet editor, you must use a special formula. It has the following form: =IF(AND(A1<=5; A1>3); “passes”, “no”).

If we consider a more complex example, we will need to use “OR” or “AND”. Thus, you can familiarize yourself with the use of a formula in Excel if there are several conditions in a 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 as 100. Otherwise, it is 0. It is possible to apply these operators to find solutions to more complex problems.

For example, in the database you need 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, you can automatically get a “problem client” mark next to the corresponding names. Let's say cell A1 contains data that indicates the duration of the debt (months). Field B1 contains the amount.

In this case, the formula is presented in the following form: =IF(AND(A1>=6; B1>10000); “problem client”; “”). Thus, if a person is identified who meets the specified conditions, the program displays a corresponding comment next to his name. For other list members, 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 identifies a match based on at least one of the parameters, a corresponding note is displayed.

Complex tasks

Excel's "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 requires copying. At the same time, the B indicator 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 A1 indicator by the B1 data and produce the required result.

Discount

As practice shows, situations often arise that are discussed below. You will need to calculate discounts, taking into account the total amount of money spent on the purchase of a specific 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 get acquainted with the situation when Excel has a database of visitors and information about the amount spent on the purchase. Next, you should calculate the discount for each client. To do this, you will 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 5,001 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.