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
July 7, 2009
Following on from the previous post, once you’ve started on the path of setting out your model in blocks of related cells, you may become obsessed with knowing that all the cells in the block contain the same formula. After all, there’s no point in arranging your spreadsheet into nice blocks of logically consistent cells, if one or more of the cells in a block has a different formula to the others. Read the rest of this entry »
2 Comments | Financial Modelling | Tagged: Spreadsheets, Excel, Design, Blocks, Inconsistencies, Macros | Permalink
Posted by Darren Miller
June 16, 2009
One of the more important considerations when attempting to create a well designed spreadsheet model is the concept of designing in blocks.
“Blocks” is a colloquial rather than a technical term, and means a “range of contiguous cells that have something in common”. This could be something trivial like number format (currency, percentages, etc.), cell shading, and the like. Or it could be something more meaningful like a logically identical formula. Read the rest of this entry »
1 Comment | Financial Modelling | Tagged: Spreadsheets, Excel, Design, Blocks | Permalink
Posted by Darren Miller
