Set Excel condition. How in excel using nested functions if () calculate the sales bonus

IF () function , english version IF (), used when checking conditions. For instance, \u003d IF (A1\u003e 100; "Budget exceeded"; "OK!")... Depending on the value in the cell A1 the result of the formula will be either "Budget Exceeded" or "OK!"

The IF () function is one of the most commonly used functions.

Function syntax

IF (boolean_test, value_if_true, [value_if_false])

Log_Expression - any value or expression that can be TRUE or FALSE.
\u003d IF (A1\u003e \u003d 100; "Budget exceeded"; "OK!")
Those. if in the cell A1 contains a value greater than or equal to 100, then the formula will return OK!,and if not, then Budget exceeded.

Formulas can be used as function arguments, for example:
\u003d IF (A1\u003e 100, SUM (B1: B10), SUM (C1: C10))
Those. if in the cell A1 contains a value\u003e 100, then summation is performed over the column B , and if less, then by column FROM .

Nested IF

In EXCEL 2007 as argument values value_if_true and value_if_false you can use up to 64 IF () functions nested inside each other to build more complex checks.
\u003d IF (A1\u003e \u003d 100; "Budget exceeded"; IF (A1\u003e \u003d 90; "Large project"; IF (A1\u003e \u003d 50; "Medium project"; "Small project")))

VIEW (A1; (0; 50; 90; 100); ("Small project"; "Medium project"; "Large project"; "Budget exceeded"))

VLOOKUP (A1; A3: B6; 2)

For the VLOOKUP () function, you need to create in the range A3: B6 value table:

If you want to display different text if it is in the cell A1 negative value, positive value, or 0, then you can write the following formula:

LOOKUP (A1; (- 1E + 307; 0; 1E-307); ("<0";"=0";">0"})

or, if you want to display formulas instead of text values, you can use references to a range of cells (containing formulas)

LOOKUP (A24; (- 1E + 307; 0; 1E-307); A27: A29) (see example file)

Omitted third argument [value_if_false]

The third argument of the function is optional; if you omit it, the function will return FALSE (if the condition is not met).
\u003d IF (A1\u003e 100; "Budget Exceeded")
If in a cell A1 contains the value 1, then the above formula will return FALSE.

Number is entered instead of TRUE or FALSE in the first argument

Because the value FALSE is equivalent to 0, then the formulas
\u003d IF (0, "Budget exceeded"; "OK!")
or (if in the cell A1 contains the value 0)
\u003d IF (A1, "Exceeded budget"; "OK!")
will return OK!

If in a cell A1 is any number other than 0, then the formula will return Budget exceeded... This approach is convenient when testing for the equality of a value to zero.

Linking the IF () function with other functions using conditions

EXCEL also contains other functions that you can use to analyze data using conditions. For example, the COUNTIF () function is used to count the number of occurrences of numbers in a range of cells, and the SUMIF () function is used to add values \u200b\u200bthat meet certain conditions.

The IF () function, alternatively, can also be used to count and add values \u200b\u200busing conditions. Illustrative examples are given below.

Let the data be in the range A6: A11 (see example file)


Let's calculate the sum of values \u200b\u200bthat are greater than 10 using the SUMIF () function by writing \u003d SUMIF (A6: A11, "\u003e 10")... A similar result (23) can be obtained using
\u003d SUM (IF (A6: A11\u003e 10, A6: A11))
(to enter a formula into a cell instead of ENTER need to press CTRL + SHIFT + ENTER)

Now let's count the number of occurrences of numbers greater than 10 in the range of cells A6: A11 \u003d COUNTIF (A6: A11, "\u003e 10")... A similar result (2) can be obtained using
\u003d COUNT (IF (A6: A11\u003e 10, A6: A11))

Now that the principle is clear, other formulas with conditions can be constructed using the IF () function. For example, finding the minimum value among numbers greater than 10:
\u003d MIN (IF (A6: A11\u003e 10, A6: A11))

The ability to perform logical checks on cells is a powerful tool. You will find endless uses for IF A() in your day to day documents. The problem is that often the necessary checks are beyond the capabilities of a simple function. IF A() ... This happens when it is necessary to check for two or more conditions before accepting the result.

For such cases, Excel provides several options: use IF A() inside another IF A(), functions AND() and IL AND()... We will look at these methods below.

Using IF A() inside another function IF A()

