Logical functions in excel with examples of their use

08.08.20193436

Excel is an excellent editor that allows you to solve many problems quickly and easily. It’s easy to calculate percentages, conduct correlation analysis, build graphs, etc. The functionality of the program is enormous, so not everyone immediately understands which operator needs to be used to cope with a specific problem. Let's look at examples of how to work with the IF formula with several conditions.

IF function

There are two ways to write a formula in Excel - manually, by entering data in the function line or directly into a cell, and by accessing the menu. Let's try to figure out how to use the IF function using an example. The algorithm of actions is quite simple - a certain condition and options are indicated for what should be done in the case of truth or false (that is, the condition is either met or not):

• Open Excel by finding the program in the Start menu or on the desktop.

• We enter the data with which to work. Let's say there is information about the company's employees - we will use it as material.

• We add a column “Premium” - we display the results of the IF function in it. Place the cursor in cell G4.

• Click on the function icon located to the left of the operator input line, which is located above the work field. You can also insert a formula into Excel by going to the “Formulas” menu item and selecting “Logical” there.

• In “Category” we find “Logical”, and in the list that appears - the IF function. Click on “Ok”.

• A window will appear where you need to specify the function arguments, that is, the conditions.

• Let's assume the managers' bonus is 30%. We fill in the arguments, starting with a logical expression - we write there the cell address and the desired value. In our case, it looks like this: Log_expression = D4 = “manager”. Then we indicate the bonus amount (30) if the expression is true, and 0 if it is false. Click “Ok”.

• As a result, we see that the first employee’s bonus is 0%, since he is not a manager. The condition is met!

• The table contains many rows, you need to fill them all. Of course, you don’t need to enter the function again every time, you just need to copy it. This is very simple to do - click on cell G4 (the result has already been obtained there), move the cursor to the lower left corner and see when it turns into a plus.

• We stretch the formula to the remaining cells by holding the left mouse button pressed and moving down.

That's it - managers get a bonus. Using the IF formula, you can quickly select the required employees.

Important: of course, looking at the example, it seems that it is not difficult to perform similar actions manually, but it all depends on the amount of information - the more data in the table, the more difficult it is to analyze it visually. The risk of missing something is too great, and Excel will not only save time, but also help avoid mistakes.

Design and examples of use

The algorithm for writing logical formulas in Excel is as follows:

1. Need to select empty cell, in which the formula will be written and the result of the action will be displayed.

   You can also enter it in the formula bar after selecting the cell.

2. Formulas in the program are preceded by a “=” sign . Put it on.
3. Type the operator's name.
4. After this, the arguments are entered , if any. The entry begins with the “opening parenthesis “(“” sign.
5. Arguments are entered sequentially using the sign “;” . Also, if after entering the name of the function you press Ctrl + A, a menu of arguments will open and you can enter them here.
6. At the end there is a “closing parenthesis “)” symbol. You can control the writing in the formula bar.
7. When finished, press the ENTER button . The result will appear in the cell.

TRUE, FALSE

As an example, we give a solution to a problem with the logical operators TRUE and FALSE. They are usually not used separately, but only as part of other operators. You can understand the principle of operation using an example. The table of telephone numbers defines toll and toll-free calls.

After applying the formula “ =IF(LEFT(B3,4)=”8800”,TRUE(),FALSE()) ”, it turns out:

The comparison occurs based on the first four digits of the number (operator LEWSIM(B3,4)). If the number starts with 8800, then the call is free, otherwise it is not.

Denial is NOT

The function refers to a cell or argument where there is a Boolean answer and reverses it. Most often used as part of formulas. Example:

Here the operator " =NOT(F2) " inverts the argument in column F.

Application IF

"IF" always includes comparison signs and is used in formulas with a condition. The logic when using it is as follows:

1. A question is asked that contains a comparison element.
2. Next, enter 2 values. The first value will be displayed in the cell if the answer is “ TRUE ”, the second - if the answer is “ FALSE ”.
3. It is possible to create multi-level “IF” nestings .

