Na Function

Purpose

Return value

Syntax

=NA()

Using the NA function

The NA function returns the #N/A error. #N/A means “not available” or “no value available”. You can use the NA function to display the #N/A error when information is missing. Note that if you use the NA function this way, other formulas that depend on cells that contain the #N/A error will also display #N/A, unless you specifically trap and manage the error. The NA function takes no arguments.

Examples

The NA function returns the #N/A error:

=NA() // returns #N/A

You can use the NA function in other formulas. For example, in the formula below, the IF function is configured to test if cell A1 is empty. If so, IF returns NA(), which returns the #N/A error. If A1 is not empty, IF returns A1*B1:

=IF(A1="",NA(),A1*B1) // #N/A if A1 is empty

You can use the NA function to indicate missing information. In the worksheet shown above, cells C9 and C13 contain the NA function:

=NA()

This indicates that cost is not available. In cell D5, the formula copied down is:

=B5*C5 // qty * cost

In cells D9 and D13, the formula returns #N/A because C9 and C13 contain errors. In cell D15 the SUMIF function is used to sum values in column D while ignoring the #N/A error:

=SUMIF(D5:D13,"<>#N/A") // ignore #N/A

If the SUM function was used instead, it would return #N/A:

=SUM(D5:D13) // would return #N/A

Notes

• When other formulas refer to cells that contain #N/A, they also return #N/A.
• NA takes no arguments, but you must provide empty parentheses.
• You can also enter the value #N/A directly into a cell as text.

Purpose

Return value

Syntax

=SHEET([value])

• value - [optional] The value to check.

Using the SHEET function

The SHEET function returns the index number of a sheet in Excel. You can use the SHEET function to get a numeric index that represents the order of sheets in an Excel workbook, starting with 1 on the left and ending with N on the right, where N is the total number of sheets in the workbook. The SHEET function includes hidden sheets in the numbering sequence.

The SHEET function takes one argument, value , which should be a reference, a named range , or an Excel Table . Value is optional. When value is omitted, SHEET will return a numeric index for the current sheet (i.e. the sheet the formula exists in).

Examples

For example, in a workbook with Sheet1, Sheet2, and Sheet3 running left to right:

=SHEET(Sheet1!A1) // returns 1
=SHEET(Sheet2!A1) // returns 2
=SHEET(Sheet3!A1) // returns 3

If Sheet2 is dragged all the way to the left, a reference to A1 on Sheet2 will return 1:

=SHEET(Sheet2!A1) // returns 1

SHEET can report the sheet number for a cell reference, named range, or Excel Table . For example, if a table called “Table1” exists on the third sheet in a workbook the SHEET function will return 3:

=SHEET(Table1) // returns 3

Notes

• If the value argument is omitted, SHEET will return the index of the current sheet.
• SHEET includes hidden sheets in the numbering sequence.
• SHEET reports the index of a sheet. The SHEETS function reports the number of sheets .