SPECIALISTS IN FINANCIAL MODELLING

Get to Know your Excel Error Values

Error values such as #REF!, #NA! or #VALUE! are Excel’s irritating but necessary way of telling us that something’s not quite right with your formula or function.  Although they can be frustrating and time-consuming to rectify, dealing with them is an obligatory part of modelling.  Instead of being ignored, supressed or yelled at, Excel errors should be heeded, taken care of and according to a recent article, perhaps even celebrated!

Excel errors should not be confused with error checks, which are formulas created by the modeller that have been designed to alert the user as soon as the model is not calculating correctly.  Excel error values are errors displayed by Excel when the formula has not been built properly.   For example, using the multiplication symbol “x” instead of “*” e.g. =I36xJ36 is an incorrect formula which will return the value #NAME? because Excel does not recognise it.  Other common sources of error are brackets (parentheses) that do not match, or missing sections for functions.

If you get an Excel error message, click OK on the error message’s dialogue box. Then either edit the formula on the Formula Bar to correct the formula, or click the Cancel button to clear it.

Here are some common error values you might get, and how to fix them:

######

The railroad tracks; my favourite!  This is not really an error, and very easily to fix. The result is too long to fit in the cell, so just make the column wider.

#DIV/0!  

You’re trying to divide by zero.  Check that the formula is dividing by is not empty, as this will translate to a zero value.

#NAME?

There’s a name in the formula Excel doesn’t recognise.  You may have named a range, and then spelt it incorrectly, or typed “VLOOKKUP” instead of “VLOOKUP”.

If you used a name you defined, check its spelling. You can avoid this error by selecting a name in the Name Box instead of typing it in.

If you typed in a function, check its spelling or verify that such a function exists.
If you are performing operations on text, make sure you have enclosed it in double quotation marks.

#REF!  

Your formula refers to a cell that no longer exists, due to a change in the worksheet.  This happens if you deleted cells referred to in the formula or pasted moved cells on cells referred to in the formula.

This is probably one of the more difficult errors to fix because you no longer have access to the cell that was deleted. You will need to re-enter the cell reference which is missing.

#VALUE!  

The formula is using a different type of reference to what the function was expecting. The most common cause of this is if a user tries to enter text into a cell instead of a number, or the formula is referring to a label instead of the numeric value.  For example: “TBA” or “Less than 10” instead of a numerical value.  Check to see that some fields are not referring to cells containing text.

#N/A   

This means that there is some data missing; the value the formula looking for is not available.  A common reason for receiving this error is if a LOOKUP cannot find the criteria it is searching for in the source data.   Make sure that the

function can find the data you’ve asked it to look for.

With the exception of the #### railroad tracks, all of these error values can be suppressed using the IFERROR function which will stop the error from showing.  So, for example, if the cell A2 contained a zero, the formula =IFERROR(A1/A2,0)  will return a zero instead of #DIV/0!.  Be careful, of course that you don’t suppress a real error which should be shown, and fixed!

Have you Considered PowerPivot?

Imagine analysing your monthly results in real time without an army of management accountants / consultants spending valuable time and money constructing delayed, unfathomable reports. Did we say “imagine”? You might be doing this already. How useful would it be for you and your colleagues to produce key analysis in seconds from automated reports talking to your existing management information systems?

First introduced as a free add-in in Excel 2010, and to be honest, slightly more difficult to find in Excel 2013, PowerPivot replaces and improves the SQL Server Analysis Services for Microsoft’s Business Intelligence (BI) suite. Put simply, PowerPivot is PivotTables on steroids. It extends the capabilities of the PivotTable data summarisation and cross-tabulation feature by introducing the ability to import data from multiple sources. It will allow you to do things you couldn’t do before.

As a self-service BI product PowerPivot is intended to allow users with no specialised BI or analytics training to develop data models and calculations, sharing them either directly or through SharePoint document libraries. But it doesn’t end there. For more sophisticated users, PowerPivot can:
•    create your own BI solutions without purchasing expensive software
•    manipulate large data sets quickly, even if it consists of millions of rows (Excel can’t do that!)
•    construct complex what-if reporting systems with data modelling and Data Analysis Expressions (DAX)

by Liam Bastick, FCA, FCMA, CGMA, Excel MVP and Director of SumProduct

Learn more about Power Pivot and Power Query

What’s New in Excel 2013

Why do they keep changing my Excel? As we all rely on Excel every day for our financial modeling and analysis work, it’s only natural that any new release will generate a lot of excitement as well as anxiety amongst users.
(more…)

Using Excel on a Mac for Financial Modelling & Analysis

The usage of Excel on a Mac is increasing and according to an online poll we ran last month on Linked In, more people are using Excel on a Mac than are still using the old Excel 2003, a gap which is likely to widen. Windows Excel 2013 is being released soon and is already available for preview, although this is unlikely to have much effect on the Windows vs.
(more…)

Which Version of Excel are you Using? Poll Results 2012

Now that Excel 2013 preview is available, I thought it was time to do another poll to see what version of Excel business users are on now.  It’s been around 18 months since the last poll on Excel Version Usage, and I was interested to see if there had been more of a shift towards the new versions of Excel.

(more…)