For example, the company's employees have a minimum sales threshold of 1 million rubles. If the plan is fulfilled, the employee will receive a salary of 20 thousand rubles and a 5% bonus. If sold for a lesser amount, no premium is paid. The results of the employees' activities are displayed in the list.

It is required to divide employees in the table according to the criterion of plan execution. To do this, the program creates a table with additional columns E (Plan Fulfillment) and F (Monthly Salary).

To select employees, use the formula =IF(D4>=1000000; “Well done!”; “The plan was not fulfilled:(“) . It is deciphered as follows:

1. D4>=1000000 . A request is created to check cell D. If the indicator in D4 is greater than or equal to 1 million, then the answer is “TRUE”. If not, then "FALSE".
2. " Well done! ". If the answer is positive, the inscription “Well done!” will appear in cell E4.
3. " Plan not completed:( ". Otherwise, "Plan not completed" will be displayed.
4. Press Enter .
5. By applying autocomplete to E4, you can extend the formula to all rows in column E.

The result will be a table indicating whether the manager fulfilled the plan or not.

IFS or CONDITIONS

In the previous example there was one condition. But in most cases, many factors are taken into account when compiling reports. You have to make multi-level nested “IFs”.

Removing unnecessary spaces in numbers in Excel

For example, if you want to divide the bonus accrual depending on the percentage of sales. If revenue is less than 90% of the plan, no additional remuneration is paid. 90-95% - 10% bonus, more than 95% - 20%, sales above the plan are awarded a 30% bonus. With the “IF” operator, the formula will look like this: “=IF(B2 0.9;0;IF( B2 0.95;0.1;IF( B2 1;0.2;0.3))) .

The record is difficult to write and check. You may miss a parenthesis or specify the order of the arguments incorrectly. To simplify things, “IFLIMN” was introduced in 2020. When using it, you do not need to write “IF” for each condition and monitor the number of parentheses. The same task with “IFLIMN”:

The recording has been simplified; conditions and their corresponding values ​​are indicated. The last argument can be specified as the TRUE operator and set the desired value. If none of the conditions are met , the function parameter TRUE will be returned. In this case, if B2 >=1, then the reward is 30%.

Be careful when setting conditions . A large number of values ​​can lead to incorrect operation of the operator. The conditions are checked one by one, in the order specified. Therefore, if any of them is executed, the function will not check the remaining values, and as a result an error will appear. It is necessary to carefully consider the order of placement, so that all arguments are worked out.

Working with SWITCH

Compares the specified value in a cell or formula with a list of data and writes the first matching value into the cell. If there are no matches and the default value is not entered, the operator will generate the error “#N/A” . The function is similar to IFS, but unlike it, the condition is set exactly, without comparative signs.

The operator's work is illustrated in the figure.

Here, instead of the numbers 1, 2, 7, you need to write down the days of the week corresponding to them in words. If there are other digits, the default value “ No match ” will be returned.

Using IFERROR

The operator is used to find an error in a table. Having found it, the function does not write any of the errors in the cell, but returns the specified response , which can be text, an empty string: =IFERROR(What_to check; What_to display_instead of_error) .

For example, you need to divide the values ​​in column A by the values ​​in column B. If by mistake the rows contain 0, you will get a division by 0.

Using the operator " =IFERROR(A2/B2;"") " hides errors.

Here the expression A2/B2 is compared. If an error is detected, an empty string is placed in the cell, indicated by a space in quotation marks ““.

ISERROR appeared in Excel 2007. Previously, the ISERROR function was used, which itself could not handle the error, since it had only one argument that checked the specified cell. To enter a response if an error was detected, it was necessary to use the IF statement: “IF(ISERROR(A2/B2);”“;A2/B2)”.

AND/OR

Simple operators, rarely used without connection with other functions.

The figure shows the operating principle of the I function.

Usage example: " =AND(A1>B1; A2<>25) ". There are two conditions here:

1. The value in cell A1 must be greater than the number in B1.
2. The number in A2 must not be equal to 25.

When both are executed, the result is TRUE.

If one of the tasks is violated, the result is FALSE. In this case, the number in A1 is less than in B1.

Below is an algorithm for the operation of the OR operator.

Let 3 expressions be given: A1>B1; A2>B2; A3>B3 . It is required to apply the OR action to them: “ =OR(A1>B1; A2>B2; A3>B3) ”. Possible options are shown in the figures:

Here the final result is TRUE, since out of three expressions one is true: A3>B3. In the following image, the function returned the answer "FALSE" because all questions received similar answers.

How to Use AutoFill in Rows or Columns in Excel

Action ISQUILI

In programming, the function corresponds to the operation "addition modulo 2" or XOR . If there are more than two arguments, the following rules apply:

• the result is " TRUE " if the number of such responses is odd;
• the result is " FALSE " if the number of "TRUE" responses is even;
• the result is " FALSE ", provided that everything is "FALSE".

Given 4 conditions A1>B1; A2>B2; A3>B3; A4>B4 . Depending on the data cells, the result of the function may be different.

=EXILED(A1>B1; A2>B2; A3>B3; A4>B4)

In the figure below, the result is “TRUE”, so there are 3 conditions with a similar result: A1>B1 (100); A2 B2 (100>80); A3>B3 (100>70). The number of conditions with the answer TRUE is odd.

In the next option, the solution is “FALSE”, since there are 4 “TRUE” answers - an even number.

In the last figure, the function will also evaluate to FALSE, since not a single condition is met.

IF function with AND condition

Often the matter is not limited to one condition - for example, you need to award a bonus only to managers who work in the Southern branch of the company. We proceed as follows:

• Select with the mouse the first cell (G4) in the column with bonuses. Click on the Fx icon located to the left of the formula input line.

• A window will appear with the function arguments already filled in.

• We change the logical expression by adding another condition there and combining them using the AND operator (we put the conditions in brackets). In our case, it will be: Log_expression = AND(D4 = “manager”; E4 = “Southern”). Click “Ok”.

• We stretch the formula to all cells by selecting the first one and dragging the mouse down while holding down the left key.

Tip: if there are many rows in the table, then it becomes inconvenient to constantly scroll up and down to look at the header. There is a way out - freeze the row in Excel. Then the column names will always be shown on the screen.

Drawing up logical formulas

The main difference between Excel and Word is the presence of formulas and functions. A formula is a powerful tool for calculations, analysis and logical conclusions. It may contain constant values, functions, a reference to a cell or range of cells, operators, and comparison signs.

A logical formula contains several logical functions, links, and comparison signs. Thanks to them, you can compare values, sort data by conditions, and automate financial calculations. The practical application of the formulas is discussed below.

Task No. 1

To enter the lyceum, applicants must pass exams in three subjects: mathematics, history, and Russian language. The minimum passing score is 12, and in the Russian language the score must be no lower than 4.

You need to create a formula to calculate the scores and produce a results column indicating whether the student was accepted or not.

The solution of the problem:

=IF(AND(C2>=4,SUM(C2:E2)>=$C$8);”Enrolled”;”Not accepted”).

Here, the “IF” contains an “AND” function that checks the conditions:

1. C2>=4 , which controls the assessment in Russian.
2. SUM(C2:E2)>=$C$8 . The points received are added up. Their sum must be equal to or greater than the value in cell C8, that is, 12.
3. If both conditions are met, then AND takes the value "TRUE" , otherwise - " FALSE ".

"AND" is a logical expression for "IF". Therefore, if the answer is “TRUE”, the string “Enrolled” will be displayed in the results column, and if the answer is “FALSE”, the line “Not Accepted” will be displayed.

Task No. 2

There are stale goods in the store. Depending on the period of stay in the warehouse, it is necessary to carry out the following actions with them:

1. For a period of 8 months or more, sales promotions are introduced.
2. 10 or more months - 50% discount.
3. 12 or more months - the price is reduced by 2 times.

The solution looks like this:

=IF(D2 >= 12;" We cut the price by 2 times ";IF(D2 >= 10;"50% discount";IF(D2 >= 8; "Promotional product";""))).

The formula is made up of three “IFs” nested inside each other. If no logical expression is executed, there will be an empty cell, since the default value is an empty string. The execution result is shown in the figure.

When using IFS, the notation is simplified:

=IFLIS(D2 >= 12; “We cut the price by 2 times”; D2 >= 10; “50% discount”; D2 >= 8; “Promotional item”;””).

The ability to use functions and formulas is a prerequisite for the full operation of the program. When drawing up formulas, you need to carefully check the record. It is better to do intermediate calculations than to build a complex “multi-tiered” structure of operators, since in case of an error, it will be difficult to find it.

IF function with OR condition

As an example, let's look at how to assign a 40% bonus in Excel to all employees who are accountants or directors. That is, we will make a sample according to two conditions:

• Click on the first cell in the bonus column (G4), then click on the icon to the left of the formula entry line.

• Editing the function arguments. The logical expression will be: OR(D4="accountant";D4="director"). In “Value_if_true” we write 40, and in “Value_if_false” we write 0. Click “OK”.

• We copy the formula, stretching it to the remaining cells. Let's look at the result - a 40% bonus was awarded to the director and two accountants.

Logical set

The number of logical functions varies depending on the program version. There were 7 of them in the 2007 application, and several more were subsequently added. The list of available logical operations can be viewed as follows:

• go to the “ Formulas ” tab on the main panel;
• click on the fx icon with the inscription “ Insert formula ”;
• in the window that appears, select the “ Logical ” category;
• A list of available operators will open below .

Most have arguments that specify the conditions of application. The recording format is as follows: “ =operator(argument1;argument2…) ”. Logical notation includes comparison signs. Excel has built- in logical functions :

• TRUE;
• LIE;
• IF;
• AND;
• OR;
• NOT;
• IFERROR;
• SEARCHED;
• IFS(CONDITIONS);
• SWITCH

TRUE and FALSE

Simple operators without arguments. It is practically not used separately, only as part of expressions. “TRUE” or “TRUE” skips values ​​that correspond to the specified parameters, “FALSE” or “FALSE” - the opposite data that does not fit the selection criteria.

The form of representation of functions is as follows: “=TRUE()”, “=FALSE()” .

NOT

Has the syntax " =NOT(_boolean_value_) ". Here, the parameter or cell that needs to be checked is indicated in parentheses. “NOT” changes the final result to the opposite. If the response was " TRUE ", then "NOT" returns " FALSE " and vice versa.

AND and OR

“And” has the following form: “ =AND(log_question1;log_question2;…) .” It is possible to enter up to 255 arguments. These can be either cells or specific values. The first element must be present. "AND" checks the arguments for truth. If one answer is “FALSE”, then the result will be the same.

“OR” is written like this: “ =OR(logical_question1;logical_question2…) .” Has up to 255 arguments. If one of them has the answer "TRUE", then the whole expression will take the same result.

ISKILI

Appeared in the 2013 version of the program. Implements the “ Exclusive OR ” operation. The spelling is similar to “AND”: =EXLI(logical_question1,logical_question2,...) and can have up to 255 arguments.

If there are only 2 options for action, then the overall result will be “TRUE” if there is one argument with the same answer. In this, the work of “SEEKLI” coincides with “OR”. If both solutions receive a TRUE or FALSE answer, the result will be FALSE. For clarification, the following table is provided:

IF and IFERROR

“IF” is often used when preparing financial documents. It compares the logical question with existing data and, based on this, produces one of two options.

Formatting: “ =IF(logical_expression;value_if_true;value_if_false) .” Argument assignment:

• logical expression - a given logical question;
• value_if_true - the value returned in case of a positive answer, the result "TRUE" is set in the absence of an argument;
• value_if_false - written to the cell if the answer is negative; the result “FALSE” is set if there is no argument.

The form of the entry “IFERROR”: “ =IFERROR(value; value_if_error) .” The first argument specifies the object to check, be it a formula or a cell. If there is no error, the original value is entered. If an error is detected, the second argument is written. Types of errors to check:

• #N/A;
• #VALUE;
• #NUMBER!;
• #DIV/0!;
• #LINK!;
• #NAME?;
• #EMPTY.

IFS (CONDITIONS) and SWITCH

IFS and SWITCH are new in Excel 2020 and 2019, respectively. Designed to facilitate the preparation of formulas, as they reduce the number of investments.

Creating a Dropdown List in Excel

“IF LIMITED” was previously called “TERMS”. Its introduction is associated with an attempt to make work easier when nesting several “IFs”. There is no need to write “IF” several times and open numerous parentheses. Syntax: “ =IFS(condition1, value1,condition2, value2,condition3, value3...) .” You can create up to 127 conditions.

"SWITCH" has the following structure: " =SWITCH(value to switch; value to match1...[2-126]; value returned if match1...[2-126]; value returned if there is no match) ."

The first argument specifies the location of the expression being tested, the others assign the first matched value to the cell.

SUMIF function

This formula in Excel is used when you need to sum the values ​​in cells that fall under any given condition. For example, you need to find out the total salary of all salespeople:

• Add a line with the total salary of salespeople and click on the cell where the result will be displayed.

• Click on the Fx icon, which is located to the left of the function input line. In the window that opens, look for the required formula through the search - enter “sumsif” in the corresponding window, select the operator in the list, click “OK”.

• A window will appear where you need to fill in the function arguments.

• We enter the arguments - the first field “Range” determines which cells need to be checked. In this case, employee positions. Click in the “Range” field and indicate D4:D18 there. You can do it even simpler - just select the required cells.

• In the “Criteria” field, enter “seller”. In the “Summation_Range” we write cells with employee salaries (manually or by selecting them with the mouse). Next - “Ok”.

• We look at the result - the total wages of all salespeople have been calculated.

Tip: Making a chart in Excel is quick and easy—you just need to find the corresponding button on the “Insert” tab in the menu.

SUMIFS function

This operator in Excel is designed to calculate a sum using several conditions. For example, you need to determine the salaries of managers working in the Southern branch:

• We add a line with the total salary of managers of the Southern branch. Select the cell where the total of the calculations will be shown.

• Click on the function icon. In the window that appears, enter “sumslimn” in the search field, click “Find” and select the desired operator from the list.

• In the window that opens, you need to fill in the function arguments. In the “Summation_Range” we indicate the cells with wages. “Range_conditions1” — cells with employee positions. “Condition1” = “manager”, since we are summing up the salaries of managers. Now we need to take into account the second condition - to take managers from the Southern branch. In “Range_conditions2” we enter cells with branches, “Condition2” = “South”. All arguments are defined, click “Ok”.

• As a result, the total salary of all managers working in the Southern branch will be calculated.

COUNTIF function

This operator is used in Excel when you need to count the number of cells that fall under a certain condition. Suppose you want to find out how many salespeople work in the company:

• Add a line with the number of sellers and select the cell where you plan to display the result.

• Click on the “Insert Function” button located in the “Formulas” tab in the menu. In the window that opens, in the “Category” field, select “Full alphabetical list”. In the list of formulas, find the alphabetical COUNTIF and click “OK.”

• We fill in the function arguments - in the “Range” field we indicate the cells with positions, in the “Criteria” we write “salesman”. Next - “Ok”.

• As a result, we get the number of salespeople working in the company. Excel simply counts the cells where “seller” is written.

AND function

Returns the boolean value TRUE if all function arguments return true. If at least one argument returns FALSE, then the entire function will return that value.

Conditions or references to cells that return logical values ​​must be accepted as arguments. The number of arguments cannot exceed 255. The first argument is required.

Consider the truth table of this function:

Syntax: =AND(Boolean_value1, [Boolean_value1];...)

Usage example:

In the first example, you can see that all arguments return a true value, therefore, the function itself will return a true result.

In the second example, the function will never return TRUE, because the condition of its second argument is not equal in advance.

COUNTIFS function

Sometimes more complex tasks arise - for example, you need to determine how many salespeople work in the Northern branch. Then you should use the COUNTIFS formula:

• Add a line with the number of sellers of the Northern branch and select the cell with the future result.

• Click on the “Insert Function” button in the “Formulas” tab. Using the alphabetical list, find the desired function and click “OK”.

• We enter the function arguments: “Condition_range1” is the cells with positions, “Condition1” = “salesman”. In “Range_condition2” we write cells with branches, “Condition2” = “Northern”.

• As a result, the number of sellers of the Northern branch will be determined.

We combine several conditions.

In order to describe the condition in the IF function, Excel allows you to use more complex constructs. You can also use several conditions. At the same time, we will also take advantage of the fact that functions can be “nested” inside each other.

To combine several conditions into one, we use the logical functions OR and AND . Let's look at simple examples.

Example 1

The OR function returns TRUE if at least one of its several conditions is true.

=IF( OR(C2=”East”, C2=”South”) ,”Export”,”Local”)

We insert the OR function as a condition into the IF function. In our case, if the buyer’s region is East or South, then the shipment is considered an export.

Example 2.

Let's use some more complex conditions inside the IF function.

If the sales region is West or South, and the quantity is more than 100, then a 10% discount is provided.

=IF( AND(OR(C2="West",C2="South"),E2>100) ,F2*0.1,0)

The AND function returns TRUE if all of its conditions are true. Inside the And function we place two conditions:

1. Region - either West or South
2. The quantity is more than 100.

We implement the first of them in the same way as it was done in the first example: OR(C2=”West”, C2=”South”)

Secondly, everything is very simple here: E2>100

Lines 2, 3 and 5 satisfy both conditions. These customers will receive a discount.

In line 4 none are executed. But in line 6,7,8 only the first one is completed, but the number is too small. Therefore the discount will be zero.

Example 3.

Of course, these few conditions can be more complex. After all, logical functions can be “nested” within each other.

For example, in addition to the previous condition, the discount is only available on dark chocolate.

Our entire previously written condition becomes, in turn, the first argument in the new AND function:

1. Region - West or South and quantity greater than 100 (discussed in example 2)
2. The name of the chocolate contains the word “black”.

As a result, we get an IF formula with several conditions:

=IF(AND(ANDNUMBER(FIND(“Black”,D2)), AND(OR(C2=”West”,C2=”South”)),E2>100),F2*0.1,0)

The FIND function looks for an exact match. If the case of characters in the text is not important for us, then instead of FIND we can use a similar COICH function.

=IF(AND(ANDNUMBER(MATCH("black",D2)), AND(OR(C2="West",C2="South")),E2>100),F2*0.1,0)

As a result, the number of nested conditions in Excel can be very large. It is only important to strictly follow the logical sequence of their implementation.

Let's sum it up

In Excel, there are several functions of the IF category - they are not difficult to use, since the program prompts the algorithm of actions as much as possible. Formulas greatly facilitate calculations, which can take a lot of time and effort without Excel. In addition, the risk of errors and the human factor is leveled. If difficulties arise with the use of operators, then most likely the problem is that the function arguments were entered incorrectly - double-check the formula. To clarify the syntax, it is better to use the help that Excel has for each function.