Boolean Logic Guide
Excel Boolean logic formulas are an area worth revising before writing your first wrap. Wraps use them extensively but they are not so common in general spreadsheets. The notes below will help you get to grips with Boolean logic. There are 5 key Boolean Logic formulas:
|
Function |
Description |
Formula Example |
Formula Description |
|
AND |
Returns TRUE if all of the arguments evaluate to TRUE |
=AND(A2>=10,B2<5) |
The formula returns TRUE if a value in cell A2 is greater than/equal to 10, and a value in B2 is less than 5, FALSE otherwise |
|
OR |
Returns TRUE if any argument evaluates to TRUE. |
=OR(A2>=10,B2<5) |
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 are met, the formula returns FALSE |
|
IF |
Use the IF function to test a condition (known as the logical_test) and return one value if the condition is met (value_if_true) and another value if the condition is not met (value_if_false). |
=IF(A2>80.”Pass”,”Fail”) |
The formula returns “Pass” if the logical function is proved to be true: the value in A2 is greater than 80. If the value in A2 was 80 or lower the function would return “Fail”. |
|
XOR |
Returns a logical Exclusive Or of all arguments |
=XOR(A2>=10,B2<5) |
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 |
|
NOT |
Returns the reversed logical value of its argument eg: if the argument is FALSE, then TRUE is returned |
=NOT(A2>=10) |
The formula returns FALSE if a value in cell A2 is greater than or equal to 10; TRUE otherwise |
Building Nested IF Formula
It is possible to use more than one function at a time by using a nested function. A nested function uses multiple functions however it still adheres to the formulas shown above. With long, difficult logical functions we recommend building them step by step and look out for Excel’s colour coding of formulas and parentheses to help understand the syntax of each condition within a formula.
=IF(A1=2,”Two”,”None”) Here we have a simple IF statement as shown above. It uses the 3 elements: logical test, value if true, value if false
=IF(A1=1,”One”,”None”) Again we have another IF statement and combining the two into a nested function is very straightforward. Use the same syntax of simple IF statement but replace one of the values with another logic function
=IF(A1=1,”One”,IF(A1=2,”Two”,”None”)) This has combined the two IF statements into one.
Multiple Logic Condition IF Formula
=IF((AND(A2>5, B2>10)), "Pass", "Fail")
In this example the logical test uses the AND function to include two conditions. The formula would give a “Pass” value if the value in A2 was greater than 5 AND the value in B2 was greater than 10. Otherwise the function would return the value of “Fail”.
Concatenated IF Formulas
=IF(A2=”A”,”Outstanding”) & IF(A2=”B”,"Very Good") & IF(A2=”C”,”Good”)& IF(A2=”D”,”Fail”)
In this example we use a CONCATANATED IF function to give a variety of value responses depending on what letter is in cell A2.
Concatenated IF Statement
=IF(OR(signed(ProjectComplete),signed(CompletedSignature)),0,1)
This complicated looking equation is very simple to break down and uses two functions; the IF and OR as well as the signed function which allows you to take advantage of ExcelWraps WrapSignoff feature. This function states that IF the wrap has been signed as “Project Complete” OR signed “Instruction to Decline” the value of 1 will be given. However if neither of these functions have been met the value of 0 would be given.
Common Error Terms
Please Note If an empty cell is included in a logical function the value is ignored. If all arguments are empty cells formula returns #VALUE! error. If a functions name is misspelt a #NAME! error would be displayed.