Page 438 - Excel 2013 All-in-One For Dummies

420 Information, Please . . . Information, Please . . .
The information functions on the continuation menu accessed by clicking the More Functions command button on the Formulas tab of the Ribbon and then highlighting the Information option (or by pressing Alt+MQI) consist
of a number of functions designed to test the contents of a cell or cell range and give you information on its current contents.
These kinds of information functions are often combined with IF functions, which determine what type of calculation, if any, to perform. The informa- tion function then becomes the logical_test argument of the IF function, and the outcome of the test, expressed as the logical TRUE or logical FALSE value, decides whether its value_if_true or its value_if_false argument is executed. (See Book III, Chapter 2 for information on using information func- tions that test for error values to trap errors in a spreadsheet.)
In addition to the many information functions that test whether the contents of a cell are of a certain type, Excel offers a smaller set of functions that return coded information about a cell’s contents or formatting and about the current operating environment in which the workbook is functioning. The program also offers an N (for Number) function that returns the value in a cell and an NA (for Not Available) function that inserts the #N/A error value in the cell.
Getting specific information about a cell
The CELL function is the basic information function for getting all sorts of data about the current contents and formatting of a cell. The syntax of the CELL function is
CELL(info_type,[reference])
The info_type argument is a text value that specifies the type of cell informa- tion you want returned. The optional reference argument is the reference of the cell range for which you want information. When you omit this argument, Excel specifies the type of information specified by the info_type argument for the last cell that was changed in the worksheet. When you specify a cell range as the reference argument, Excel returns the type of information specified by the info_type argument for the first cell in the range (that is, the one in the upper-left corner, which may or may not be the active cell of the range).
Table 6-1 shows the various info_type arguments that you can specify when using the CELL function. Remember that you must enclose each info_type argument in the CELL function in double-quotes (to enter them as text values) to prevent Excel from returning the #NAME? error value to the cell containing the CELL function formula. So, for example, if you want to return the contents of the first cell in the range B10:E80, you enter the following formula:
=CELL(“contents”,B10:E80)