Chapter 29: Information Functions in Excel

Introduction to Information Functions
Information functions in Excel provide details about the type of data in cells, the properties of worksheets, and error handling. These functions are useful for debugging and managing your data effectively.

1. N

N

• Purpose: Converts non-numeric values to numbers, returning 0 for text and 1 for TRUE. Returns the number itself if it is numeric.
• Syntax: =N(value)
• Example: =N(“Hello”) returns 0, and =N(10) returns 10.

2. NA

NA

• Purpose: Returns the #N/A error value, which signifies that a value is not available.
• Syntax: =NA()
• Example: =NA() returns #N/A.

3. SHEET & SHEETS

SHEET

• Purpose: Returns the sheet number of the specified sheet.
• Syntax: =SHEET([reference])
• Example: =SHEET(Sheet2!A1) returns the sheet number of Sheet2.

SHEETS

• Purpose: Returns the total number of sheets in the workbook.
• Syntax: =SHEETS([reference])
• Example: =SHEETS() returns the total count of sheets in the workbook.

4. TYPE

TYPE

• Purpose: Returns a number indicating the type of data in a cell (1 for number, 2 for text, 4 for logical, etc.).
• Syntax: =TYPE(value)
• Example: =TYPE(10) returns 1, and =TYPE(“Text”) returns 2.

5. CELL

CELL

• Purpose: Returns information about the formatting, location, or contents of a cell.
• Syntax: =CELL(info_type, [reference])
• Info Types:
  • “address”: Returns the cell address.
  • “color”: Returns 1 if the cell is colored.
  • “contents”: Returns the content of the cell.
  • “format”: Returns the cell format.
• Example: =CELL(“address”, A1) returns the address of cell A1.

6. ERROR.TYPE

ERROR.TYPE

• Purpose: Returns a number corresponding to the error type in a cell (e.g., #DIV/0!, #VALUE!, etc.).
• Syntax: =ERROR.TYPE(error_val)
• Example: =ERROR.TYPE(#DIV/0!) returns 2.

7. INFO

INFO

• Purpose: Returns information about the operating environment or workbook.
• Syntax: =INFO(type)
• Info Types:
  • “directory”: Returns the current directory.
  • “os”: Returns the operating system.
  • “numfile”: Returns the number of open workbooks.
• Example: =INFO(“os”) returns the operating system of the computer.

8. Practice Assignment

1. Type Check:
   • Use TYPE to identify the data types of various cells in a range.
2. Error Handling:
   • Use ERROR.TYPE to handle and identify errors in a set of formulas.
3. Sheet Information:
   • Utilize SHEET and SHEETS to manage and reference worksheets in a workbook.
4. Cell Information:
   • Apply CELL to retrieve and display different properties of cells, such as address and format.
5. NA Function:
   • Use NA to handle cases where data might be missing or unavailable in your reports.

Pro Tip: Combining information functions with error handling can greatly simplify debugging and improve data integrity in your spreadsheets. Mastering these functions allows you to create more robust and error-resistant Excel models. Anjni Computer Education provides comprehensive exercises to enhance your understanding of these functions.