Let's consider a variant based on the previously studied function \u003d IF (A1\u003e 1000; "a lot"; "little"). What if you need to print another string when the number in A1 is, for example, greater than 10.000? In other words, if A1\u003e 1000 is true, you will want to run another test and see if A1\u003e 10000 is true. You can create such a variant by applying the second function IF A() inside the first as an argument, the value _if_true: \u003d IF (A1\u003e 1000; IF (A1\u003e 10000; "very much"; "many"); "little").

If A1\u003e 1000 is true, another function starts IF A(), which returns the value "very much" when A1\u003e 10000. If, however, A1 is less than or equal to 10,000, the value "many" is returned. If during the very first check the A1 number is less than 1000, the value "little" will be displayed.

Note that you can just as well run the second check if the first one is false (that is, in the value_if_false argument of the ifo function). Here is a small example that returns the value "very small" when the number in A1 is less than 100: \u003d IF (A1\u003e 1000; "many"; IF (A1<100;"очень мало"; "мало")) .

Sales Bonus Calculation

A good example of using one check inside another is to calculate the sales bonus for staff. who works in the Club - hotel Heliopark Thalasso, Zvenigorod. In this case, if the value is X, you want one result, if Y is another, if Z
- the third. For example, in the case of calculating a bonus for successful sales, three options are possible:

  1. The seller has not reached the planned value, the bonus is 0.
  2. The seller exceeded the planned value by less than 10%, the bonus is 1,000 rubles.
  3. The seller exceeded the planned value by more than 10%, the bonus is equal to 10,000 rubles.

Here is the formula for calculating such an example: \u003d IF (E3\u003e 0; IF (E3\u003e 0.1; 10000; 1000); 0). If the value in E3 is negative, then 0 is returned (no bonus). If the result is positive, it is checked whether it is more than 10%, and depending on this, 1,000 or 10,000 are issued. 4.17 shows an example of how the formula works.

AND () function

It is often necessary to check only for the fidelity of two conditions simultaneously. For example, a bonus is paid only when the sales of a certain product have exceeded the plan and also the total sales have exceeded the plan. If one of these conditions is not met (or both conditions), the premium is not paid. In Boolean logic, this is called a logical AND because both conditions must be true for the entire expression to be true.

In Excel, boolean expressions AND are processed using the function AND(): AND (boolean_value1; boolean_value2; ...). Each argument is a boolean value to test. You can enter as many arguments as you like.

Let's note how the function works:

  • If all expressions return TRUE (or any positive number), AND() returns TRUE.
  • If one or more arguments return FALSE (or 0), AND() returns FALSE.

Most often AND() applied inside a function IF A()... In this case, when all arguments are inside AND() will return TRUE, function IF A() will follow its branch value if true. If one or more of the expressions in AND() will return FALSE, the function IF A() will follow the branch value_if_false.

Here's a small example: \u003d IF (AND (C2\u003e 0; B2\u003e 0); 1000; "no bonus"). If the value in B2 is greater than zero and the value in C2 is greater than zero, the formula will return 1000, otherwise the line "no bonus" will be displayed.

Separating values \u200b\u200binto categories

A useful use of the and () function is to categorize by value. For example, you have a table with the results of a poll or vote, and you want to divide all votes into categories according to the following age ranges: 18-34.35-49, 50-64.65 or more. Assuming that the respondent's age is in cell B9, the following function arguments and () conduct a logical check for age belonging to the range: \u003d AND (B9\u003e \u003d 18; B9


