Get to know, and learn to love, your Excel errors

The usage of Excel on a Mac is increasing and according to an online poll we ran last month on Linked In, sale 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. Mac market share.  With the use of smartphones and tablets increasing, of which Apple has a large share, users are becoming more likely to use it for all their applications, including their financial models in Excel.  I’m pleased that my publishers convinced me to include Excel for Mac instructions in my new book, Using Excel for Business Analysis; a Guide to Financial Modelling Fundamentals.  Below is an extract from the supplementary online materials  of the book.

Using Excel on a Mac for the purpose of financial modelling has only recently become a viable option. Although Microsoft has been releasing versions of Excel for Mac since 1985, only recently has the Mac version of the program been even close to as robust as its Windows counterpart.

Excel for Mac 2008

Excel for Mac 2008 is the equivalent of Windows Excel 2007, and is not generally considered a viable option for financial modelling in Excel.  One of the most noticeably absent features of Excel for Mac 2008 was the lack of VBA (Visual Basic for Applications) support, and for this reason, it was not often used for financial modelling.  This lack of functionality caused quite a few problems for business users who had highly sophisticated models in previous versions of Excel, which included VBA macros. Critical add-ins, such as Solver, were also not supported.

Excel for Mac 2008 also did not include Microsoft Office’s new “Ribbon”, which had been released for Windows in 2007.

Excel for Mac 2011

With the release of Excel for Mac 2011, serious Excel users finally have the option of using Excel on the Mac platform. It now includes VBA as well as the customisable “ribbon” interface although the menu groupings differ slightly in name from time to time. For this reason, Excel for Mac instructions have been included in Using Excel for Business Analysis.

With the release of Excel 2011, Excel for Mac became a possible competitor to Windows Excel.  As you will see in the book, only very rarely does one find a function or feature available in the Windows version of Excel that is not available in the Mac version.

Run Excel for Windows From a Mac

Even though one still might not want to use Excel for Mac, it is still possible to use Excel on a Mac using software that allows one to install the Windows operating system on a Mac. Several software options are available:

  • Parallels: Parallels partitions a part of the user’s hard drive, enabling the user to run a virtual copy of Windows on Mac Hardware. In Parallels, the virtual copy of Windows is run like any other program on Mac. In this way, the user can run Excel in Windows, but still switch back to the Mac environment whenever needed.
  • Boot Camp: Boot Camp is Apple’s official software to enable users to run other operating systems on their Mac, in similar fashion as is described above. The key weakness of Boot Camp, however, is that the end user must choose at startup which operating system to boot. Thus, the end user is unable to switch from his or her activities in Windows to other activities in Mac without completely rebooting his or her computer. This makes the use of Boot Camp for modelling impractical.

Running Excel in a virtual Windows environment is 99% the same as running Excel on an actual Windows computer. The only noticeable differences will be

  • Speed: Attempting to run Windows and Excel on an older or slower Mac will result in a noticeable lag in performance.
  • Keyboard Shortcuts: The user may desire to reroute various keyboard shortcuts on the Mac to enable use of familiar keyboard shortcuts in the Windows environment.
  • VBA: Some high-level functions (mainly those that interact with system hardware) in VBA may be restricted.
  • No Right Click:  All Macs can use a two button mouse, but the recent Macs come with one (or a no-button mouse which detects right side or left side pushes).
  • Keys: The F-keys don’t work, and you will need to edit or enable the button when switching to Mac especially the shortcut or even the common keys; keys you are used to usually has a shortcut assigned to it already.  Also there is no Delete or End key!

Compatibility

With the release of Excel for Mac 2011, cross-operating system compatibility is almost completely guaranteed. While all of the compatibility considerations mentioned above with regards to older versions of Excel still need to be thought through, the latest version of Excel for Mac has nearly eliminated the gap between versions created on different operating systems.

As I was scrolling through the index for my book recently, seek
I noticed the #REF error jump out at me.

Why is it that the #REF strikes such fear into a modeller’s heart?  Probably because out of all the common Excel errors, visit this site
it is the one which is the most difficult to fix.  A #REF error means your formula is referring to a cell that doesn’t exist anymore!

A model saved with a #REF error in it is not easy to correct because the formula that it’s referencing has gone. As such, the formula needs to be completely rebuilt.

As annoying as they are, believe it or not I actually like seeing Excel errors in my formulas. It means that something isn’t working, and I can, therefore, fix it.  I always tell my training course attendees that the only thing worse than finding a mistake in your model is NOT finding a mistake in your model and your model being wrong! Worse still is your boss finding the mistake in your model.

There’s no faster way to lose credibility as a modeller than for others to find formula mistakes in your model. There are a number of strategies to reduce error that you can employ reduce this possibility in your modelling.

If you’ve attended one of my courses, you’ll know I’m quite fond of the SUMIF function. This is because it is much more versatile and robust than other popular functions, such as a VLOOKUP.  One of my issues with the SUMIF, though, is that it does not warn you if you use mismatching ranges.  In the example below, the result in cell C13 will not return an error; it will simply give a wrong answer without any warning!

Source:  Fig 6.6 , page 96, Using Excel for Business Analysis

In this example, the thorough modeller will check the totals as they work, and then put an error check below in cell C17, to make sure that any incorrect entry in the future is quickly identified.

Even better than the SUMIF is its big brother, the SUMIFS function, which can handle multiple criteria and – critically – will warn the user if the ranges are mismatching, unlike the SUMIF.  It is one of SUMIF’s annoying little quirks that it allows mismatching ranges. Exactly why SUMIF allows this is something I’ll never understand.   It’s highly unlikely that we’d deliberately want mismatching ranges, and far more likely that it’s been done inadvertently (although I have been known to use it for staggering periods).

So, instead of cringing at a horrid #VALUE! error value, or supressing it with an IFERROR function, we should instead embrace, use and perhaps even love our Excel error values!  Figure out what’s wrong, and get to the bottom of it.  An Excel error value is far more helpful than the alternative – to have deceivingly innocent number values which are in fact incorrect.

Recent posts by Danielle Stein Fairhurst

Comments for “Get to know, and learn to love, your Excel errors”

  1. Zyrus says:

    Interesting article and totally agree. My rule of thumb is that if you are relying on error based logic, your core logic is incorrect and it needs to be revisited.

    Relying on an error to govern results is a recipe for disaster, not the least of which being figuring out why something isn’t working!