Microsoft Office Tutorials and References
In Depth Information
Analyzing data by using data tables
Analyzing data by using data tables
When you examine business data in Excel, you will often want to discover the result of
formulas if you provide different inputs. For example, the executive team of Consolidated
Messenger might want to calculate the change in revenue from raising shipping rates,
increasing package volume, or both. In Excel 2013, you can calculate the results of those
changes by using a data table.
To create a data table that has one variable, you create a worksheet that contains the data
required to calculate the variations in the table.
IMPORTANT The data and formulas must be laid out in a rectangle so the data table you
create will appear in the lower-right corner of the cell range you select.
The formula used to summarize the base data appears in cell D2, the cells with the
changing values are the range C3:C5, and the cells to contain the calculations based on those
values are D3:D5. To create the single-variable data table, select the cell range C2:D5, which
encompasses the changing rates, summary formula, and target cells. Then, on the Data tab,
click What-If Analysis, and then click Data Table to display the Data Table dialog box.
Now you need to identify the cell that contains the summary formula’s value that will
change in the data table’s cells. In this case, that cell is B3. Because the target cells D3:D5
are laid out as a column, you enter the cell address B3 in the Column Input Cell box and
click OK.
Search JabSto ::

Custom Search