Excel Data Table with More Than Two Input Variables

Quote of the Day

Half-truths are like half a brick - they can be thrown farther.

— Hyman von Rickover

Introduction

Figure 1: Example of a 3-Input Variable, 4-Output Variable Data Table.

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.

Illustration

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.

Figure 2: Annotated 2-Dimensional Table.

 Examples

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.

• Atmospheric Characteristics
• Ballistic Example

Save