Today we will talk about the function of the Excel spreadsheet editor "If". It has to do with the logical capabilities of the application. It can be attributed to the most demanded functions during work.
Key Features
In Excel, the If formula helps you perform differentsort of task, when you want to compare certain data and get the result. This solution makes it possible to apply branching algorithms, as well as create a decision tree.
Examples of using
The function looks like this: = IF (task, truth, lie). 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 in. Truth is a value that will be displayed as a result if the expression is true. Lies are data that will be issued when the task is not correct.
Equality of the parameters of two cells
To understand the possibilities of the "If" function inExcel, the examples are simply necessary, and then we proceed to consider them. Enter the number 8 in cell C. Next, in the field at address D1, enter the following formula: = IF (C1 <10; 1; 2). Thus, the program will begin to compare the value from cell C1 to 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 problem. There is a list of students and their grades received on the exam. The data are as follows: 5, 4, and also 3 and 2. The condition of the task involves creating a text comment for each student "passed" or "failed." In other words, if the student received a grade of three or more, he coped with the exam. If his ball is lower than 3 - the student did not pass. To solve this problem, we write the following formula: = IF (C1 <3, "failed", "passed"). Excel will compare the results of each student. When the indicator is less than three (i.e., a deuce), the corresponding cell will contain a comment "failed". If the score is 3 or higher, a note will be placed in the necessary box indicating that the student passed the exam. Text comments should always be quoted. According to this simple principle, the formula "If" functions in Excel. During its use, you can use such comparison operators: <>, =,>, <,> =, <=.
Examples with the application of the conditions "OR", "AND"
We continue to practice and master the logicalapplication capabilities. The function of the Excel spreadsheet editor "If" can be combined with comparison operators. These include the parameters: "OR", "AND". We indicate the necessary condition in Excel: if the student's score is equal to or less than 5, but more than 3. A comment should be displayed: "passes". Otherwise, "no." So, only those students who have received fives and fours pass. You can write a similar task in a spreadsheet using a special formula. It will look like: = IF (AND (A1 <= 5; A1> 3); "goes through", "no"). To a more complex example, we can refer 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, in the opposite case it will be 0. You can use these operators to solve more complicated problems. For example, in the database you need 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 function of the Excel spreadsheet editor "If" allows you to automatically get the "problem client" mark in front of the corresponding names. Suppose cell A1 contains data indicating the period of the debt (months). Field B1 displays the amount. In this case, the formula will have the following form: = 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 in front of his name. For all other participants in the list, a similar cell will remain empty. Let us 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 coincidences on at least one of the parameters (term, amount of debt), the user will see a corresponding note. In the first situation, the message "problem client" was issued only when both conditions were met.
Tasks of a high level of complexity
Excel table editor function "If"It is used to bypass the built-in errors when dividing by zero, and also in a few more cases. The first situation is indicated by the editor as "DEL / 0" and is encountered quite often. As a rule, it arises when the formula "A / B" is to be copied, while the indicator B in individual cells is zero. This can be avoided by means of 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 the cell B1 is filled with the parameter "zero", the editor will return "0", otherwise Excel will divide the A1 indicator by the data of B1 and display the result.
A discount
In practice, there is often a situation thatwill be discussed later. It's about calculating discounts, based on the total amount spent on the purchase of 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%. Consider the situation when in Excel there is a database of visitors, as well as information on the amount spent by them on the purchase of funds. Now you need to calculate the discount for each customer. 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 purchase amount when it exceeds the 5001 rubles, there is a multiplication of 93 percent of the value of the goods.If the level of 3001 units is overcome, a similar action takes place, but taking into account 95% .The formula can be easily applied in practice.The sales volume and discount rates are set at the discretion of the user.
The application of the described possibilities is possible forsolutions of various kinds of problems. The main stage - the correct formulation of the formula, so as not to receive an erroneous result. Now you know how to use an operator in Excel if there are several conditions in the task.
