JohnKauffman
New Member
- Joined
- Nov 1, 2012
- Messages
- 36
I'm writing up a note on #N/A for my students (adult office workers). I condensed the answers to students' questions into 5 paragraphs. I would appreciate any comments about mistakes or potential logical problems.
Much thanks, John.
[h=1]#N/A – An Introduction[/h]A display of #N/A means not available with the hash implying there is an error. It signifies there is nothing in this cell (not even a blank) available for use by any other feature of Excel, including formulas and charts. Display of #N/A can come from two sources: intentionally typed into the cell or result of a formula error.
Intentionally typing an N/A overcomes the possibility that a blank in the data was a mistake of omission by the data entry clerk. The ambiguity is resolved by inputting an N/A in the cell to indicate the cell is intentionally empty. N/A is the preferred technique over leaving the cell blank or a place-holder like zero or the empty string "". In this case N/A is used intentionally so the hash mark displayed as an error prefix is misleading.
A formula error that displays N/A can result from an argument that points to a value that the function cannot use. A very common example is vlookup() to a table that is not properly organized. Any formula will return an N/A if it has a precedent of a cell holding N/A (by inheritance of errors). In this case the hash mark displayed as an error prefix is not confusing.
The only way to type an N/A in a cell is by typing the formula =NA(). The clerk cannot type the letters N/A because that creates a string. An N/A can also be put in a cell as a result of another function such as this formula in cell B1: =IF(A1="",NA(),A1).
By convention, entry clerks do not enter anything when they see there is a blank in the source data. Switching to using N/A can be solved in two ways. First, after data entry those blanks can be changed to N/A by Ribbon: Home / Editing / Find&Select / Replace / Find What: (leave blank) Replace with: =NA(). But this introduces the error of a mistakenly blank cell being turned into N/A. The second way is to give clerks a way to explicitly and easily enter N/A on the keyboard by this macro assigned to a key combo that is unused by Excel and easy to type e.g. Ctrl+Shift+J.
Much thanks, John.
[h=1]#N/A – An Introduction[/h]A display of #N/A means not available with the hash implying there is an error. It signifies there is nothing in this cell (not even a blank) available for use by any other feature of Excel, including formulas and charts. Display of #N/A can come from two sources: intentionally typed into the cell or result of a formula error.
Intentionally typing an N/A overcomes the possibility that a blank in the data was a mistake of omission by the data entry clerk. The ambiguity is resolved by inputting an N/A in the cell to indicate the cell is intentionally empty. N/A is the preferred technique over leaving the cell blank or a place-holder like zero or the empty string "". In this case N/A is used intentionally so the hash mark displayed as an error prefix is misleading.
A formula error that displays N/A can result from an argument that points to a value that the function cannot use. A very common example is vlookup() to a table that is not properly organized. Any formula will return an N/A if it has a precedent of a cell holding N/A (by inheritance of errors). In this case the hash mark displayed as an error prefix is not confusing.
The only way to type an N/A in a cell is by typing the formula =NA(). The clerk cannot type the letters N/A because that creates a string. An N/A can also be put in a cell as a result of another function such as this formula in cell B1: =IF(A1="",NA(),A1).
By convention, entry clerks do not enter anything when they see there is a blank in the source data. Switching to using N/A can be solved in two ways. First, after data entry those blanks can be changed to N/A by Ribbon: Home / Editing / Find&Select / Replace / Find What: (leave blank) Replace with: =NA(). But this introduces the error of a mistakenly blank cell being turned into N/A. The second way is to give clerks a way to explicitly and easily enter N/A on the keyboard by this macro assigned to a key combo that is unused by Excel and easy to type e.g. Ctrl+Shift+J.
Sub InsertNA()
ActiveCell.FormulaR1C1 = "=NA()":ActiveCell.Offset(1,0).Select
End Sub
Aside, N/A is an interesting logical problem as it indicates a cell is empty. But the cell actually holds the formula =NA() and thus makes the cell not empty. Although a conundrum to humans, Excel handles this without problem. ActiveCell.FormulaR1C1 = "=NA()":ActiveCell.Offset(1,0).Select
End Sub
