Excel logical functions
Vinayak Jadhav
?? Certified Microsoft Power BI Data Analyst Associate?? Certified Microsoft Office Specialist? Non IT Professional Driving Digital Transformation ?? Generate your several Income sources
Overview
Microsoft Excel provides 4 logical functions to work with the logical values. The functions are AND, OR, XOR and NOT. You use these functions when you want to carry out more than one comparison in your formula or test multiple conditions instead of just one. As well as logical operators, Excel logical functions return either TRUE or FALSE when their arguments are evaluated.
The following table provides a short summary of what each logical function does to help you choose the right formula for a specific task.
Function
Description - Returns TRUE if all of the arguments evaluate to TRUE.
Formula Example =AND(A2>=10, B2<5)
Formula Description - The formula returns TRUE if a value in cell A2 is greater than or equal to 10, and a value in B2 is less than 5, FALSE otherwise.
Description - Returns TRUE if any argument evaluates to TRUE.
Formula Example =OR(A2>=10, B2<5)
Formula Description - The formula returns TRUE if A2 is greater than or equal to 10 or B2 is less than 5, or both conditions are met. If neither of the conditions it met, the formula returns FALSE.
Description - Returns a logical Exclusive Or of all arguments.
领英推荐
Formula Example =XOR(A2>=10, B2<5)
Formula Description - The formula returns TRUE if either A2 is greater than or equal to 10 or B2 is less than 5. If neither of the conditions is met or both conditions are met, the formula returns FALSE.
Description - Returns the reversed logical value of its argument. I.e. If the argument is FALSE, then TRUE is returned and vice versa.
Formula Example =NOT(A2>=10)
Formula Description - The formula returns FALSE if a value in cell A1 is greater than or equal to 10; TRUE otherwise.
?
In additions to the four logical functions outlined above, Microsoft Excel provides 3 "conditional" functions - IF,?IFERROR?and?IFNA.
Excel logical functions - facts and figures
1.????? In arguments of the logical functions, you can use cell references, numeric and text values, Boolean values, comparison operators, and other Excel functions. However, all arguments must evaluate to the Boolean values of TRUE or FALSE, or references or arrays containing logical values.
2.????? If an argument of a logical function contains any?empty cells, such values are ignored. If all of the arguments are empty cells, the formula returns #VALUE! error.
3.????? If an argument of a logical function contains numbers, then zero evaluates to FALSE, and all other numbers including negative numbers evaluate to TRUE. For example, if cells A1:A5 contain numbers, the formula =AND(A1:A5) will return TRUE if none of the cells contains 0, FALSE otherwise.
4.????? A logical function returns the #VALUE! error if none of the arguments evaluate to logical values.
5.????? A logical function returns the #NAME? error if you've misspell the function's name or attempted to use the function in an earlier Excel version that does not support it. For example, the XOR function can be used in Excel 2016 and 2013 only.
In Excel 2007 and higher, you can include up to 255 arguments in a logical function, provided that the total length of the formula does not exceed 8,192 characters. In Excel 2003 and lower, you can supply up to 30 arguments and the total length of your formula shall not exceed 1,024 characters.