Microsoft Office Tutorials and References
In Depth Information
Playing What-If with Data Tables
Playing What-If with Data Tables
Data tables enable you to enter a series of possible values that Excel then
plugs into a single formula. Excel supports two types of data tables: a
onevariable data table that substitutes a series of possible values for a single
input value in a formula and a two-variable data table that substitutes series
of possible values for two input values in a single formula.
Both types of data tables use the very same Data Table dialog box that you
open by clicking Data What-If Analysis Data Table on the Ribbon or
pressing Alt+AWT. The Data Table dialog box contains two text boxes: Row Input
Cell and Column Input Cell.
When creating a one-variable data table, you designate one cell in the
worksheet that serves either as the Row Input Cell (if you’ve entered the series
of possible values across columns of a single row) or as the Column Input
Cell (if you’ve entered the series of possible values down the rows of a single
column).
When creating a two-variable data table, you designate two cells in the
worksheet and, therefore, use both text boxes. One cell serves as the Row Input
Cell that substitutes the series of possible values you’ve entered across
columns of a single row, and the other cell serves as the Column Input Cell that
substitutes the series of possible values you’ve entered down the rows of a
single column.
Creating a one-variable data table
Figure 8-1 shows a 2014 sales projections spreadsheet for which a
onevariable data table is to be created. In this worksheet, the projected sales
amount in cell B5 is calculated by adding last year’s sales total in cell B2 to
the amount that we expect it to grow in 2014 (calculated by multiplying last
year’s total in cell B2 by the growth percentage in cell B3), giving you the
formula
=B2+(B2*B3)
Because I clicked the Create From Selection command button on the Ribbon’s
Formulas tab after making A2:B5 the selection and accepted the Left Column
check box default, the formula uses the row headings in column A and reads:
=Sales_2013+(Sales_2013*Growth_2014)
Search JabSto ::

Custom Search