There are spreadsheets in which it is necessary to apply logical functions, logical circuits of different orders. The Microsoft Excel software package comes to the rescue. He is able not only to calculate the logical value of the expression, but also to carry out complex mathematical calculations.
What is Excel?
A software product designed to work with spreadsheets. Created by Microsoft and suitable for almost any operating system. Here you can use both formulas to find the results, and build graphs and charts of various kinds.
The user uses in Excel not only logical functions, but also mathematical, statistical, financial, textual, etc.
Excel Features
Scopes of a software product are various:
- The Excel worksheet is a ready-made table, so the user no longer needs to make calculations to bring the document to its proper form.
- The software package offers the use of Boolean functions, as well as trigonometric, statistical, text, etc.
- Excel builds graphs and charts based on calculations.
- Since the software package contains a large library of mathematical and statistical functions, schoolchildren and students can use it to perform laboratory and term papers.
- It is useful for the user to use Excel features for home and personal calculations.
- The VBA programming language is built into the software product, capable of making life easier for the accountant in automating the workflow of a small company.
- An Excel spreadsheet also acts as a database. Fully functional is implemented only from the 2007 version. In early products there was a limit on the number of rows.
- When creating reports of various kinds, Excel comes to the rescue as it helps to create a pivot table.
Excel Logical Operators
Logical expressions are understood as data needed to record elements in which conjunction and disjunction, as well as other operators, compare numbers, formulas, text. With their help, the message is recorded in symbolic form with an indication of the action.
Logical functions (also called Boolean) use numbers, text, links with cell addresses as elements.
You can learn more about each operator and its syntax in several ways:
- Call the "Function Wizard".
- Use Microsoft Help through F1.
- In Excel versions from 2007, examine the composition of each category in the toolbar.
Boolean Algebra
The founder of the propositional logic (another name for the section of mathematics) is D. Bull, who was engaged in translations of the works of ancient Greek philosophers in his youth. It was from there that he gained knowledge and suggested introducing special notation for statements: 1 - Truth, 0 - False.
Boolean algebra is a branch of mathematics that studies utterances, considers them as logical values and the operations performed on them. Any statement can be encoded, and then used, manipulated to prove it is true or false.
A Boolean function is called f (x 1 , x 2 , ..., x n ), of n variables, if the function or any of its operators takes values only from the set {0; 1}. The laws of the algebra of logic are applied in solving problems, in programming, coding, etc.
You can display a Boolean function in the following ways:
- verbal (statement recorded in text form);
- tabular;
- numerical
- graphic;
- analytical;
- coordinate.
The And Function
The AND operator is a conjunction in the Excel software package. Otherwise, it is called logical multiplication. Usually denoted by ∧, &, * or the sign between operands is completely omitted. The function is needed to determine the veracity of the entered expression. In Boolean algebra, a conjunction takes values from a set, and the result of the calculation is also written to it. Logical multiplication happens:
- binary, because it contains 2 operands;
- ternary, if there are 3 factors;
- n-ary if the set contains n operands.
You can solve the example through agreement with the rule or through the creation of a truth table. If the expression contains several operands, it is more convenient to use the Excel software package for the second method of solution, since the whole process when calculating manually will be cumbersome.
The result of the calculations may be:
- Truth: if all arguments are true.
- False: if all the criteria are false or at least one of them.
The AND and OR operators can contain up to 30 criteria.
Example.
1) It is necessary to determine the truth of the entered data. Obviously, the last example, enclosed in brackets, is incorrect from a mathematical point of view, therefore the function will give the answer “False”.
2) In two cells opposite values are indicated. The AND function returns the result False, because one of the arguments is false.
3) Arithmetic operations are given. It is necessary to verify their truth. This operator gives the answer “True”, since from an arithmetic point of view, everything is correct.
OR function
The OR operator in the Logical Functions category is a disjunction, that is, it allows you to get a truthful answer in a non-categorical form. Another name for the operator in Boolean algebra: logical addition. Denote: ∨, +, "or". Variables take values from the set and the answer is written there.
The result of the calculations are:
- Truth: if at least one of the arguments or all of them are true.
- False: if all criteria are false.
Example.
1) The disjunction in Excel checks not only logical expressions, but also mathematical ones for correctness. So, in a particular case, both results are false from an arithmetic point of view, so the answer is “False”.
2) The operator gives the answer “True”, because one of the arguments is true, the other is false. This is a valid criterion for disjunction.
IF function
In the group “Logical functions”, the operator “IF” takes pride of place. The function is necessary to obtain a result if the information is true, and another result if the data is false.
- In a conditional statement, it is possible to check up to 64 conditions at a time.
- If one of the criteria is an array, then the function checks each element.
- If the answer is false, but the formula does not spell out what the total should be in the case of “False”, then the operator returns a result equal to 0.
Example.
Given:
- Name of product;
- its price for 1 unit;
- quantity of purchased goods;
- cost.
It is necessary to calculate the column "For payment". If the purchase price exceeds 1000 rubles, then the buyer is given a 3% discount. Otherwise, the columns “TOTAL” and “For payment” are the same.
1) Verification of the condition: the cost exceeds 1000 rubles.
2) If the criterion is true, the cost is multiplied by 3%.
3) In the case of a false statement, the result “For payment” does not differ from “TOTAL”.
Checking multiple conditions
There is a table showing the scores for the exam and the mark of the teacher.
1) It is necessary to check whether the sum of points is less than 35. If the answer is true, then the result of the work is “Did not pass”.
2) If the previous condition is false, the sum of points> 35, the operator proceeds to check the next argument. If the value in the cell> = 75, then “Excellent” is assigned next to it. In other cases, the function will return “Passed”.
Despite the fact that the “If” operator works with logical values, it works great with numbers.
Example.
Are given:
- names of sellers;
- their sales.
It should be calculated to which of the sellers which commission is due:
- if the number of sales is less than 50 thousand, then the percentage is not charged;
- if the volume of transactions varies between 50-100 thousand, then the commission is 2%;
- if the number of sales is more than 100 thousand, then the premium is issued in the amount of 4%.
Under the number 1 is marked the first block "IF", where it is checked for truth. If the condition is false, then block 2 is executed, where an additional 2 more criteria are prescribed.
ERROR function
Boolean functions are supplemented by this operator, since it is able to return a certain result if there is an error in the formula. If everything is correct, "ERROR" returns the result of the calculation.
The function "TRUE" and "FALSE"
Boolean functions in Excel are not complete without the TRUE operator. It returns the appropriate value.
The inverse operator for the TRUE action is FALSE. Both functions have no arguments and are rarely used as a standalone example.
Operator "NOT"
All logical functions in Excel can be refuted using the "NOT" operator. The entered value when using this procedure will result in the opposite.
Example.
Obviously, the operator returns the opposite of the original data.
Minimization of logical functions
This phenomenon is directly related to the creation of a circuit or circuit. This is expressed through its complexity and cost, the proportionality of the number of logical operations and the number of occurrences of the arguments. If we use the axioms and theorems of logic, we can simplify the function.
There are special algorithmic minimization methods. Thanks to them, the user is able to independently simplify the function quickly and without errors. Among these methods distinguish:
- Carnot cards;
- Quine's method;
- implicant matrix algorithm;
- Quine-McClusky method, etc.
If the number of arguments does not exceed 6, then the user should use the Carnot map method for clarity. Otherwise, the Quine-McCluskey algorithm is used.