Quote of the Day

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

— Hyman von Rickover

## Introduction

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.

## 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.

This is beautiful!

Thanks! I do an enormous amount of data analysis and this has proved to be very useful.