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 value from 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 student a text comment “Passed” or “Didn’t 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: <>, =,>, <,> =, <=.
Examples using the conditions "OR", "AND"
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 look like this: = 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.
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.
A discount
In practice, the situation is often encountered, which will be discussed later. We are talking about calculating discounts based on the total amount of funds spent on the purchase of a particular product. The matrix used in this case may have the following form: less than 1000 - 0%; 1001-3000 - 3%; 3001-5000 - 5%; more than 5001 - 7%. Consider the situation when Excel has 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 the figure of 5001 rubles, the product is multiplied by 93 percent of the value of the product. If the mark of 3001 is exceeded, a similar action takes place, but already taking into account 95%. The above formula can be easily applied in practice. Sales and discount rates are set at the user's discretion.
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.