software

Microsoft excel logical functions what arehe, what arehe, for and how to use them perfectly in my spreadsheets?

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

Microsoft Excel has functions that will 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 that is 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 it returns a true argument when it tests, or it can return false or true if some or all of the arguments are false.
  • IF : it 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 be met 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, this formula should be used.
  • 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 the value 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 : 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 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

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 ( [the place where the amount of the increase is found] > = 2500; “OUT OF BUDGET”; “APPROVED”)

Not

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

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 salary for 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 return TRUE .

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 of the 30% increase, even though it was initially out of budget.

Y

This tool will help you to check different determinants that will return you a true value, if all the conditions you set as an 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 work was really done, the Y function is used and it is demonstrated by comparing the column of new salaries with that of the 30% increase.

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 to return the true value. For example:

= TRUE () returns the value of the TRUE cell

SIND

This function is necessary to find # N / A errors 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

If you have an expression that has an error, it will return ERROR. This way you will be able to include an argument to report this mistake. Its use is as follows:

= IF.ERROR (H2; “POORLY EXPRESSED”)

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button