Quote of the Day
Half-truths are like half a brick - they can be thrown farther.
— Hyman von Rickover
I am a big fan of Excel data tables, but I often struggle because they are designed to work with one or two input variables and a single output function. Many of calculations that I do have more than two input variables. These calculations also often have multiple output values. This post discusses how to create a data table with more than 2 input variables and more than one output variable.
I often use this type of table when I am verifying my Excel formula implementation against a reference. Figure 1 shows a typical example. I needed to compute the air density, dew point, humidity ratio, and enthalpy for multiple combinations of three input parameters: air pressure, relative humidity, and temperature (°F). I can now check these results against a reference table.
I give links to two examples at the bottom of this post.
Figure 2 shows an example of how I layout the data table. Here are the key features:
- Create a data table with sequentially numbered row and column values.
- The data table column input is in C45 (red).
- The data table row input is in C44 (blue).
- The output is selected from D34:D37 (green).
- The input parameters are selected based on the row input variable.
You can use either INDEX or OFFSET functions to implement the data table. I prefer the INDEX function because it is not volatile.
Here are two recent examples of this type of data table that I used to verify my implementation of some formulas. I should note that I spend quite a bit of time checking my Excel spreadsheets – errors are just too easy to make.