Logical Functions in Excel (IF, AND, OR, XOR, IFERROR, IFNA, IFS, and SWITCH)

Logical functions are functions that allow you to test conditions and return values based on the results. In this blog post, we will learn how to use nine common logical functions in Excel: IF, AND, OR, XOR, NOT, IFERROR, IFNA, IFS, and SWITCH. 

IF Function

The IF function allows you to make decisions based on whether a condition is true or false. The IF function can also be nested inside another IF function, to test multiple conditions and return different values. 

Syntax of the IF function is: =IF(logical_test, value_if_true, value_if_false)

I have also applied conditional formatting for cells containing "F" values

AND Function

The AND function checks multiple conditions and returns TRUE if all of them are true, and FALSE if any of them are false. You can have up to 255 conditions in the AND function. 

Syntax of the AND function is: =AND(condition1, condition2, …) 

OR Function

The OR function checks multiple conditions and returns TRUE if any of them are true, and FALSE if all of them are false. You can have up to 255 conditions in the OR function. 

Syntax of the OR function is: =OR(condition1, condition2, …)

All these functions are not case-sensitive. meaning whether you use uppercase or lowercase (e.g., "YES" or "Yes") in formula, the results remain same.

XOR Function

The XOR function, stands for "exclusive or,". It returns TRUE if an odd number of conditions are TRUE and FALSE if an even number of conditions are TRUE. In other words, XOR evaluates multiple conditions and returns TRUE only when an odd number of these conditions are true. If an even number or none of the conditions are true, it returns FALSE. 

Syntax of the XOR function is: =XOR(condition1, condition2, …)

Student Data Example

IFERROR Function

The IFERROR function is a logical function that can be used to handle errors in formulas. It returns a value if the first argument is an error, and the value of the first argument otherwise. 

Syntax of the IFERROR function is: =IFERROR(value, value_if_error)

The value argument can be any value or expression that you want to test for errors. The value_if_error argument can be any value that you want to return if the value argument is an error.

Sales Data Example

IFNA Function

The IFNA function is a logical function that can be used to handle #N/A errors in formulas. It returns  the value of the first argument, otherwise a other specified value if the first argument is the #N/A error.

Syntax of the IFNA function is: =IFNA(value, value_if_na)

The "value_if_na" argument can be any value that you want to return if the first argument is the #N/A error.

IFS function

The IFS function is a logical function that checks multiple conditions and returns a value corresponding to the first true condition. It is a simplified version of the nested IF function, as it eliminates the need for multiple IF statements. You can use up to 127 pairs of logical tests and values in the IFS function. 

Syntax of the IFS function is: =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)

Student Data Example

SWITCH function

The SWITCH function is a logical function that evaluates an expression and returns a value based on a matching case. It is similar to the IFS function, but it uses exact matches instead of logical tests. You can use up to 126 pairs of values and results in the SWITCH function. The syntax of the SWITCH function is:

=SWITCH(expression, value1, result1, [default or value2, result2], ...)

Product Data Example


Conclusion

In this blog post, we have learned about some of the most common and useful logical functions in Excel, such as IF, AND, OR, XOR, IFERROR, IFNA, IFS, and SWITCH. We have seen how these functions can help us perform different actions or calculations with some examples of how to use these functions in real-life scenarios, such as assigning grades, calculating bonuses, identifying customers, handling errors, and more. Thank you for reading!

Comments

Post a Comment

Popular posts from this blog

XLOOKUP Function in Excel

Image
In our previous blog post we have already learned about 2 most useful lookup functions: VLOOKUP and HLOOKUP . In this blog post, we’ll explore what XLOOKUP is, its syntax, why you should use it, its pros and cons, and provide some practical examples. The  XLOOKUP  function revolutionize the way we search and retrieve data. XLOOKUP function is a modern and versatile replacement for traditional lookup functions in Excel. It allows you to find things in a table or range by row, regardless of whether the return column is on the left or right side. It’s like a search engine for your Excel data. Here’s what you need to know: Syntax : =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) Arguments : lookup_value : The value you want to search for. lookup_array : The range or array to search. return_array : The range or array from which to return results. [ if_not_found ] : (Optional) Specify what to return if no match is found. [ match_...
Read more

Introduction to Pivot Table in Excel - Advanced

Image
In the previous blog post, we learned the  Basics of Pivot Table  in Excel. In this blog post, we will explore some of the advanced features of pivot tables, such as slicers, timelines, calculated fields, pivot charts, managing relationships, etc. These features can help you change your existing pivot table and create different views of your data every time. Slicers Slicers are used to filter your data by selecting specific values from a field. For example in this data table , we have a column named Product (Crayons, Geometry Box, Paper, Pen, Stapler, Sticky Notes)  you can use a slicer to show only the data for a particular product or products.  Steps to add a Slicer: Select your pivot table ➡️ PivotTable Analyze ➡️ Click on "Insert Slicer" ➡️Choose the field for the slicer. A slicer window will appear, where you can select a field and a slicer will be inserted. You can select one option or multiselect by holding down the CTRL key while making your selections. You c...
Read more

Introduction to Pivot Table in Excel - Basics

Image
Pivot tables are one of the most powerful and useful features in Excel. Allowing us to quickly summarize, analyze, and explore large amounts of data in various ways. It is a special type of table that displays the summary of a data set and helps us in analyzing the data in seconds without knowing any formulas and functions. It consists of four areas: rows, columns, values, and filters. You can drag and drop different fields from your data set into these areas to create different views of your data. In this blog post, we will introduce the basics of pivot tables. Converting data to tabular data before inserting Pivot Table is an added advantage as this eliminates the need to frequently adjust the table range as new entries are added (select a cell from the data and click CTRL+T and then OK).  Additionally the following conditions need to be fulfilled: ✔ Each column should have unique headers, and the headers should be descriptive enough.       For ex. If Revenue...
Read more