5 Logic Functions in Excel You Should Master and How to Use Them + Examples
Home >> Excel Tutorials from Compute Expert >> Excel Tips and Trick >> 5 Logic Functions in Excel You Should Master and How to Use Them + Examples
In this tutorial, you will know the top 5 logic functions in excel that you should master. We will show their implementation example here and discuss a bit about how to use them. Moreover, we will also talk about excel logic function in general, logic operators, and other excel logic functions besides those five.
As we work in excel, we may sometimes need to get our data processing results based on logic. Whether a logic condition that we test is true or not will strongly determine the results that we get. For this kind of data processing, excel has provided us with some logic functions we can utilize.
However, we may not often use all the logic functions. Some seem to be more important to master than others. That is because we should more often need those logic functions when processing data in excel.
Want to know what are those logic functions which can be more important when working in excel? Want to learn much more about the logic processing on data in excel? Read this tutorial until its last part!
Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more
Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!
Table of Contents:
What is a Logic Function in Excel?
A logic function in excel is a function that depends its result heavily on one/multiple logic conditions test.A logic condition produces a TRUE/FALSE logic value, depending on whether the condition is correct or not. This TRUE/FALSE logic value will determine our logic function result.
The most common example of a logic function in excel is probably IF. If we write an IF formula like this in a cell:
= IF ( A1 > B1 , “Correct” , “Wrong” )
Then the cell value will depend on the comparison test result between the A1 and B1 cell values. If cell A1 has a larger value than B1, then the cell where we write our IF will get “Correct”. Otherwise, the cell will get “Wrong”.
Excel Logical Operators
To input a logic condition into a logic function, it seems that we always have to use a logical operator. Below is a table that summarizes logical operators we can use in excel, their meaning, and their writing example.| Logical Operator | Meaning | Writing Example |
|---|---|---|
| = | equal to | A1=B1 |
| <> | not equal to | A1<>B1 |
| > | more than | A1>B1 |
| < | less than | A1 |
| >= | more than or equal to | A1>=B1 |
| <= | less than or equal to | A1<=B1 |
Use the correct logical operator in our logic condition input to get the correct result from our logic function!
5 Important Logic Functions in Excel and How to Use Them + Examples
While we can use many logic functions in excel, there are some we use more often than others. According to our experience, here are 5 of those more important logic functions. Mastering them should help you with most logic processing that you need to do in your excel work.IF
Not only one of the most important logic functions, IF is probably one of the most important functions in general too. We often use IF when we need to get a result based on a dynamic value.
IF concept is pretty simple. We input it with a logic condition and two results. The result we get from IF depends on whether the logic condition is TRUE or FALSE.
If the logic condition is TRUE, then we get one result. If the logic condition is FALSE, then we get another one. We may also need to nest IF within IF when we have multiple logic conditions we need to test.
Here is the general writing form of an IF formula in excel.
= IF ( logic_condition , result_if_true , result_if_false )
If you haven’t mastered how to use a basic IF formula yet, then you should learn that as soon as possible! Doing this can help you many times when you need to process your data optimally in excel.
Learn more about IF in its Compute Expert tutorial
AND
The AND function is more of a function to help us using IF more optimally. When we need to test multiple logic conditions simultaneously in IF, AND can let us do that easily. Just write an AND in your IF logic condition input part and input all those logic conditions into it.
The important thing that you should remember when using AND is its logic thinking process. AND will give you a TRUE if all your logic conditions are TRUE. If at least one of them is FALSE, then AND will give you a FALSE.
Here is the general writing form of an AND in excel.
= AND ( logic_condition1 , logic_condition2 , … )
Separate the logic conditions you input into AND with comma signs ( , ). As its result is a TRUE/FALSE logic value, we mostly use AND together with an IF in excel.
Learn more about AND in its Compute Expert tutorial
OR
If you need a different logic thinking process from AND when you test multiple logic conditions, you can use OR. OR will give you the opposite TRUE/FALSE result from AND.
If at least one of the multiple logic conditions you input into OR is TRUE, then OR will give you TRUE. If all multiple logic conditions you input into OR are FALSE, then OR will give you FALSE.
Thus, if you need to test multiple logic conditions in IF, you should understand how to use AND and OR. Select to use one of them depending on the TRUE/FALSE result you want from your multiple logic conditions testing.
The general writing form of OR is quite similar to AND.
= OR ( logic_condition1 , logic_condition2 , … )
By mastering AND and OR, you should be able to use IF in excel much more optimally!
Learn more about OR in its Compute Expert tutorial
IFERROR
When working in excel, we sometimes get an error from the formula that we write. The error can make our work goes awry as further data processing from the error will produce an error too.
To anticipate an error in excel and produce an alternative value, we can use the IFERROR function to help us. Input the formula we anticipate the error from to IFERROR and our alternative value to the error.
If the formula we input to IFERROR produces an error, then IFERROR will change it to that alternative value! This is often important if we use our formula result as a reference in another formula. We can also display our data processing results in excel much better too without any error values in it.
Here is the general writing form of an IFERROR in excel.
= IFERROR ( data_or_formula , error_alternative_value )
Whenever we need to anticipate an error value, just use IFERROR to help us out!
Learn more about IFERROR in its Compute Expert tutorial
IFNA
While IFERROR allows you to replace all types of errors, IFNA replaces only the #N/A error. This is useful if you want to identify whether your error is because your data isn’t there in your reference table.
When you know the data you look for isn’t available, you may want to add its reference in your reference table. That way, you can call it when you need it later in another cell. If you use IFERROR, then you won’t know whether the error is because of the data unavailability or something else!
The way to write IFNA in excel is quite similar to IFERROR.
= IFNA ( data_or_formula , na_error_alternative_value )
If you need to replace a #N/A error while also knowing whether your data is available or not, use IFNA!
Learn more about IFNA in its Compute Expert tutorial
Other Logic Functions in Excel
Besides the five logic functions that we have already discussed, excel has other logic functions you can utilize. Although this list may not be exhaustive, here are the other logic functions available to use in excel.- SWITCH: returns a value based on the matching scenarios we input in it
- NOT: converts TRUE into FALSE and FALSE into TRUE
- TRUE: gives you a TRUE logic value
- FALSE: gives you a FALSE logic value
- XOR: returns TRUE if there is an odd number of logic condition/value inputs in it that are TRUE. If it is even or zero, then XOR will return FALSE
- IFS (available since excel 2019): a more simple version of nested IFs. Returns a value based on multiple levels of logic condition test
In a common data processing situation, you should find yourself rarely use either one of them (unless for IFS, probably, if you use it to replace the nested IFs function since excel 2019 onwards).
Additional Note
- You can nest up to 64 IFs in one formula writing
- You can input up to 255 logic conditions/values into AND and OR
Related tutorials you should learn from:
