1 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

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

submitted by /u/SetBee
[link] [comments]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#Excel alternatives for data analysis
#natural language processing for spreadsheets
#generative AI for data analysis
#AI formula generation techniques
#rows.com
#Excel compatibility
#financial modeling with spreadsheets
##N/A
#NA()
#=LET
#=HSTACK
##N/A!
#literal
#error checking
#array
#green corner
#=IF
#performance
#evaluation
#function