Contents
Excel is an incredibly functional program that allows you not only to record data in a tabular form, but also to automate their processing. Logic functions are the main element that allows you to carry out any operations of this kind. They are used in formulas and other functions in order to simplify all operations.
They are designed to check if the values meet the specified criteria. If there is such a match, in the cell where it is written, the value “TRUE” is entered, in case of a discrepancy – “FALSE”. Today we will consider in more detail such issues as the structure of logical functions, the scope of their use.
List of Boolean Functions in Excel
There are a huge number of logical functions, but the most commonly used are the following:
- TRUE
- LYING
- IF
- IFERROR
- OR
- И
- NOT
- EOSHIBKA
- ISBLANK
All of them can be used to create complex structures and specify criteria of any order. Almost all of these functions involve passing certain parameters to them. The only exceptions are TRUE and FALSE, which return themselves. Numbers, text, cell references, ranges, and so on are often used as parameters. Let’s take a look at all of the above operators.
Operators TRUE and FALSE
What both of these functions have in common is that they only return one value. The scope of their use is the use as a component of other functions. As can be understood from the name of the operators, the functions TRUE и LYING return values TRUE и LYING respectively.
NOT operator
This function is used with one argument and writes the opposite value to the cell. If you pass this operator TRUE, then it will return LYING and, accordingly, the opposite assertion is true. Therefore, the result of data processing by this operator completely depends on what parameters to pass to it. 
The syntax of this operator is as follows: =NOT(true or false).
Operators AND and OR
These two operators are necessary in order to convey the relation of the conditions of an expression to each other. Function И is used to indicate that two criteria must match the same number or text at the same time. This function returns a value TRUE only on the condition that all criteria produce this value at the same time. If at least one criterion fails, the entire sequence returns a value LYING. 
The way the AND operator is built is very simple: =And(argument1; argument2; …). The maximum number of arguments that can be used by this function is 255. Operator syntax OR similar, but the mechanics of work are slightly different. If one of the list of functions produces a result TRUE, then this number will be returned as a whole logical sequence. 
IF and ISERROR statements
These two functions have a very important purpose – they directly set the criterion for compliance with which a certain expression must be checked. For a deeper understanding of how the operator works IFERROR, you must first describe the function IF. Its general structure is a little more complicated than the previous ones: =IF(logical_expression, value_if_true, value_if_false).
The task of this operator is to create the most complex constructions. It checks if the criteria is met. If yes, then the operator will return TRUE, if not – LYING. But the operator is often used in conjunction with others. For example, if it is used as a function argument NOT, then, accordingly, the total will be automatically replaced by the opposite. That is, if there is a match to the criterion, then the value will be returned LYING. This is the main advantage of logic functions: they can be combined in the most bizarre forms.
Further, the scheme becomes more complicated. If by this criterion we get the result “TRUE”, then you can specify the text, the number that will be displayed or the function that will be calculated. Similarly, you can set the result that will be displayed if the result was returned after processing the data. LYING. 
Operator structure IFERROR quite similar, but still somewhat different. Contains two required arguments:
- Meaning. It is the expression itself that is being tested. If it turns out to be true, then that value is returned.
- The value if an error. This is the text, number, or function that will be displayed or executed if the result of checking for the first argument was FALSE.
Syntax: =IFERROR(value;value_if_error).
ISERROW and ISEMPLAND operators
The first function of the above contains only one value and has the following syntax: =ISERROR(value). The task of this operator is to check how well the cells are filled (one or in the whole range). If it turns out that the padding was wrong, it returns the true result. If everything is good – false. Can be applied directly as a criterion for another function. 
Excel can check links for the following types of errors:
- #NAME?;
- #N/A;
- #DEL/0!;
- #NUMBER!;
- #SO;
- #EMPTY!;
- #LINK!.
Function ISBLANK Overall, it’s incredibly simple. It contains only one parameter, which is the cell/range to be checked. If there is a cell that has neither text, nor numbers, nor non-printing characters, then the result is returned TRUE. Accordingly, if there is data in all cells of the range, then the user receives the result LYING. 
Memo table “Logical functions in Excel”
To summarize everything described above, let’s give a small table containing information about all commonly used logic functions.
Logic functions and examples of problem solving
Logic functions make it possible to solve a variety of tasks, including complex ones. Let’s give some examples of how they work in practice.
Task 1. Suppose we have a part of the goods left after a certain sales time. It must be re-evaluated according to the following rules: if it was not possible to sell it in 8 months, divide its price by 2 times. First, let’s create a range that describes the initial data. It looks like this.
In order for the described task to be successfully solved, you need to use the following function. 
You can see it in the formula bar in the screenshot. Now let’s make some clarifications. The logical expression that was shown in the screenshot (that is, C2>=8) means that the product must be in stock for up to 8 months inclusive. Using the >= arithmetic operators, we define the greater than or equal to rule. After we have written this condition, the function will return one of two values: “TRUE” or “FALSE”. If the formula meets the criterion, then the value after revaluation is written to the cell (well, or passed as an argument to another function, it all depends on the parameters set by the user), divided by two (for this, we divided the price at the time of receipt at the warehouse by two) . If after that it is found that the product has been in stock for less than 8 months, then the same value is returned that is contained in the cell.
Now let’s make the task more difficult. We apply the condition: the scale of discounts must be progressive. Simply put, if the goods lie for more than 5 months, but less than 8, the price should be divided by one and a half times. If more than 8, two. For this formula to match the value, it must be as follows. Look at the screenshot in the formula bar to see it.
Important! As arguments, it is permissible to use not only numeric, but also text values. Therefore it is admissible to set criteria of the most different order. For example, to make a discount on goods received in January and not to do it if they arrived in April.
Task 2. Let’s apply this criterion to a product that is in stock. Suppose, if after the markdown made above, its value has become less than 300 rubles, or if it has been without sale for more than 10 months, then it is simply removed from sale. The formula is the following.
Let’s analyze it. We used the function as a criterion OR. It is needed to provide such a fork. If cell D2 contains the number 10, then the value “written off” will be automatically displayed in the corresponding line of column E. The same applies to the other condition. If none of them are met, then an empty cell is simply returned.
Task 3. Let’s say we have a sample of students trying to get into high school. To do this, they need to pass exams in several subjects, shown in the screenshot below. To be considered eligible for admission to this educational institution, they must score a total of 12 points. At the same time, an important condition is that the score in mathematics should be no less than 4 points. The task is to automate the processing of this data, as well as to compile a report on which students entered and which did not. To do this, we will make such a table.
So, our task is to make the program calculate how many points there will be in total, look at the passing result and perform a comparison. After these operations, the function must put the result in the cell in which it fits. There are two possible options: “accepted” or “no”. To implement this task, enter a similar formula (just plug in your values): =ЕСЛИ(И(B3>=4;СУММ(B3:D3)>=$B$1);»принят»;»нет»).
With a boolean function И we can verify that two conditions are met at once. In this case, we used the function SUM to calculate the total score. As the first condition (in the first argument of the AND function), we specified the formula B3>=4. This column contains a score in mathematics, which should not be lower than 4 points.
We see the wide application of the function IF when working with spreadsheets. That is why it is the most popular logic function that you need to know first.
It is highly recommended to practice on the test chart before using these skills in real work. This will help save a lot of time.
Task 4. We are faced with the task of determining the total cost of goods after markdown. Requirement – the cost of the product must be higher or average. If this condition is not met, the goods must be written off. In this example, we will see how a bunch of arithmetic and statistical functions work.
Let’s use the table we’ve already drawn. In order to solve this problem, it is necessary to set the rule as a condition that cell D2 must be less than the arithmetic mean of the entire range of goods. If the rule is confirmed, then in the cell where this formula is written, the value “written off” is set. If the criterion is not met, then an empty value is set. To return the arithmetic mean, there is a function AVERAGE. 
Task 5. Suppose we need to calculate the average sales of different products in different stores of the same brand. Let’s make such a table.
Our task is to determine the average for all values, which fits certain characteristics. To do this, we use a special function that was not in the list above. It allows you to combine two functions AVERAGE и IF. And she called HEARTLESS. Contains three arguments:
- The range to check.
- The condition to be checked.
- Range averaging.
As a result, the following formula is obtained (in the screenshot).
We see that the range of application of logical functions is simply huge. And their list is actually much larger than described above. We just listed the most popular of them, but also described an example of another function, which is a combination of statistical and logical. There are also other similar hybrids that deserve separate consideration.