Microsoft Office Tutorials and References
In Depth Information
Mastering Excel’s Built-in Functions
is required. If you omit the decimals argument, Excel displays the result using the
default of two decimal places.
● TEXT(value,format_text) When you want to display a number as text using a
custom format, use this function. The help text for this function is misleading. You can
apply any number format using the same syntax as entries in the Custom category in
the Format Cells dialog box. The format string must be enclosed in quotation marks.
Thus, if cell B5 contains 24.44, the formula =TEXT(B5,"#,##0_);[Red](#,##0)") returns
the text string 24.
If some cells in a column contain text and others contain numbers, you might want to use
the T(value) function, which checks the contents of the referenced cell. If the cell contains
text, the result of the function is that text; if the cell contains a value, the T function returns
an empty text string.
Finally, you can use the VALUE(text) function to convert text to a number. If the text
argument can’t be evaluated as a number (for example, if it contains even a single letter), the
function returns a #VALUE! error.
The functions in this group are some of the most powerful and useful in Excel. The IF
function, for example, allows you to test for a specific condition and then return a result based
on the answer to that test. Its syntax is IF(logical_test,value_if_true,value_if_false). In this
example, the formula in cell D2 uses a logical test of whether the invoice due date in B2 is
less than today’s date. If the result is true, it returns the text “Overdue,” and if the result is
false, it returns “Pending.”
You can combine two or more logical tests, often in conjunction with IF. The AND function
returns TRUE if all its arguments are true, and the OR function returns TRUE if any of its
arguments are true.