Offset and Choose

1 comment

Formulas discussed: Offset, Choose, Lookup, Index
The workbook used throughout this article is available here.

Both Offset and Choose are commonly used in scenario/sensitivity selections. Unfortunately, both functions have major weaknesses, even when used in something as simple as scenario selection.

Brendan WalpoleOffset and Choose
read more

Even Microsoft makes Excel mistakes

No comments

You can see the original article by David Yanofsky on Quartz here.
Formulas discussed: Sum

This photo is from an ad for the new Microsoft Surface tablet, meant to show some of the things you can do on the new tablet (photo by David Yanofsky)

What most will notice is that the Total in cell B9 is showing $9,000, but adding up the individual holiday items comes to $9,500. Strangely enough, the pie chart (despite being one of those horrendous exploding ones) looks to be correct. At least it wasn’t a 3D exploding pie.

So what happened. Some peoples guesses on Twitter were that B3 was being edited, but it appears in the chart already.

Brendan WalpoleEven Microsoft makes Excel mistakes
read more

Net Present Value and Internal Rate of Return


Formulas discussed: NPV, IRR, XNPV, XIRR
The workbook used throughout this article is available here.

Net Present Value (NPV) and Internal Rate of Return (IRR) are thought to be the two of the most common tools for evaluating the financial viability of projects. What is also apparent is that there is significant debate as to the appropriateness of these measures, which is something we’ll leave to those other forums (such as here and here).
What we will cover in this article is their incorrect use in Excel, due largely to people misusing the inbuilt formulas or not understanding their mathematical construction. This is often apparent in financial models, where the a single outcome is sought, and the formula syntax is simple to use, but the modeller may not completely understand what is going on under the hood of these formulas and produces an answer that is not what they intended (whether they detect the error or not).

Brendan WalpoleNet Present Value and Internal Rate of Return
read more

Index and match versus lookups – when to use them

1 comment

Formulas discussed: Lookup, Vlookup, Hlookup, Index, Match.
Just want the table to show you which formula to pick – click here. The workbook used throughout this post is available here.

This article and the attached spreadsheet will discuss the various methods of searching for a data point in an Excel table and return the value in a corresponding row or column, without having to manually look through the table yourself. Common examples include searching for a sales result for a particular period, or matching a data field to a person’s name.

screen shot - sorted data table

Brendan WalpoleIndex and match versus lookups – when to use them
read more

Excel’s best feature hidden in plain sight – Using Styles

No comments

Styles are a reasonably new feature, being significantly upgraded in Excel 2007 for Windows (earlier verisons had much reduced functionality), but not making it to the Mac version until Excel 2011, inexplicably being left out of Excel 2008 for Mac, along with all macro/VBA functionality. I love styles so much that 10 years ago wrote a .xla file that I used to keep formatting consistent throughout a model (although far inferior to what MS ultimately incorporated, as it was very slow, having to look at each cell individually). And when I found out that they’d been included in Excel 2011, I went and upgraded the whole Office suite on the spot (which if the company you work for is a participant in Microsoft’s Home Use Program, is well worth it).

What are Styles

Styles are a set of preset, editable formats that you can apply throughout your workbook. Excel contains a good number of built-in styles straight out of the box, such as Normal, Output, Heading1, Total, Input etc. These are all capable of being customised to your liking, or you can create new ones.

Brendan WalpoleExcel’s best feature hidden in plain sight – Using Styles
read more