What are “Data Tables”?

“Data tables” are a useful tool to change variables in a “Formula” and to see what effect they have on the results of the “Formula”.

We use this for “What-if” analysis (“What-if” analysis).

We can have “Data tables” with single variable value or “Data tables” with two variable values.

For example, we can make a “Data Table” with a variable value such that it calculates the commission of selling various products.

When we create a “Data table”, we must take into account, for the organization of the table, if we have a single or a double “Data table”.

All “Data tables” consist of three basic points:

1. an input column or row (or both if we have double “Data table”).
2. an output column or row (or both if we have double “Data table”).
3. and a formula that is evaluated in the “Data table”.

The variables in the “Formula” need to be changed and must be entered as cell references.

Unless we already have cells that the formula refers to in our “Worksheet” so that they can be entered in the table, we create a “Formula Reference table”, which contains one or more cells which we want to apply in our “Formula” in the “Data table”.

The “Formula Reference table”

The “Formula” for which you wish to see the different values, must have a variable that can fill with values, in the input column.

The variable in this “Formula” must therefore be introduced as a cell reference so that, we will always be able to adjust that part of the “Formula”.

When the variable in the “Formula” does not exist in the worksheet, it helps to create a “Formula reference table”.

That way we can be sure that the “Formula” that is evaluated in the “Data table”, gets its reference from the “Formula reference table”.

When the cell reference already exists in the table, this step is unnecessary.

Suppose we have a product that costs $ 100, for which we give a discount of 5% to certain customers, then we can make a “Formula Reference table” with three cells, one number, one with the price, and with the discount.

When we enter our “Formula” with references to these cells, the outcome is always adjusted when one of these cells is changed.

“Data tables” with a variable value

In a “Data table” with a variable value, we can choose between column-oriented or row-oriented.

When the “Data table” is column-oriented, we place the “Formula” in the row above the first value and one cell to the right of the column of values. All other “Formulas” to the right of the first “Formula” are carried out first.

When the “Data table” is row-oriented, we place the formula in the left column of the first value and one cell below the row of values. All other formulas must be entered under the first “Formula”.

A column-oriented data table:

Once we have the “Formula Reference Table” and the structure of our “Data table”, it is easy to fill the rest of the “Data table”.

First, we select the cell “Range” which has the “Formula” to evaluate the “Formula” itself.

In our case, therefore, it is the “Input column” and the “Output column”. (D4 to E9)

Now we click on “Data” in the menu bar and select “Table”.

This opens the dialog “DataTable”.

In the dialog, we click the button at the far end of the “Column input cell” box.
(If the “Data table” is row-oriented we choose for “Row input cell” of course.)

We select the cell with the variable that changes in the input row in the “Formula Reference table”.

We click on the “Expand” button again. .

And click OK.

Result:

When we change the percentage in the reference table, the column of discounts in the “Data table” changes automatically.

“Data tables” with two variable values

In a “Data table” with two variables we need to fill in both boxes in the “DataTable” dialog, indicating the cell

reference used for the “Input column” and the “input row”.
We therefore first select the cell “Range” from where we have to evaluate the “Formula” to the “Formula” itself.

Then we click on “Data” in the menu bar and select ” Table”.

We click the button  on the far right of the “Row input cell:”.

We select the discount (the first variable) in our reference table.

We click on the “Expand” button again.

And click on the “Expand” button to the right of the box “Column input cell”.

We select the amount (the second variable) in our reference table.

We click on the “Expand” button again.

And click OK.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats