When you have an intermediate or advanced level in managing and you need to controlautomatically, the entire spreadsheet, it is necessary to have tools that help you detect errors in the formulas.
Microsoft Excel has functions that will help youin a few steps, to analyze conditional arguments so that you achieve optimal results. These functions are found in the group of logical formulas.
If you want to become an expert, continue reading this article until the end.. We will teach you the step by step of all the functions that you will use in a spreadsheet.
What is a logical function and what is it for in Excel spreadsheets?
A logical function is a type of tool used in Excel that helps to make decisions and evaluate if the formulas and data that have been entered meet different conditions that were established earlier.
In this way, any reference characteristic can be modified so that it meets the established criteria.
What are all the logical functions that I can use in my Excel spreadsheets?
The logical functions that you can use in a Microsoft Excel spreadsheet are the following:
- FAKE: This function returns the logical value. That is, it has no arguments, so it will always have the FALSE result.
- NO: in case of having a value that is true or false, you can modify it by the opposite function. With this you can change a true boolean value to a false one or vice versa.
- EITHER: This function is used when you have up to 255 conditions that you want to test and it returns an argument true when it tests or it can return false or true if some or all of the arguments are false.
- YES: is a conditional function that must satisfy a condition in order for the argument to be evaluated as true or false. In this way, different situations are established that must be met for the argument to return a certain value.
- MISTAKE: to establish if an expression is an error or has a value that is not true, you must use this formula.
- NA: in case the expression becomes a #N/A result the tool returns a specific value. If not, the result will be that of the expression.
- REAL:ll Like the FALSE function, this tool has no arguments so it returns the value TRUE.
- XO: when you want to test in a list of conditions if they can be true or false expressions, you must use this function.
- Y: if all the arguments are true, it will return the true value.
Learn step by step how to use all the logical functions of Excel like a professional
The logical functions that you can use in a Microsoft Excel spreadsheet are the following:
FALSE
As with the TRUE function, this formula does not need an ARGUMENT, so you will find the value using it directly. An example of this is: “=FALSE() returns the value FALSE”
Yes
To use this function, the first thing you must determine is the condition that the cell must meet and then expose the logical values that will be given if the result meets the established condition or not. If we take as an example the possible 30% increase in employee salaries and there is the possibility of approving them as long as the amount is less than €2,500.
Therefore, the formula should be used in this way:
=IF(>=2500;”OUT OF BUDGET”;”APPROVED”)
Nope
When you need to use this function, you just have to choose the cell and then press Enter, this will return the opposite value. For example:
- =NOT(TRUE) will equal FALSE
- =NOT(FALSE) will equal TRUE
EITHER
You can use this function to check if there are true or false arguments in any given condition.
In the case of the example of the 30% increase in the salary for employees, it had been established that those whose increase was greater than €2,500 were out of the budget. But if we analyze a little more and we logically assume that if the increase is between €2,501 and €3,000, they could be reconsidered. In this way the result would yield REAL.
The use of the formula is as follows:
=O(2501>=C2<=2999)
With the example, it is determined that EMPLOYEE 3 exists and may have the authorization for the 30% increase, even though it was initially outside the budget.
Y
This tool will help you check different determinants that will return a true value, if all the conditions you set as argument are correct. Otherwise the value will be false.
Continuing with the example, the new salaries that the 7 employees will have can be established. To check if the correct job was actually done, the AND function and it is demonstrated by comparing the column of the new salaries with that of the increase of 30%.
If both columns are the same, the value will be true, but since there are two employees who did not have the approval, they should have the old salary, so the result will be false. This means that the column is well designed.
EMPLOYEE 6’s salary has been poorly settled, since the 30% increase was taken when it did not correspond. So your salary will be €2,559.
REAL
The use of this function is very simple, you will only have to incorporate the formula so that it returns the true value. For example:
=TRUE() returns the value of the cell TRUE
SIND
This function is needed to find errors #N/A in the argument list. Its use is simple, you only have to choose the elements that you need to analyze.
XO
It is a useful tool for setting conditions and can return true or false to arrays, references, or boolean values. If you want to use the formula in an array you will have to hit the combination “Ctrl + Alt + Enter”after having written “=XOR(”.
SIERROR
If you have an expression that has an error, it will return ERROR. This way you can include an argument to report this mistake. Its use is as follows:
=IF.ERROR(H2;”BADLY EXPRESSED”)
Facebook Comments
Disqus