If the person's answer is in cell C9, the following formula will output the person's voting result if the check is triggered for compliance with the age group 18-34: \u003d IF (AND (B9\u003e \u003d 18; B9

  • 35-49: \u003d IF (AND (B9\u003e \u003d 35; B9
  • 50-64: \u003d IF (AND (B9\u003e \u003d 50; B9
  • 65+: \u003d IF (B9\u003e \u003d 65; C9; "")

OR () function

There are situations when you only need a decision when one of the conditions is positive (TRUE). For example, you might decide to pay a bonus to employees when total sales are above target or when sales of an individual item are above forecast. In boolean logic this is called boolean OR.

Such conditions are checked in Excel using the function OR(): OR (boolean1, boolean2, ...). Each argument is a boolean value to test. You can enter as many arguments as you like. Result of work OR() depends on the following conditions:

  • If one or more arguments return TRUE (any positive number), OR() returns TRUE.
  • If all arguments return FALSE (zero), the result is OR() will be FALSE.

As well as AND(), most often the function OR() used inside check IF A()... In this case, when one of the arguments inside OR() will return TRUE, function IF A() will follow its own branch value_if_true. If all expressions in OR() will return FALSE, function IF A() will follow the branch value_if_false... Here's a small example: \u003d IF (OR (C2\u003e 0; B2\u003e 0); 1000; "no bonus").

If there is a positive number in one of the cells (C2 or B2), the function will return 1000. Only when both values \u200b\u200bare negative (or equal to zero), the function will return the string "no bonus".

This article will focus on the "If" function of the Excel spreadsheet editor. It deals with the logical capabilities of the application. This function can be attributed to the most common, which is often used during operation.


Key features

IN excel program the "If" formula allows various kinds of tasks that require you to compare certain values \u200b\u200band get a result. This solution makes it possible to use branching algorithms, create a decision tree.

Application examples

The function looks like: \u003d IF (task; true; false). The first part is a boolean expression. It can appear as a phrase or number. For example, "10" or "without VAT". This parameter must be filled in. True is the value displayed as a 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, you need to provide examples. Therefore, further it is worth starting to consider them. You need to enter the value 8 in cell C1. After that, in the field at the address D1, you must enter a formula of the form: \u003d IF (C1<10; 1; 2). В результате программа самостоятельно начинает сравнение параметров из клетки C1 со значением 10. Когда оно достигнет десяти, в поле по адресу D1 можно увидеть единица. В противном случае редактор отобразит 2.

Another example can be considered. There are several students and their grades that are earned for the exam. The data are as follows: 5, 4, as well as 3 and 2. According to the terms of the assignment, it is envisaged to create for each student a text comment “passed” or “did not pass”. Thus, if a student receives a grade of three or higher, they are considered to have passed the exam. If his grade is less than 3, the student has failed to do well. To solve such a problem, write the following formula: \u003d IF (C1<3; «не справился»; «сдал»).

The program starts comparing the results of each student. If the indicator is less than three, the inscription "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 enclosed in quotation marks. When using this function, you can use the following comparison operators:< >, =, >, <, >=, <=.

Examples using the conditions "OR", "AND"

It is necessary to further consider the logical possibilities of this application. 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 a student's grade is equal to or less than 5, but more than 3. In this case, a comment should be displayed: "passes" or "no". Thus, only those students who have earned fives and fours pass. To write this task in a spreadsheet editor, you should apply a special formula. It looks like this: \u003d IF (AND (A1<=5; A1>3); "Passes", "no").

If you consider a more complex example, you will need to use "OR" or "AND". Thus, you can familiarize yourself with applying a formula in Excel if there are multiple conditions in the job. For example: \u003d IF (OR (A1 \u003d 5; A1 \u003d 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 100. Otherwise, it is 0. It is possible to use these operators to find a solution to more complex problems.

For example, in the database you need to calculate the debtors who need to pay more than 10,000 rubles. You can set a 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, it is possible to automatically get the mark "problem client" near the corresponding names. Let's say cell A1 contains data that indicates the term of the debt (months). Field B1 contains the amount.

In this case, the formula is presented in the following form: \u003d IF (AND (A1\u003e \u003d 6; B1\u003e 10000); "problem client"; ""). Thus, if a person is identified that meets the specified conditions, the program displays a corresponding comment opposite his name. For other members of the list, 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 look like this: \u003d IF (OR (A1\u003e \u003d 6; B1\u003e 10000); "critical situation"; ""). However, if the program detects matches for at least one of the parameters, a corresponding note is displayed.

Challenging tasks

Excel's "If" function is used to avoid built-in division by zero errors. In addition, it is used in several 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 indicator B 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: \u003d IF (B1 \u003d 0; 0; A1 / B1). So, if cell B1 is filled with a value of "zero", the editor will display "0". Otherwise, the program will divide the A1 indicator by the B1 data and give the required result.

Discount

As practice shows, situations often arise, which are discussed below. You will need to calculate discounts based on the total amount spent on the purchase of a particular item. The matrix used is as follows: less than 1000 - 0%; 1001-3000 - 3%; 3001-5000 - 5%; more than 5001 - 7%. You can see the situation when Excel has a database of visitors and information about the amount spent on a purchase. Next, you should calculate the discount for each client. To do this, you need to apply the following expression: \u003d IF (A1\u003e \u003d 5001; B1 * 0.93; IF (A1\u003e \u003d 3001; B1 * 0.95; ..).

The system checks the total purchase amount. If it exceeds the value of 5001 rubles, multiplication is performed by 93 percent of the price of the item. If the mark of 3001 units is exceeded, a similar action occurs, but 95% is already taken into account.



We recommend reading