In my last tutorial I showed you Excel's T function, today we’re looking at the other one-letter function; the Excel N function.
Funnily enough the N function was only provided for compatibility with other spreadsheet programs, but it does have one handy use if compatibility is not your raison d’etre.
The syntax is:
=N(value)
Depending on what the ‘value’ is Excel will return the following:
 |
Now, you won’t have much use for this formula if you aren’t concerned with compatibility issues since Excel automatically converts values to the appropriate format when required. For example; dates are automatically converted to serial numbers when used in a formula.
But I wouldn’t be telling you all this if there wasn’t a clever use for it…
Excel N Function Trick – Embed Annotations in Your Formulas
You can use the N function in a nifty way to make notes within a formula to help you remember why you used a certain value or formula.
This trick relies on the fact that a text string returns a zero value. Take this for example:
=100*(1+10%)+N("10% was advised by Richard as the increase for 2011")
What this formula is actually doing:
=100*(110%)+0
As you can see the N function component of the formula has no impact on the result, but it allows you to insert an unobtrusive note in your spreadsheet that explains your rationale without the need for what can be annoying comments like this.
 |
Download the Workbook
Enter your email address below to download the sample workbook.
Microsoft Excel’s T Function
jim
NB using N() to annotate in this way will only work with formulas that return a number, otherwise it will error
a text-returning formula can be annotated with:
…&REPT(“comment text”,)
(note the sneaky comma before the closing bracket)
this can be used to annotate a number-returning formula too, but would convert the answer to text
Mynda Treacy
Nice tip, Jim! Thanks so much for sharing.
Pavel
Hello guys!
Interesting function
But doesn’t work here: =VLOOKUP(“*”&A12&”*”;Table1;2;0)+N(“any text here”)
I just added N function to a formula from your Wildcards lesson…
and it returnes #VALUE!
What am I doing wrong?
Mynda Treacy
Hi Pavel,
I don’t think it’s N that’s the problem. It’s likely your VLOOKUP is not finding the value you’re trying to lookup. Test it without the ‘N’ part.
Mynda
Chris Dunhill
Nope, it’s because Excel doesn’t like it when you try to add a number (0, above) to a string.
Mark
I first started doing spreadsheets in Quattro Pro, WordPerfect’s spreadsheet program. It allowed for comments in the formula line similar to how comments are done in VB. Finally! Years later – an Excel equivalent! Thanks.
Wookiee
Wow. I just stumbled on this site because a user posted a link to an array-based VLOOKUP article on MrExcel.com. I have to say that I’m really impressed!
I’ve been a fan of sites such as Ozgrid, MrExcel, and Chandoo.org for the past 2 or 3 years, but I don’t think I’ve ever learned so many new things that I can actually put to use in so little time as I have today just poking around on this site for an hour. I mean, I’ve just discovered the Camera tool, how to use the format painter multiple times in a go (been trying to figure that out for quite a while), and now a handy little function that pretty much allows you to include a comment in a formula just like you can comment out a line of code in VBA. Somebody just earned themselves a spot on my Favorites bar!
Thanks for creating and maintaining such a beneficial site.
Mynda Treacy
Wow, thanks Wookiee. I don’t think I’ve ever had so many kind things said about our site in one comment! 🙂
Shane Brown
I am preparing for my excel project which I have to submit to my university. This website really helped me and I am sure I am going to get A grade 🙂
Thanks a bunch really.
Mynda Treacy
🙂 Thanks, Shane.