🐷 🧔🏻 👨🏼‍⚕️ Excel: "If" (formula). In Excel, the If function 👩‍👦‍👦 🧜🏾 📫

Today we’ll talk about the Excel If function of the Excel spreadsheet editor. It relates to the logical capabilities of the application. It can be attributed to the most popular functions during operation.

Key features

In Excel, the “If” formula helps you perform various tasks when you need to compare certain data and get a result. This solution makes it possible to use branching algorithms, as well as 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 “without VAT” are logical expressions. This parameter must be filled. True is the value that will be displayed as the result if the expression is true. False - data that will be issued 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 consider them. We enter indicator C1 into cell C1. Next, enter the following formula in the field at address D1: = IF (C1 <10; 1; 2). Thus, the program will begin to compare the valuefrom cell C1 with parameter 10. When it is less than ten, one will appear in the field at address D1. In the opposite case, the editor will indicate the number 2. Consider another task. There is a list of students and their grades obtained from the exam. The data are as follows: 5, 4, as well as 3 and 2. The condition of the task involves creating for each of the students a text comment “passed” or “did not pass”. In other words, if a student has a grade of three or higher, he or she will succeed. If his score is below 3 - the student did not pass. To solve this problem, we write the following formula: = IF (C1 <3; “failed”; “passed”). Excel will begin to compare the results of each student. When the indicator is less than three (that is, two), the comment “failed” will be indicated in the corresponding cell. If the grade is 3 or higher, a mark will appear in the necessary column indicating that the student passed the exam.Text comments must always be quoted. According to this simple principle, the If formula works in Excel. During its use, the following comparison operators can be used: <>, =,>, <,> =, <=.

«», «»

We continue to practice and master the logical capabilities of the application. The Excel If function of the spreadsheet editor can be combined with comparison operators. These include the parameters: “OR”, “AND”. We indicate the necessary condition in Excel: if the student's grade is equal to or less than 5, but greater than 3. A comment should be displayed: “passes”. Otherwise, no. So, only those students who received fives and fours go through. You can record a similar task in a spreadsheet using a special formula. It will have the form: = IF (AND (A1 <= 5; A1> 3); “passes”, “no”). A more complex example can be attributed to the use of “OR” or “AND”. So let's see how to apply the formula in Excel, if there are several conditions in the task. An example of such an expression: = IF (OR (A1 = 5; A1 = 10); 100; 0). It follows 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 the debtors who need to pay more than 10,000 rubles. However, they did not repay the loan for more than six months. The function of the Excel spreadsheet editor “If” allows you to automatically get the “problem client” mark opposite the corresponding names. Suppose, in cell A1, there are data indicating the debt period (months). Field B1 displays the amount. In this case, the formula will have the following form: = IF (AND (A1> = 6; B1> 10000); “problem customer”; “”). It follows that if a person is found who meets the specified conditions, the program will indicate the required comment in front of his name.For all other participants in the list, a similar box will remain empty. Consider an example for the case when the situation is critical. Let us introduce the corresponding comment. As a result, the formula will look like this: = IF (OR (A1> = 6; B1> 10000); “critical situation”; “”). In this case, if the program detects matches in at least one of the parameters (term, debt amount), the user will see a corresponding note. In the first situation, the message “problem customer” was issued only when both specified conditions were met.In this case, if the program detects matches in at least one of the parameters (term, debt amount), the user will see a corresponding note. In the first situation, the message “problem customer” was issued only when both specified conditions were met.In this case, if the program detects matches in at least one of the parameters (term, debt amount), the user will see a corresponding note. In the first situation, the message “problem customer” was issued only when both specified conditions were met.

High difficulty tasks

The Excel If function of the spreadsheet editor is used to bypass the built-in errors when dividing by zero, as well as in several other cases. The first situation is indicated by the editor as “DEL / 0” and is encountered quite often. As a rule, it arises in cases where the formula “A / B” is to be copied, while the indicator B 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 have the following form: = IF (B1 = 0; 0; A1 / B1). It follows that if cell B1 is filled with the parameter “zero”, the editor will give “0”, otherwise Excel will divide the indicator A1 into the data B1 and display the result.

Discount

, . , , . : 1000 — 0%; 1001-3000 — 3%; 3001-5000 — 5%; 5001 — 7%. , Excel , . . : =(A1>=5001; B1*0,93; (1>=3001; B1*0,95;..). . 5001 , 93 . 3001 , , 95%. . .

The application of the described capabilities is possible for solving various kinds of tasks. The main stage is the correct preparation of 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 task.