In This Chapter
Getting information about a cell or range
Finding out about Excel or your computer system
Testing for numbers, text, and errors
In this chapter, we show you how to use Excel’s information functions, <C which you use to obtain information about cells, ranges, and the workbook you’re working in. You can even get information about the computer you’re using. What will they think of next!?
The information functions are great for getting formulas to focus on just the data that matters. Some functions even help shield you from Excel’s confusing error messages. The first time we saw the #NAME? error we thought Excel was asking for a name to be entered. Just another of the more exciting Excel moments. Now at least we know to use the ISERROR or ERROR.TYPE functions to make error messages more meaningful. And after reading this chapter, so will you!
Getting Informed with the CELL Function
The CELL function provides feedback about cells and ranges in a worksheet. You can find out what row and column a cell is in, what type of formatting it has, whether it’s protected, and so on.
CELL takes two arguments. The first argument, which is enclosed in double quotes, tells the function what kind of information to return. The second argument tells the function which cell or range to evaluate. If you specify a range that contains more than one cell, the function returns information about the top-left cell in the range. The second argument is optional; when it isn’t provided, Excel reports back on the most recently changed cell.
Table 15-1 shows the list of possible entries for the first argument of the CELL function.
| Table 15-1 | Selecting the First Argument for the CELL Function | |
| Argument | Example | Comment |
| address | =CELL (“address”) |
Returns the address of the last changed cell. |
| col | =CELL (“col”,Sales) |
Returns the column number of the first cell in the Sales range. |
| color | =CELL (“color”,B3) |
Tells whether a particular cell (in this case, cell B3) is formatted in such a way that negative numbers are repre sented in color. The number, currency, and custom formats have selections for displaying negative numbers in red. If the cell is formatted for color-negative numbers, a 1 is returned; otherwise, a 0 is returned. |
| contents | =CELL (“contents”,B3) |
Returns the contents of a particular cell (in this case, cell B3). If the cell contains a formula, returns the result of the formula and not the formula itself. |
| filename | =CELL (“filename”) |
Returns the path, filename, and work sheet name of the workbook and worksheet that has the Cell function in it (for example, C:\Customers\[Acme Company]Sheet1). Trying this out in a new workbook that has not yet been saved returns a blank answer. |
| format | =CELL (“format”,D12) |
Returns the number format of a cell (in this case, cell D12). See Table 15-2 for a list of possible returned values. |
| parentheses | =CELL (“parentheses”, D12) |
Returns 1 if a cell (in this case, cell D12) is formatted to have positive values, or all values, to have paren theses. Otherwise, 0 is returned. A custom format is needed to make parentheses appear with positive values in the first place. |
| Argument | Example | Comment |
| prefix | =CELL (“prefix”,R25) |
Returns the type of text alignment in a cell (in this case, cell R25). There are a few possibilities: a single quotation mark (‘) if the cell is left-aligned; a double quotation mark (“) if the cell is right-aligned; a carat (A) if the cell is set to centered; or a backslash(\) if the cell is fill-aligned. If the cell being evaluated is blank or has a number, then the function returns nothing. |
| protect | =CELL (“protect”,D12) |
Returns 1 if the protection of a cell (in this case, cell D12) is set to locked; otherwise, a 0 is returned. The returned value is not affected by whether the worksheet is currently protected. |
| row | =CELL (“row”,Sales) |
Returns the row number of the first cell in the Sales range. |
| type | =CELL (“row”,Sales) |
Returns a value corresponding to the type of information in a cell (in this case, cell D12). There are three possi ble values: b if the cell is blank; 1 if the cell has alphanumeric data; and v for all other possible values including num bers and errors. |
| width | =CELL (“width”) | Returns the width of the last changed cell, rounded to an integer. For example a width of 18.3 is returned as 18. |
The second argument, whether it’s there or not, plays a key role in how the CELL function works. When included, the second argument is either a cell address, such as B12, or a range name, such as Sales. Of course, you could have a range that is only one cell but let’s not confuse the issue!
If you enter a nonexistent range name for the second argument Excel returns the #NAME? error. Excel can’t return information about something that doesn’t exist!
An interesting way to use CELL is to keep track of what the last entry was on a worksheet. Let’s say you’re updating a list of values. The phone rings and you’re tied up for a while on the call. When you get back to your list, you’ve
forgotten where you left off. Yikes! What a time to think “If only I had used the CELL function!”
Figure 15-1 shows such a worksheet. Cell B18 displays the address of the last cell that was changed.
Figure 15-1:
Keeping track of which cell had the latest entry.
Using CELL with the filename argument is great for displaying the workbook’s path. This technique is common for printed worksheet reports. Being able to find the workbook file that a report was printed from six months ago is a real timesaver. Don’t you just love it when the boss gives you an hour to create a report, doesn’t look at it for six months, and then wants to make a change? Here’s how the CELL function is entered to return the filename:
You can format cells in many different ways. When the first argument to CELL is “format”, a code is returned that corresponds to the formatting. The possible formats are those found in the Format Cells dialog box. Table15-2 shows the formats and the code that CELL returns.
| Table 15-2 | Returned Values for the “Format” Argument |
| Format | Returned Value from CELL Function |
| General | G |
| 0 | F0 |
| Format | Returned Value from CELL Function |
| #,##0 | ,0 |
| 0.00 | F2 |
| #,##0.00 | ,2 |
| $#,##0_);($#,##0) | C0 |
| $#,##0_);[Red]($#,##0) | C0- |
| $#,##0.00_);($#,##0.00) | C2 |
| $#,##0.00_);[Red]($#,##0.00) | C2- |
| 0% | P0 |
| 0.00% | P2 |
| 0.00E+00 | S2 |
| # ?/? or | G |
| m/d/yy or m/d/yy h:mm or mm/dd/yy | D4 |
| d-mmm-yy or dd-mmmm-yy | D1 |
| d-mmm or dd-mmm | D2 |
| mmm-yy | D3 |
| mm/dd | D5 |
| h:mm AM/PM | D7 |
| h:mm:ss AM/PM | D6 |
| h:mm | D9 |
| h:mm:ss | D8 |
Using CELL with the “format” argument lets you add a bit of smarts to your worksheet. Figure 15-2 shows an example where CELL makes sure information is correctly understood. The dates in Column A are of the d-mmm format. The downside of this format is that the year is not known. So, cell A1 has been given a formula that uses CELL to test the format of the dates. If the d-mmm format is found in the first date (in cell A4) then cell A1 displays a message that includes the year from cell A4. After all, cell A4 has a year — it’s just formatted not to show it. This way the year is always present — either in the dates themselves, or at the top of the worksheet.
Figure 15-2:
Using CELL and the “format” argument to display a useful message.
This formula is in Cell A1:
This formula says that, if the formatting in A4 is d-mmm (according to the values in Table 15-2), then display the message with the year; otherwise, just display Receipts.
Here’s how to use the CELL function:
1. Position the cursor in the cell where you want the results to appear.
2. Enter =CELL( to begin the function entry.
3. Enter one of the first argument choices listed in Table 15-1.
Make sure to surround it with double quotes (” ” ).
4. If you want to tell the function which cell or range to use, enter a comma (,).
5. If you want, enter a cell address or the name of a range.
6. Enter a closing parenthesis to end the function, and press Enter.
Getting Information about Excel and Your Computer System
Excel provides the INFO function to get information about your computer and about Excel. INFO takes a single argument that tells the function what type of information to return. Table 15-3 shows how to use the INFO function.
| Table 15-3 | Using INFO to Find Out about Your Computer or Excel | |
| Argument | Example | Comment |
| directory | = INFO (“directory”) |
Returns the path of the current directory. Note that this is not necessarily the same path of the open workbook. |
| memavail | = INFO (“memavail”) |
Returns the amount of available memory in bytes. |
| memused | = INFO (“memused”) |
Returns the amount of memory, in bytes, being used at the time the function is run. The more applications and/or files that are open, the higher the number will be. |
| numfile | = INFO (“numfile”) |
Returns the number of work sheets in all open workbooks. This number includes worksheets of add-ins, so the number could be misleading. |
| origin | = INFO (“origin”) | Returns the address of the cell that is at the top and left of the scrollable area. An A$ prefix is put in front of the cell address for compatibility with Lotus 1-2-3. |
| osversion | = INFO (“osversion”) |
Returns the name of the current operating system. |
| recalc | = INFO (“recalc”) | Returns the status of the recalculation mode: either Automatic or Manual. |
| release | = INFO (“release”) |
Returns the version number of Excel being run. |
| system | = INFO (“system”) | Returns the name of the operating environment: either mac or pcdos. |
| totmem | = INFO (“totmem”) | Returns the sum of the available memory and the used memory. |
One useful application of the INFO function is to use the returned Excel version number to determine whether the workbook can use a newer feature. For example the ability to work with XML data has only been available in
Excel 2002 and later. By testing the version number, a user can be notified whether he can work with XML data. This formula uses the “release” choice as the argument:
Figure 15-3 shows values returned with the INFO function. The information shows facts about computer. Please, no comments on how much memory he has!
Figure 15-3:
Getting facts about the computer with the INFO function.
Here’s how to use the INFO function:
1. Position the cursor in the cell where you want the results to appear.
2. Enter =INFO( to begin the function entry.
3. Enter one of argument choices listed in Table 15-3. Make sure to surround it with double quotes (” “).
4. Enter a closing parenthesis to end the function, and press Enter.
Finding Out What Is and What Is Not
A handful of IS functions report back a True or False answer about certain cell characteristics — for example, is a cell blank or does it contain text. These functions are often used in combination with other functions, typically the IF function, to handle errors or other unexpected or undesirable results.
The errors Excel reports are not very friendly. What on earth does “#N/A” really tell you? Well, the functions we describe in this section won’t make the error any clearer, but they give you a way to instead display a message that’s friendly, like, “Something is wrong, but I don’t know what it is.”
Table 15-4 shows the IS functions and how they’re used. They all return either True or False, so the table just lists them.
| Table 15-4 Using the IS Functions to See What Really Is | |
| Function | Comment |
| =ISBLANK(value) | Tells whether a cell is blank. |
| =ISERR(value) | Tells whether a cell contains any error, other |
| than the #N/A error. | |
| =ISERROR(value) | Tells whether a cell contains any error. |
| =ISLOGICAL(value) | Tells whether value is logical. |
| =ISNA(value) | Tells whether a cell contains the #N/A error. |
| =ISNONTEXT(value) | Tells whether a cell contains a number or error. |
| =ISNUMBER(value) | Tells whether a cell contains a number. |
| =ISREF(value) | Tells whether value is a reference. |
| =ISTEXT(value) | Tells whether a cell contains text. |
Let’s look at some of these functions. First, three of the IS functions tell you
about an error: ISERR, ISERROR, and ISNA.
| Error Function | Comments |
| ISERR | Returns True if the error is anything except |
| the #N/A error. For example the #DIV/0! Error | |
| returns True. | |
| ISNA | This function is the opposite of the ISERR func- |
| tion. It only returns True if the error is #N/A. | |
| ISERROR | Returns True for any type of error. This |
| includes #N/A, #VALUE!, #REF!, DIV/0!, #NUM!, | |
| #NAME?, and #NULL! |
Why is #N/A treated separately? It is excluded from being handled with ISERR, and has its own ISNA function. Actually #N/A can be used to your advantage to avoid errors. How so? Figure 15-4 shows an example that calculates the percentage of surveys returned for some of Florida’s larger cities. The calculation is simple — just divide the returned number by the number sent.
However, errors do creep in (creepy errors, yuck!). For example, no surveys were sent to Gainesville, yet 99 came back. Interesting! The calculation becomes a division by zero error, which makes sense.
On the other hand, Tallahassee had no surveys sent, but here the returned value is the #N/A error, purposely entered. Next look at Column E. In this column, True or False is returned to indicate whether the calculation, per city, should be considered an error — Gainesville true, Tallahassee false.
TRUE or FALSE appears in Column E because all the cells in Column E use the ISERR function. The formula in cell E13, which tests the calculation for Tallahassee is this:
Simply put, D13 displays the #N/A error because its calculation (=C13/B13) uses a cell with an entered #N/A. The ISERR does not consider #N/A to be an error; therefore, E13 returns False. The upshot to all this is that eyeballing Column E makes distinguishing entry and math errors from purposeful flagging of certain rows as having incomplete data easy.
Figure 15-4:
Putting an error to your advantage.
The ISBLANK, ISNONTEXT, ISTEXT, and ISNUMBER functions tell you what type of data is in a cell. Actually ISBLANK returns True when nothing is in a cell. Using ISBLANK is useful to count how many cells in a range are blank. Perhaps you’re responsible for making sure that 200 employees get their timesheets in every week. You can use a formula that lets you know how many employees have not yet handed in their hours.
Such a formula uses ISBLANK along with the IF and SUM functions, like this:
This formula makes use of an array. See Chapter 3 for more information on using array formulas. Figure 15-5 shows how this formula works. In columns A and B are long lists of employees and their hours. The formula in cell A1 reports how many employees are missing their hours.
ISTEXT returns True when a cell contains any type of text. ISNONTEXT returns True when a cell contains anything that is not text, including numbers, dates, and times. The ISNONTEXT function also returns True if the cell contains an error.
Figure 15-5:
Calculating how many employees are missing an entry.
The ISNUMBER function returns True when a cell contains a number, which can be an actual number or a number resulting from evaluation of a formula in the cell. You can use ISNUMBER as an aid to help data entry. Let’s say you designed a worksheet that people use to fill out. One of the questions is their age. Most people would enter a numeric value such as 18, 25, 70, and so on. But someone could type in the age as text, such as “eighteen” or “thirty-two” or maybe even “none of your business.” An adjacent cell could use ISNUM-BER to return a message about entering the numeric age. The formula would look something like this:
Here’s how to use any of the IS functions:
1. Position the cursor in the cell where you want the results to appear.
2. Enter one of the IS functions, for example, enter =ISTEXT( to begin the function entry.
3. Enter a cell address.
4. Enter a closing parenthesis to end the function, and press Enter.
The result is always either True or False.
Getting to Know Your Type
The TYPE function tells you what the type of the information is. Possible types are a number, text, a logical value, an error, or an array. In all cases TYPE returns a number:
1 is returned for numbers.
2 is returned for text.
4 is returned for logical values. 16 is returned for errors. 64 is returned for arrays.
Figure 15-6 shows each of these values returned by the TYPE function. Cells B3:B7 contain the TYPE function, with each row looking at the adjacent cell in Column A. The returned value of 64 in cell B7 is a little different. This indicates an array as the type. The formula in cell B7 is =TYPE(A7:A9). This is an array of values from cells A7:A9.
Figure 15-6:
Getting the type of the data.
Here’s how to use the TYPE function:
1. Position the cursor in the cell where you want the results to appear.
2. Enter =TYPE( to begin the function entry.
3. Enter a cell address, or click on a cell.
4. Enter a closing parenthesis to end the function, and press Enter.
The ERROR.TYPE function returns a number that corresponds to the particular error in a cell. Table 15-5 shows the error types and the returned numbers.
| Table 15-5 | Getting a Number of an Error |
| Error Type | Returned Number |
| #NULL! | 1 |
| #DIV/0! | 2 |
| #VALUE! | 3 |
| #REF! | 4 |
| #NAME? | 5 |
| #NUM! | 6 |
| #N/A | 7 |
The best thing about the ERROR.TYPE function is that you can use it to change those pesky errors into something readable! To do this, use the
CHOOSE function along with ERROR.TYPE, like this:
See Chapter 14 for assistance on using the CHOOSE function. This is how you
use the ERROR.TYPE function:
1. Position the cursor in the cell where you want the results to appear.
2. Enter =ERROR.TYPE( to begin the function entry.
3. Enter a cell address, or click on a cell.
4. Enter a closing parenthesis to end the function, and press Enter.
