Logical Functions

Excel Logical Functions check the data and return the result «TRUE» if the condition is true, and «FALSE» if not.

Check multiple cells are equal in Excel

To confirm two ranges of the same size contain the same values, you can use a simple array formula based on the AND function. See illustration below: Formula {=AND(range1=range2)} Explanation In the example shown, the formula in C9 is: {=AND(B5:D12=F5:H12)} Note: this is an array formula and must be entered with control + shift + enter. How… read more »

Return blank if in Excel

This tutorial shows how to calculate Return blank if in Excel using the example below; Formula =IF(A1=1,B1,””) Explanation To return a blank result using the IF function, you can use an empty string (“”).  In the example shown, the formula in D5 (copied down) is: =IF(B5=1,C5,””) How this formula works This formula is based on the IF function,… read more »

Nested IF function example in Excel

This tutorial shows how to calculate Nested IF function example in Excel using the example below; Formula =IF(T1,R1,IF(T2,R2,IF(T3,R3,IF(T4,R4,R5)))) Explanation In the code above, T1-T5 represents 5 different logical tests, and R1-R5 represents 5 different results. You can see that each IF function requires it’s own set of parentheses. This article describes the Excel nested IF construction. Usually,… read more »

Invoice status with nested if in Excel

This tutorial shows how to calculate Invoice status with nested if in Excel using the example below; Formula =IF(balance=0,”Paid”,IF(current_date<due_date,”Open”,”Overdue”)) Explanation To determine invoice status (i.e. paid, open, overdue), you can use a nested IF formula and the TODAY function. In the example shown, the formula in G5 is: =IF(F5=0,”Paid”,IF(TODAY()<C5,”Open”,”Overdue”)) How this formula works Note: the “current date”… read more »

IF with boolean logic in Excel

This tutorial shows how to calculate IF with boolean logic in Excel using the example below; Formula = IF(criteria1*criteria2*criteria3,result) Explanation In the example shown, the formula in F8 is: {=SUM(IF((color=”red”)*(region=”East”)*(quantity>7),quantity))} Note: this is an array formula, and must be entered with control + shift + enter. How this formula works Note: This example demonstrates how to replace a nested… read more »

IF with wildcards in Excel

This tutorial shows how to calculate IF with wildcards in Excel using the example below; Formula =IF(COUNTIF(A1,”??-????-???”),””,”invalid”) Explanation The IF function doesn’t support wildcards, but you can combine IF with COUNTIF or COUNTIF to get basic wildcard functionality. In the example shown, the formula in C5 is: =IF(COUNTIF(B5,”??-????-???”),””,”invalid”) How this formula works Unlike several other frequently used functions,… read more »

How to use Excel FALSE Function

This Excel tutorial explains how to use the Excel FALSE function with syntax and examples. Excel FALSE Function Description The Excel False function returns the logical value FALSE. The function takes no arguments and therefore, the syntax is simply: FALSE() Note that you can also get the same result by simply typing the text ‘False’ into your… read more »

How to use Excel XOR Function

This Excel tutorial explains how to use the XOR function with syntax and examples. Excel XOR Function Function Description Excel XOR function performs what is called “exclusive OR”. The XOR function returns the Exclusive Or logical operation for one or more supplied conditions. With two logical statements, XOR returns TRUE if either statement is TRUE, but returns… read more »

How to use Excel TRUE Function

This Excel tutorial explains how to use the TRUE function with syntax and examples. Excel TRUE Function Description The Excel True function returns the logical value TRUE. The function takes no arguments and therefore, the syntax is simply: TRUE() Note that you can also get the same result by simply typing the text ‘True’ into… read more »

How to use Excel OR Function

This Excel tutorial explains how to use the  OR function with syntax and examples. Excel OR Function Description The Excel OR function tests a number of supplied conditions and  returns TRUE if any of the conditions are TRUE. Otherwise, it returns FALSE. That is; TRUE if ANY of the conditions evaluate to TRUE or FALSE otherwise (i.e. if ALL of the conditions… read more »

Sidebar