June 9, 2010
Many spreadsheet users will know that when you need to pick out a value from a list, it’s a good idea to use a lookup formula. There are numerous ways to do this, but which is the best? Each formula or method has its advantages and disadvantages depending on the circumstances.
It’s hard to cover all of these circumstances, so we’ll focus on one specific yet common situation. Let’s say you have a unique list of items, say “fruit”. Each type of fruit has a numerical value associated with it — perhaps the quantity or price of the item. Somewhere else in your spreadsheet you want to enter the fruit item you’re interested in and have a formula return the specific value for this fruit item. Let’s further assume that the items are not sorted in any particular order.
The table below gives you the idea.
In this table, we want to enter the name of the fruit in cell B10, and we want the value in cell C10 to automatically pick out the correct quantity in the source data.
So, now that we’ve set the scene, let’s look at 7 different ways to do this in Excel.
Read the rest of this entry »
4 Comments | 
Financial Modelling | Tagged: Spreadsheets, Excel, INDIRECT, Design, Errors, named ranges, INDEX, MATCH, Lookups | 
Permalink
Posted by Darren Miller
April 9, 2010
Everyone loves lists, so here’s one of my own — the top 7 things not to do when designing and building a financial model. Nothing controversial in this I hear you say? Fear not — this list will ruffle a few feathers. A few of these are on others’ best practice lists. Read the rest of this entry »
1 Comment | Financial Modelling | Tagged: Spreadsheets, Excel, Formatting, Styles, Design, Errors, named ranges, multiple worksheets, VBA/macros | Permalink
Posted by Darren Miller
August 17, 2009
Most people who use spreadsheets for financial modelling will have encountered the dreaded problem of circular references. If you don’t know what a circular reference is, then this post is not for you – at least not yet. Perhaps bookmark this page and come back one day when Excel barfs at you for writing a formula which refers to itself.
Circular references are the financial modeling equivalent of the old philosophical causality dilemma, typically stated as “which came first, the chicken or the egg?”
Circular references in spreadsheets can be either a) intended, or b) unintended. If they are the latter, then there is a mistake in your model and the circular reference should be eliminated as soon as possible. Read the rest of this entry »
5 Comments | Financial Modelling | Tagged: Spreadsheets, Excel, Design, Circular references, Subscriber equation, Algebra, Errors | Permalink
Posted by Darren Miller
