Create a two-variable data table

Two-variable data tables (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: one-input tables and two-input tables.) use only one formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) with two lists of input values. The formula must refer to two different input cells (input cell: The cell in which each input value from a data table is substituted. Any cell on a worksheet can be the input cell. Although the input cell does not need to be part of the data table, the formulas in data tables must refer to the input cell.).

  1. In a cell on the worksheet, enter the formula that refers to the two input cells.

In the example below, where the formula's starting values are entered in cells B3, B4, and B5, you would type the formula =PMT(B3/12,B4,-B5) into cell C2.

  1. Type one list of input values in the same column, below the formula.

In the example below, you would type the different interest rates into cells C3, C4, and C5.

  1. Type the second list in the same row, to the right of the formula.

In the example below, you would type the loan terms (in months) into cells D2 and E2.

  1. Select the range of cells that contains the formula and both the row and column of values.

In the example below, you would select the range C2:E5.

  1. On the Data menu, click Table.
  2. In the Row input cell box, enter the reference to the input cell for the input values in the row.

In the example below, you would type cell B4 in the Row input cell box.

  1. In the Column input cell box, enter the reference to the input cell for the input values in the column.

In the example below, you would type B3 in the Column input cell box.

  1. Click OK.

Example

A two-variable data table can show how different interest rates and loan terms will affect the mortgage payment. In the following example, cell C2 contains the payment formula, =PMT(B3/12,B4,-B5), which uses two input cells, B3 and B4.

Data table with two variables

 
 
Applies to:
Excel 2003