Logical Excel Functions  What are they? + How to use them ▷ 2020

When you have an intermediate or advanced level in the handling of spreadsheets and you need to control, automatically, the entire spreadsheet, it is necessary to have tools that help you detect errors in the formulas.

Microsoft Excel has functions to help you, in a few steps, to analyze conditional arguments so that you achieve optimal results. These functions are in the group of logical formulas.

If you want to become an expert, continue reading this article to 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 before.

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:

  • FALSE: this function returns the logical value. That is, it has no arguments so it will always have the result FALSE.
  • 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 logical value for a false one or vice versa.
  • OR: This function is used when you have up to 255 conditions that you want to test, and returns a true argument 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 meet a condition so that it can be evaluated if the argument is true or false. In this way, different situations are established that must meet for the argument to return a certain value.
  • ERROR: to establish if an expression is an error or has a value that is not true, use this formula.
  • ND: in case the expression becomes a result # N / A the tool returns a specific value. If not, the result will be that of the expression.
  • TRUE: ll like the FALSE function, this tool has no arguments so it returns TRUE.
  • XO: when you want to test in a list of conditions if they can be true or false expressions you should use this function.
  • Y: in case 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 pro

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

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 increase of 30% to the salaries of the employees and there is the possibility of approving them as long as the amount is less than € 2,500.

Therefore, the formula should be used like this:

= YES ([el lugar en el que se encuentre el importe del aumento]> = 2500; “OUT OF BUDGET”; “APPROVED”)

No

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:

  • = NO (TRUE) will be equal to FALSE
  • = NO (FALSE) will be equal to TRUE

OR

OR

This function can be used to check if there are true or false arguments in any given condition.

In the case of the example of the 30% increase in salary to employees, it had been established that those with an increase of more than € 2,500 were out of budget. But if we analyze a little more and put as a logical way that if the increase is between € 2501 and € 3000 they could be reconsidered. In this way the result would give TRUE.

The use of the formula is as follows:

= O (2501> = C2

With the example it is determined that EMPLOYEE 3 exists and may have the authorization of the 30% increase, even though it was originally out of budget.

Y

Y

This tool will help you to check different determinants that they will return a true value, if all the conditions you set as an argument they 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 work was really done, the Y function and it is demonstrated by comparing the column of the new salaries with the one 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 has badly paid the salary, since the 30% increase was taken when it did not correspond. So your salary will be € 2,559.

TRUE

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 TRUE cell

SIND

This function is necessary 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 whether arrays, references, or logical values ​​are true or false. If you want to use the formula in an array you will have to press the combination “Ctrl + Alt + Enter”, after having written “= XO (“.

SIERROR

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; “POORLY EXPRESSED”)

If you have any questions, leave them in the comments, we will answer you as soon as possible, and it will also be of great help to more members of the community. Thank you! 😉

You may be interested:

Rate this post

Leave a Comment