Logical Functions

Logical functions are used to create logical tests. A test enables a formula to make a decision based on particular data. A test can determine whether a value is greater than 25 , and the formula making the test can perform one function if true, and another if false.

IF

The most common and useful logical function is the IF function, which allows you to develop several kinds of tests based on the operators used in the test statement. IF is often combined with other logical functions to create more specific tests. The syntax for the IF function looks like this:

 =IF(condition,value if true,value if false) 

The IF function tests that a condition is true or false. If the condition proves true, one value is returned. If the condition proves false, another value is returned. To prove a condition true or false requires a relational operator. Excel offers several:

• > Is greater than

• < Is less than

• = Is equal to

• >= Is greater than or equal to

• <= Is less than or equal to

• <> Is not equal to

In the following example, you can substitute any of these operators for the one given:

 =IF(A1=A2,"Right","Wrong") 

If the value of A1 is equal to that of A2 , the formula returns Right . Otherwise , the formula returns Wrong . The value if true and the value if false can be any constant value, cell reference, or formula.

Perform the steps in the upcoming To Do exercise to build a logical formula to test a condition and show results with the words Yes and No . Then you copy the formula to a column to test the condition for all the sales data. The results in the last column show either the word Yes or No in each cell that contains a logical formula. You start by entering data in a blank worksheet to prepare for building a logical formula.

To Do: Build a Logical Formula

1. Click the Sheet2 tab. Enter the data into Sheet2 to match the data in the worksheet shown in Figure 15.8. You need to widen column C and format the numbers in column D with the Comma Style with zero (0) decimal places.

   Figure 15.8. Sales quota data for building a logical formula.

   

2. Click cell E2.

3. Type =IF( .

4. Click cell D2.

5. Type >50000,"Yes","No" ) .

6. Press Enter.

7. Click cell E2 and point to the fill handle.

8. Drag the fill handle to copy the logical formula down to cell E8.

9. Click any cell to deselect the range.

   Excel shows the word Yes or No in each cell that contains a logical formula, as shown in Figure 15.9.

   Figure 15.9. Logical formula results.

   

ISBLANK

This function tests whether a cell is blank. If the specified cell is blank, a value of TRUE is returned; otherwise, FALSE is returned. The referenced cell can be any valid cell in the worksheet. The ISBLANK function is commonly used with the IF function to test for a blank cell and then perform some action based on the outcome. For example, you can use ISBLANK with IF to print a message next to cells that need to be filled in and then remove the message after the data is entered.

The ISBLANK function's format is

 =ISBLANK (cell) 

The corresponding formula follows :

 =IF(ISBLANK(B5) = FALSE, "", "Please enter the amount in cell B5") 

ISERR

This function tests whether a specified cell contains an error. If so, then a value of TRUE is returned. Otherwise, FALSE is returned. The ISERR function is commonly used with the IF function to "trap" errors in the worksheet and allow control over the result of the error. Normally, any calculation that references a cell containing an error causes #VALUE! or some other error message to be returned. But using the ISERR function, you can pinpoint the error.

The syntax for the ISERR function is

 =ISERR(cell) 

For example:

 =IF(ISERR(B5) = TRUE, "Invalid entry in cell B5", B5*B6) 

This formula tests whether the value of B5 is an error. If so, the phrase Invalid entry in cell B5 is returned. Otherwise, the desired calculation is performed.

Is It True or False?

TRUE and FALSE return TRUE and FALSE as values.

AND

The AND logical function returns the value TRUE if all of its arguments are true; otherwise it returns the value FALSE . The syntax for the AND function is

 =AND(Condition 1,Condition 2...) 

You can test up to 30 conditions with the AND function. Typically, you use the AND function with the IF function to return a value based on more than one condition.

For example, there are only two possible grades for marking a student mid-term and final term papers: P (Pass) and F (Fail). To pass the course, a student must have a mark that is >=50 for each term paper. The AND function combined with the IF function would look like this:

 =IF(AND(B2>=50,C2>=50,"P","F") 

Cell B2 contains 51 and cell C2 contains 95. The AND function tests whether both marks satisfy this condition. If both marks are >=50, the return value of the AND function will be TRUE , and the IF function's value of P (Pass) will appear in the cell. Otherwise the return value of the AND function will be FALSE , and the IF function's value of F (Fail) will appear in the cell.

OR

The OR logical function returns TRUE if one or more of its arguments is true; otherwise it returns FALSE . The syntax for the OR function is

 =OR(Condition 1,Condition 2...) 

You can test up to 30 conditions with the OR function. Typically, you use the AND function with the IF function to return a value based on more than one condition.

Typically, you use the AND function with the IF function to return a value based on more than one condition.

For example, there are only two possible grades for marking a student mid-term and final term papers: P (Pass) and F (Fail). To pass the course, a student must have a mark that is >=50 for either term paper. The OR function combined with the IF function would look like this:

 =IF(OR(B2>=50,C2>=50,"P","F") 

Cell B2 contains 51 and cell C2 contains 95. The OR function tests whether either mark satisfies this condition. If either mark is >=50, the return value of the OR function will be TRUE , and the IF function's value of P (Pass) will appear in the cell. Otherwise the return value of the OR function will be FALSE , and the IF function's value of F (Fail) will appear in the cell.

NOT

NOT reverses the logic of an argument. True arguments become false and vice versa. The format of the NOT function looks like

 =NOT(logical) 

The NOT function returns the value TRUE if the logical argument is false. Conversely, the NOT function returns the value FALSE if the logical argument is true. Use the NOT function when you want to make sure a value is not equal to one particular value.

Suppose you are told that you need to create a formula that does something if the city is Boston or New York. An approach would be to specify that you're interested in cities that are not Denver. This way, all cities that are not Denver will return TRUE . Here's an example of using the NOT function:

 =Not(B5="New York") 

The NOT function returns TRUE if B5 contains anything except New York.