Microsoft Office Tutorials and References
In Depth Information
Understanding logical functions
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
Search JabSto ::

Custom Search