Computer Data Analysis Instructor: Greg Shaw

Computer Data Analysis Instructor: Greg Shaw

CGS 2518

Two-Variable Data Tables

Concepts

A two-variable data table is used to perform a series of more sophisticated what-if analyses all at the same time, using various combinations of values for twoinput cells

• A two-variable data table always has exactly two input cells, and exactly one result cell

• Tutorial Example

“What would be the net income for various combinations of units sold and price per unit?”

• The result cell is the one with the formula for the net income
• The input cells are the two cells that contain the number of units sold and the price per unit

How to Create a Two-Variable Data Table

1. In the upper-left-hand corner cell of the table, insert a formula that references the result cell

1. Just to the right of the of upper-left-hand corner cell, fill the row with the range of values for one of the input cells

1. Just below the upper-left-hand corner cell, fill the column with the range of values for the other input cell

1. Select the entire table (except for any headings)

1. Data | Data Tools | What-If Analysis | Data Table...

1. In the Row input celltext box, enter the input cell corresponding to the row of values you entered

1. In the Column input celltext box, enter the input cell corresponding to the column of values you entered

1. Click OK

Creating a Custom Format

“Lets us display whatever we want in a cell regardless of the actual contents of that cell!”

Example: We want to “hide” the reference to the result cell in the upper-left-hand corner of the two-variable table because it could be confusing. Instead, let’s display a proper column heading.

1. Right-click the cell and choose Format Cells...
2. Click the Number tab
3. In the Category list, Click on Custom
4. In the Type text box, replace the format code shown with your text, enclosed in double quotes - for example, “Units Sold”
5. Click OK

Charting a Two-Variable Data Table

1. Select the entire table except for the top row!

Recall that the top row contains the column headings, which are usually labels. But when charting a two-variable data table, the column headings are numbers, not labels, so Excel would consider them to be part of the data and include them in the chart

1. Insert | Charts | Scatter and select the Scatter with Straight Lines subtype

1. By default, Excel assumes that the data series are in the rows. Ifinstead the data series are in the columns – as in the tutorial example – the chart will look weird and we will need to tell Excel to plot the data by column:

Chart Tools | Design | Data | Switch Row/Column

1. Move the chart to a separate sheet and add chart and axis titles

Note the “generic” entries in the chart legend (Series 1, Series 2, etc)!

Since the data series were in the columns, the legend was automatically created from the column headings, and we did not select any column headings! So, the last step is to rename each data series (see next page)

Renaming the Data Series

1. Chart Tools | Design | Data | Select Data

(Opens the Select Data Source dialog)

1. Now, for each label in the Legend Entries (Series) text box, do this:

1. Click the label to select it and click the Edit button

1. Click on the cell containing the column heading you want to use. (Since the chart is in a separate sheet, this will involve a worksheet reference)

1. Click OK

1. When all series have been renamed, click OK to close the Select Data Source dialog