Worksheets are primarily used for what-if analysis.  Suppose you've created the worksheet at the left, but you would like to know what your payment would be at different interest rates.  You could type different interest rates in cell B2 and watch the result of the formula change over and over again.  But there's an easier way!!!
 
 

Creating a data table is a wonderful way to be able to easily see how changing values in a worksheet affect the results of formulas in the worksheet.  There are two different types of data tables, a one-variable data table and a two-variable data table.  A one-variable data table will allow you to vary one value in the worksheet as in the example above, where we're only changing the interest rate.  But if you wanted to see the results of payments using different interest rates AND different terms, you'd use a two-variable data table because you're varying two different values. 
 
 

One-Variable Data Table Varying Interest Rates	Two-Variable Data Table Varying Interest Rates and Terms
Notice in the one-variable data table, interest rates from 4.5% to 9% have been entered in column A.  In cell B8, =B5 was entered to reference the payment formula.  Then when the data table command was entered, column B automatically filled in with the various payment amounts.	In the two-variable data table, interest rates from 4.5% to 9% have been entered in column A and terms of 36, 48, 60 and 72 have been entered in Row 8.  Although it is hidden, a formula =B5 has been entered in cell A8.  The data table automatically computed the payments based on varying interest rates and terms.  You can easily see that with a term of 60 months and an interest rate of 7%, you'd have a payment of 693.04.

A one-variable data table can have only one value that varies (input value), but can have one or more result values.  In the one-variable data table above, the input values are the interest rates and the result values are the payments.  There is only one set of result values.  The illustration on page 9.14 of your textbook shows a one-variable data table with three columns of result values.

A one-variable data table can be created by placing the input values and result values in columns or in rows.  The illiustration above uses columns.   The input values must always be placed in the first column (or the first row if using rows instead of columns).

A two-variable data table can have two input values, but can have only one result value.  In the two-variable data table above, the input values are the interest rate and the term and the result values are the payments.  The interest rates are in a column and the terms are in a row.  It is important to remember which values are in columns and which are in rows, because you must specify this when you go through the data table command.

Read pages 9.04 - 9.25 and perform  the steps as instructed on these pages to create both one-variable and two-variable data tables.

Go to XY Charts.

sloopj@octech.edu

Back to The Course