Applying a complex Excel model to multiple input values

You may remember the day you were introduced to Excel’s pivot tables. It might have happened through a co-worker, a book or an online tutorial but the effect was probably along the lines of: “Wow! How did I ever analyze data without them?”

Well, I sort of went through the same experience again several months ago reading about Dermot Balson’s Merge pattern (sample file here) – thanks to a post by Jim Johnson. I would love to elaborate some other time on Dermot’s idea of applying the concept of design pattern to spreadsheets but I will delve here specifically into his merge pattern, which makes use of Excel data tables.

According to Microsoft, data tables allow you to “test different input values for a formula without having to retype or copy the formula for each value” (Q282852). This is typically useful for sensitivity analysis, but it also works great for running complex calculations against multiple records in a data set.

I’ve argued before that most (structured) data benefits from being stored in a a flat file format. One of the downsides of doing so, however, is the amount of work involved in running a complex Excel model against all rows in a flat file. If you don’t intend to migrate your model to a relational or multidimensional database, then data tables as a wonderful way of achieving just that – free-form spreadsheet computations applied to structured data.

Additional links of interest

Happy Excel modeling!

One thought on “Applying a complex Excel model to multiple input values

  1. Pingback: Spreadsheet Design Standards | Stéphane-Robert Langer

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s