There are spreadsheets in which it is necessary to apply logical functions, logical schemes of various orders. The Microsoft Excel software package comes to the rescue. It can not only calculate the logical value of an expression, but also perform 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 results, and build graphs and charts of various types.
The user uses not only logical functions in Excel, but also mathematical, statistical, financial, text, etc.
Excel Features
The application areas of the software product are diverse:
- The Excel worksheet is a ready-made spreadsheet, so there is no need for the user to perform calculations to bring the document to the proper form.
- The software package offers the use of Boolean functions, as well as trigonometric, statistical,text, etc.
- Based on calculations, Excel builds graphs and charts.
- Because the software package contains a large library of mathematical and statistical functions, schoolchildren and students can use it to complete laboratory and term papers.
- It is useful for the user to use the features of Excel for home and personal calculations.
- The VBA programming language is built into the software product, which can make life easier for an accountant when automating the workflow of a small company.
- The Excel spreadsheet also acts as a database. Full functionality is implemented only from the 2007 version. Early products had a line limit.
- When creating reports of various kinds, Excel comes to the rescue as it helps to create a pivot table.
Excel Logical Operators
Boolean expressions are understood as the data necessary to write elements in which conjunction and disjunction, as well as other operators, match numbers, formulas, text. With their help, the message is written in symbolic form, indicating the action.
Logical functions (otherwise called Boolean) use numbers, text, links with cell addresses as elements.
There are several ways to learn more about each operator and its syntax:
- Call Function Wizard.
- Use Microsoft help via F1.
- In 2007 versions of Excel, examine the composition of each category on the toolbar.
Boolean algebra
The founder of propositional logic (another name for a section of mathematics) is D. Buhl, who in his youth was engaged in translations of the works of ancient Greek philosophers. It was from there that he gained knowledge and proposed to introduce special designations for statements: 1 - True, 0 - False.
Boolean algebra is a branch of mathematics that studies statements, treats them as logical values, and performs operations on them. Any statement can be encoded and then used, manipulated to prove true or false.
A Boolean function is called f(x1, x2, …, x ), from 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 written in text form);
- table;
- numeric;
- graphic;
- analytic;
- coordinate.
And function
The AND operator is a conjunction in the Excel software package. Otherwise, it is called logical multiplication. It is usually denoted by ∧, &,or the sign between the 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 2operand;
- ternary if there are 3 multipliers;
- n-ary if the set contains n operands.
You can solve an example by matching the rule or by creating a truth table. If the expression contains several operands, it is more convenient to use the Excel software package for the second solution, since the whole process will be cumbersome when calculating manually.
The result of calculations can be:
- True: if all arguments are true.
- False: if all 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 parentheses is not mathematically correct, so the function will return False.
2) The two cells have opposite values. The AND function returns False because one of the arguments is false.
3) Arithmetic operations are set. It is necessary to check their truth. This operator returns "True" because everything is correct from an arithmetic point of view.
Function "OR"
The "OR" operator in the category "Logical functions" is a disjunction, that is, it allows you to get a truthful answer in a non-categorical form. Another name for an operator in Boolean algebra: logical addition. Designate: ∨, +, "or". Variables take values from the set and the answer is written there.
The results of calculations are:
- True: if any or all of the arguments are true.
- False: if all criteria are false.
Example.
1) Disjunction in Excel checks not only logical expressions, but also mathematical ones for correctness. So, in this particular case, both results are false from an arithmetic point of view, so the answer is False.
2) The operator returns True because one of the arguments is true and the other is false. This is a valid criterion for disjunction.
IF function
In the "Logic functions" group, the "IF" operator takes pride of place. The function is needed to get 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 specify what the total should be in the case of "False", then the operator gives a result equal to 0.
Example.
Given:
- product name;
- its price for 1 unit;
- quantity of goods purchased;
- price.
It is necessary to calculate the column "Payable". If the purchase price exceeds 1000 rubles, then the buyer is given a 3% discount. Otherwise, the columns "TOTAL" and "Payable" are the same.
1) Condition check: the cost exceeds 1000 rubles.
2) If truecriterion cost is multiplied by 3%.
3) If the statement is false, the result “Payable” does not differ from “TOTAL”.
Checking multiple conditions
There is a table that shows the scores for the exam and the mark of the teacher.
1) It is necessary to check if the total score is less than 35. If the answer is true, then the result of the work is “Failed.”
2) If the previous condition is false, the score is >35, the operator proceeds to the next argument. If the value in the cell is >=75, then "Excellent" is assigned next to it. Otherwise, the function will return "Passed".
Although the "If" operator works with boolean values, it works fine with numbers as well.
Example.
Data:
- vendor names;
- their sales.
It should be calculated which of the sellers what 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 bonus is issued in the amount of 4%.
Under the number 1 is the first block "IF", where it is checked for truth. If the condition is false, then block 2 is executed, where 2 more criteria are added.
Function "IFERROR"
Boolean functions are complemented by this operator, because it is able to return some result if there is an error in the formula. If alltrue, "IFERROR" returns the result of the calculation.
Function "TRUE" and "FALSE"
Boolean functions in Excel cannot do without the "TRUE" operator. It returns the corresponding value.
The inverse of "TRUE" is "FALSE". Both functions take no arguments and are rarely used as standalone examples.
NOT operator
All logical functions in Excel can be refuted using the "NOT" operator. The value entered when using this procedure will result in the opposite.
Example.
Obviously, the operator gives the opposite answer to 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 arguments. If you use the axioms and theorems of logic, you 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 are:
- Carnot cards;
- Quine method;
- implicant matrix algorithm;
- Quine-McCluskey method, etc.
If the number of arguments does not exceed 6, then it is better for the user to use the Karnot map method for clarity. Otherwise, the Quine-McCluskey algorithm is applied.
