ETC Technology Portal

This is a multi-part article based on a webinar hosted by Decisioneering, Inc. on June 27th, 2007. The article outlines effective techniques and practices for managing large and complex spreadsheet simulations. Part I of the series deals with general spreadsheet construction techniques that become handy when working with large and complex spreadsheets. 

I recently gave a Webinar/Presentation that was hosted by Decisioneering, the folks who make Crystal Ball. To supplement the PowerPoint slides and files available for download, I thought it would be instructive to provide more of a how-to explanation and/or discussion of the techniques.

This article is organized into four main topics:

• Spreadsheet techniques you might find useful but may not know about
• Techniques that may be helpful in your Crystal Ball simulations
• Constructing a Data Overpass
• An application of the Layered Approach design pattern 

Some Useful Spreadsheet Techniques

One of the interesting things about constructing spreadsheets is that there are few if any constraints placed on you as you build your spreadsheet models and simulations. This feature can be tremendously empowering or cobbling.

One of the interesting things about constructing spreadsheets is that there are few if any constraints placed on you as you build your spreadsheet models and simulations. This feature can be tremendously empowering or cobbling.

I want to discuss some of these so-called "ridiculously complicated and abstract" techniques and show how they can be put to use. 

Transforming a table into a linear list

Sometimes you may have a table of N rows and M columns and you would like to have it as a straight list (see Figure 1). It is not a big deal to have a formula that directly links the value in say, cell P9 to B9 or P20 to E11 when the table size is small. That is, the formula for P9 is

=B9 

However when a table is large, this kind of direct table mapping is arduous and error prone. Obviously, there should be better ways to handle this. 

Figure 1: Transforming a rectangular table into a linear list

I want to introduce an Excel function called OFFSET. At its simplest level offset starts returns the value of a spreadsheet cell based on some starting point but shifted down a number of rows and shifted to the right a certain number of columns. OFFSET is great for looking up values in a table.

To look up values in a table say, Product C which is in the third row, and the West region which is in the fourth column to the right relative to the top left corner (cell A8 in Figure 1), we could use the formula:

=OFFSET(A8,3,4)