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 accomplish all sorts of tasks when you need to compare certain data and get results. 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, 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 in. Truth is a value that is 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 functionality of the "If" function in Excel, examples are just necessary, and then we'll go over them. Enter the indicator 8 into the cell C. Next, in the field at the 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 below 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 (that is, a deuce), the corresponding cell will contain a comment "failed". If the score is 3 or higher, a note will be added in the required 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 logical capabilities of the application. The function of the Excel spreadsheet "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); "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). From this it follows that if the indicator in cell A1 is equal to 5 or 10, the program will display the result 100, in the opposite case - 0. You can use these operators and solve more complicated problems. For example, in the database you need to calculate debtors who need to pay more than 10,000 rubles. At the same time, they did not repay the loan for more than six months. The function of the Excel spreadsheet editor "If" allows in automatic mode to get the mark "problem client" 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 who meets the specified conditions is found, 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 coincidence for 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 given only when both conditions were met.
Problems of a high level of complexity
The Excel spreadsheet editor function "If" 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 often encountered. As a rule, it occurs 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", in the opposite case Excel will divide A1 by B1 and display the result.
A discount
In practice, there is often a situation that will 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. To this end, 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 cost 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 to solve various tasks. 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.