literal #n/a vs na()
The error #N/A! can be returned in a formula by directly writing it =#N/A or the function =NA()
I use #N/A instead of NA() since it's a direct literal, however I noticed that if the formula contains NA() anywhere and the result is #N/A!, the error checking green corner is not visible.
For example, =LET(a,NA(),#N/A) doesn't show green corner but =LET(a,#N/A,#N/A) does.
Not calling the function doesn't hide the green corner, i.e. =LET(a,NA,#N/A)
But avoiding evaluation does hide it, i.e. =IF(TRUE,#N/A,NA())
Couldn't find anything on this online, I assume the error checking still occurs because if it returns other error than #N/A! then the green corner appears, i.e. =LET(a,NA(),0/0)
I'm curious if there is any benefit to this, perhaps performance wise, for example:
=LET(a,NA(),IF(SEQUENCE(100)=SEQUENCE(1,100),1,#N/A))
This returns a 100x100 array with 9900 #N/A, if not for "a,NA()" it would show green corner on 9900 cells.
Sidenote, a benefit to literal #N/A is wrapping it into an array to avoid function returning just #N/A, for example:
=HSTACK(1,NA()) and =HSTACK(1,#N/A) return just #N/A!
=HSTACK(1,{#N/A}) returns {1,#N/A}
Thanks
[link] [comments]
Want to read more?
Check out the full article on the original site