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.

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.

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.

Click cell E2.

Type

`=IF(`.Click cell D2.

Type

`>50000,"Yes","No"``)`.Press Enter.

Click cell E2 and point to the fill handle.

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

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.

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")

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.

`TRUE` and `FALSE` return `TRUE` and `FALSE` as values.

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.

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` 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.