Determining a Value of a Cell

Summary: Much of Excel's worksheet data is maintained by internal numbers, regardless of what is displayed on the screen. You can uncover the underlying numbers by using the N worksheet function, described in this tip. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, and Excel 2003.)

You already know that a cell in a worksheet can contain any number of different items: numbers, dates, formulas, and so on. There may be times when you want to determine the underlying value in a cell, without regard to the way the cell is formatted. For this need, Excel provides the N worksheet function. For instance, let's assume that cell F17 contains a date. If you use = N(F17) as your formula, the value returned by the formula is the underlying serial number used for the date.

Besides returning date serial numbers, the N worksheet function returns a number if the referenced value or cell can be resolved to a number, a 1 if the value or cell can be resolved to the logical value True, and a 0 for anything else. The following provides a few examples of how the N worksheet function works:

Value in F17		Returned by = N(F17)
3/11/99		36230
37.14		37.14
TRUE		1
Quarter 1		0
5:40		0.236111

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2320) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Step Up and Take Control! Subscribers to ExcelTips know just how valuable a resource it is. ExcelTips Premium provides twice the number of exceptional, easy-to-understand tips every week in an ad-free newsletter, as well as substantial discounts on ExcelTips archives and e-books.

Check out ExcelTips Premium today!