Microsoft Office Tutorials and References

In Depth Information

**Understanding logical functions**

TROUBLESHOOTING

Concatenated dates become serial numbers

If you try to concatenate the contents of a cell formatted as a date, the result is

probably not what you expect. Because a date in Excel is only a serial number, what you

usually see is a formatted representation of the date. But when you concatenate the

contents of a date-formatted cell, you get the unformatted version of the date. To

avoid this problem, use the TEXT function to convert the serial number to a

recognizable form. For example, suppose cell A1 contains the text
Today’s Date is
, and cell A2

contains the function =NOW() and is formatted to display the date in
dd/mm/yyyy

format. Despite the cell’s formatting, the formula =CONCATENATE(A1, " ", A2) results in

the value
Today’s Date is 41573
(or whatever the current date serial number happens to

be). To remedy this problem, use the TEXT function as follows:

=CONCATENATE(A1, " ", TEXT(A2, "dd/mm/yyyy")).

This version returns the value
Today’s Date is 10/26/2013
(or whatever today’s date

happens to be). Note that the formula includes a space character (" ") as a separate

argument between the two cell reference arguments.

Understanding logical functions

You use logical functions to test for specific conditions. These functions are often called

logical operators
in discussions of Boolean logic, which is named after George Boole, the

British mathematician. You might have run across logical operators in
set theory
, which is

used for teaching logical concepts in high school. You use logical operators to arrive at one

of two conclusions: TRUE or FALSE. We discuss the most useful logical functions in the

following sections. You can access the logical functions by clicking the Logical button on the

Formulas tab on the ribbon.

Using selected logical functions

Excel has a rich set of logical functions. Most logical functions use conditional tests to

determine whether a specified condition is TRUE or FALSE.

For more information about conditional tests, see “Creating conditional tests” in Chapter 12.

The IF function

The IF function returns values according to supplied conditional tests. It takes the

arguments (
logical_test, value_if_true, value_if_false
). For example, the formula =IF(A6<22, 5,

10) returns
5
if the value in cell A6 is less than 22; otherwise, it returns
10
. You can nest