Automating without code

In the page on design patterns, I've noted that one of the biggest problems with Excel is that when you have a set of data rows, it can be difficult to cram in all the calculations on the same row as the data.

I've suggested an approach I call a "merge pattern", where you have a calculation sheet which does all the calculations for one data record only. You then have to find a way to push all the data rows through this sheet, one by one, and store the results.

This page shows how to do this without code.

The spreadsheet here gives an example and explains in detail how it works. A brief summary is given below.

How it works  

The picture below shows an example, with



Note how the calculation sheet has a record selector at the top. If you enter 1, the formulae underneath will pull in the first row of data, whereas if you enter 6, they will pull in the 6th row, etc. It is not difficult to get this far - the hard part is getting Excel to select all the data rows in turn, and to put the results on another sheet.

One solution is VBA, and in fact, it only takes a dozen lines of code to set up a generic template that could be adapted for any number of projects.

However, there is a way to do it without any code at all, using Excel data tables. Normally, a data table is used for sensitivity testing, with the user providing a series of different values for one input (or two inputs), and telling Excel which output cells are needed. Excel then calculates all the results for each of the different input values.

The limitation of data tables is that they can only handle one or two inputs. However, we only need to change one input, the record selector. If we create a column of numbers 1,2,3,4,5..... and give it to Excel as the value of the record selector, it will run through all our data for us.

Please see the workbook (link above) for more detail.

Sensitivity testing, scenario testing and simulation 

This approach can be easily extended to handle sensitivity testing, scenarios or simulations. You set up each set of assumptions (or simulated inputs) as a row in a data sheet, and use the record selector to bring a set of assumptions into the calculations sheet. A data table can then process all the records through the calculation sheet.

You can get even more sophisticated. Suppose you just have a BIG model that projects figures over a number of years, and while you have been putting each year in its own row, it is getting cramped because of all the calculations. Suppose you could write the model to just calculate ONE year at a time, so you could spread out all the calculations as much as you liked, and you'd use that one set of calculations for all the years. The problem is that normally, one year's calculations depend on values brought forward from previous years, but you can store those in data tables. The example spreadsheet linked above has an example to illustrate.

NOTE: Generally, VBA will run a lot of calculations faster than a data table, but it requires good programming skills.

 

 

 

Note: Where possible, I've attributed ideas to the people who thought of them. If I have missed someone or made a mistake, please let me know.