Excel Formula Tutorial IF AND OR NOT TRUE FALSE
Using Excels Logical Functions – A Step By Step Training Guide
Aug 7, 2009
There are six logical formulae in Excel that may be used to summarise the information in a spreadsheet. They are amongst the easiest of all the Excel functions to use, but can be used to great effect when combining the information in several other cells.
Excel Logical Comparison Operators – “=”, “>”, “<”, “<=”, “>=”, “<>”
Before attempting to use the logical formulae in Excel, it is important to know what the basic comparison operators are. These are listed here, as they are used extensively in this article.
Meaning ……………..… Symbol
Equals ………………..… =
Greater than ………...…. >
Less than …………...…. <
Greater than or equal to >=
Less than or equal to ….<=
Not equal to ……………..<>
Excel Logical Functions – IF, AND, OR, NOT, TRUE and FALSE Examples
Each Excel formula is shown as an example, and followed by a statement of what is achieved:
=IF(A2>=A1, 0.5, ”N/A”)
Return 0.5 if the value in cell A2 is greater than or equal to the value in cell A1, and N/A if it is not.
=AND(A3=A2, A1<>C9)
Return TRUE if the value in cell A3 equals the value in cell A2, AND the value in cell A1 is not equal to the value in C9. It is possible to use up to 30 conditions.
=OR(A1=2, A2<B3)
Return TRUE if the value in cell A1 equals 2, OR if the value in cell A2 is less than the value in cell B3. It is possible to use up to 30 conditions.
=NOT(A1>A2)
Return FALSE if the statement in brackets (the value in cell A1 is greater than the value in cell A2) is TRUE, and return TRUE if the statement in brackets is FALSE.
TRUE is a reserved word in Excel. It may be treated like the number 1 for most purposes. For example, if cell A1 contains TRUE, then the formula
=1*A1
will return the value 1.
FALSE is a reserved word in Excel. It may be treated like the number 0 for most purposes. For example, if cell A1 contains FALSE, then the formula
=1*A1
will return the value 0.
Excel Logical Formula Short Cuts
Once the Excel logical formulae are understood, and knowing that TRUE = 1 and FALSE = 0, it is possible to substitute the AND statement as follows:
=AND(A3=A2, A1<>C9) is the same as
=(A3=A2)*(A1<>C9)
The only difference is that the first formula returns TRUE, and the second returns 1. Having the value 1 instead of TRUE is usually better, especially if the data needs to be summarised: it is possible to SUM, AVERAGE, COUNTIF etc if the values are 0 or 1, but not if they are TRUE or FALSE.
Step By Step Example of Excel Logic Functions
Figure 1 shows a list of job applicants. The recruitment criteria are Age, Height, Health Score, and whether they have a Criminal Record. The formula for the criteria could be:
Age: =IF(AND(C2>=18,C2<65),TRUE,FALSE)
Height: =OR(AND(B2="M",D2>=170,D2<210),AND(B2="F",D2>=165,D2<210))
Health: =IF(E2<3,TRUE,FALSE)
Criminal Record: =NOT(F2="Y")
Overall: =AND(G2=TRUE,H2=TRUE,I2=TRUE)
The height criteria are different for men and women, and the formula returns a TRUE if the subject is male between 170 – 210 cm, OR a female in the height range 165 – 210 cm.
Summary of Excel Logical Formulae
The functions supplied in Excel to compare cell values are extremely useful. They are easy to use, and can be made to return logical (TRUE / FALSE) values or numeric (0 and 1). Using numeric values allows further processing of summarising.
More information about Excel functions is available free on-line, or in Excel for Dummies by Greg Harvey.
 |
 |
