We commonly use Excel’s comment or note function to explain formulas or where the cell values come from. But, have you ever wanted to put this information in the formula bar itself? Sometimes it’s more helpful to document our formulas and data right where the user is reading them. Enter Excel’s N function.
The N function converts it’s parameter to a number. This is not to be confused with the VALUE function which converts text to number values. The N function will convert any text given to it to the value zero. If that doesn’t sound useful, keep in mind the additive property of zero anything added to zero is the original value. With this in mind, you can use this function to insert comments into the formula. For example if you had a formula to give an annual average and increase it by 3% your formula might be =AVERAGE(A1:A12)*1.03+N(“Returns a 3% increase in the average”). This use is shown in the featured image.
Switching in Formulas
Another interesting property of the N function is the way it converts TRUE or FALSE values. N will return the value 1 for a TRUE or 0 for a FALSE. Considering the multiplicative properties of 1 and 0 we can use this to create switches in our formula. A switch is where an operation is included in the final result if it’s multiplied by 1. For Example, this formula would result in the value 2 =(2*N(FALSE))+(2*N(TRUE)) if you expanded it it would be (2 x 0) + (2 x 1), change the first FALSE to TRUE and the value will increase to 4. With some careful workbook design, the N function can be used to replace nested IF statements which would serve a similar purpose. Let’s look at a more sophisticated example.
The sample workbook features quarter ending dates and the date of the current workpaper. By simply changing the workpaper date the Year To Date amortization updates appropriately. I’ve also used the comment N technique described earlier to demonstrate that use.
The N function is deceptively powerful. On the surface it would be easy to dismiss, but with a little ingenuity and careful workbook design it’ll allow you to simplify your workpapers and provide great documentation in the formula bar.
Do you want to get started with VBA? Want to learn how to automate your financial workpapers?
My book, Beginning Microsoft Excel VBA Programming for Accountants has many examples like this to teach you to use Excel to maximize your productivity! It’s available on Amazon, Apple iBooks and other eBook retailers!
Download the sample workbook (Excel 2010 and higher):
Learn More:
https://support.office.com/en-us/article/n-function-a624cad1-3635-4208-b54a-29733d1278c9