Tag: Automation

N Function Allows for Documentation in Excel’s Formula Bar

Microsoft Excel’s N function has many uses, including Switching and in line documentation

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):

N-Workbook-ExampleDownload

Learn More: 

https://support.office.com/en-us/article/n-function-a624cad1-3635-4208-b54a-29733d1278c9

Excel EOMONTH Function Leads to Consistent Expense Accruals

EOMONTH Function

The Excel EOMonth Function is a handy formula which will return a date representing the last day of a particular month. The month end date returned is determined by the second parameter. 

The syntax is simple =EOMONTH(Start Date, Number of Months) where number of months can be positive or negative integer .

Any date can be used as a starting point in the first parameter. Regardless if it’s the first or thirtieth you’ll always get a date at the end of the month. 

The second parameter is what makes the formula so interesting. You can specify the number of months to forward or backwards to return. To move backwards provide a negative number.

One potential use – would be to automate your monthly expense accruals. In the above sample workbook, the accountant only needs to update the most recent paid invoice. The formulas then automatically determine the period to accure.

The Accure From is simply the period end date plus one and then the EOMONTH in cell F8 determines the end of the following month. Using a formula like this would help you apply a consistent accrual methodology throughout your financials. 

For more information on EOMONTH check out the official documentation https://support.office.com/en-us/article/eomonth-function-7314ffa1-2bc9-4005-9d66-f49db127d628

You can download the sample workbook below

EOMONTH Example WorkbookDownload