

 Thread Tools  Search this Thread 
#1
 
 
A Data Table in Microsoft Office Excel
Introduction  A data table is a range of cells that shows how changing one or two variables in your formulas will affect the results of those formulas. Data tables provide a shortcut for calculating multiple results in one operation and a way to view and compare the results of all the different variations together on your worksheet. Overview  Data tables are part of a suite of commands that are called whatif analysis tools. When you use data tables, you are doing whatif analysis. 
#2
 
 
Re: A Data Table in Microsoft Office Excel Whatif analysis Whatif analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. For example, you can use a data table to vary the interest rate and term length that are used in a loan to determine possible monthly payment amounts. Kinds of whatif analysis There are three kinds of whatif analysis tools in Excel 
Scenarios and data tables take sets of input values and determine possible results. Goal Seek works differently from scenarios and data tables in that it takes a result and determines possible input values that produce that result. Like scenarios, data tables help you explore a set of possible outcomes. Unlike scenarios, data tables show you all the outcomes in one table on one worksheet. Using data tables makes it easy to examine a range of possibilities at a glance. Because you focus on only one or two variables, results are easy to read and share in tabular form. A data table cannot accommodate more than two variables. If you want to analyze more than two variables, you should instead use scenarios. Although it is limited to only one or two variables (one for the row input cell and one for the column input cell), a data table can include as many different variable values as you want. A scenario can have a maximum of 32 different values, but you can create as many scenarios as you want. 
#3
 
 
Re: A Data Table in Microsoft Office Excel Data table basics You can create onevariable or twovariable data tables, depending on the number of variables and formulas that you want to test. Onevariable data tables  Use a onevariable data table if you want to see how different values of one variable in one or more formulas will change the results of those formulas. For example, you can use a onevariable data table to see how different interest rates affect a monthly mortgage payment by using the PMT function. You enter the variable values in one column or row, and the outcomes are displayed in an adjacent column or row. In the following illustration, cell D2 contains the payment formula, =PMT(B3/12,B4,B5), which refers to the input cell B3. Twovariable data tables  Use a twovariable data table to see how different values of two variables in one formula will change the results of that formula. For example, you can use a twovariable data table to see how different combinations of interest rates and loan terms will affect a monthly mortgage payment. In the following illustration, cell C2 contains the payment formula, =PMT(B3/12,B4,B5), which uses two input cells, B3 and B4. 
#4
 
 
Re: A Data Table in Microsoft Office Excel OneVariable Data Table To create a onevariable data table A onevariable data table has input values that are listed either down a column (columnoriented) or across a row (roworiented). Formulas that are used in a onevariable data table must refer to only one input cell.

#5
 
 
Re: A Data Table in Microsoft Office Excel Add a formula to a onevariable data table

#6
 
 
Re: A Data Table in Microsoft Office Excel TwoVariable Data Table To create a twovariable data table A twovariable data table uses a formula that contains two lists of input values. The formula must refer to two different input cells.
Example  A twovariable data table can show how different combinations of interest rates and loan terms will affect a monthly mortgage payment. In the following illustration, cell C2 contains the payment formula, =PMT(B3/12,B4,B5), which uses two input cells, B3 and B4. 
#7
 
 
Re: A Data Table in Microsoft Office Excel Data table calculations Data tables are recalculated whenever a worksheet is recalculated, even if they have not changed. To speed up calculation of a worksheet that contains a data table, you can change the Calculation options to automatically recalculate the worksheet but not the data tables. Speed up calculation on a worksheet that contains data tables 1. Click the Microsoft Office Button , click Excel Options, and then click the Formulas category. 2. In the Calculation options section, under Calculate, click Automatic except for data tables. Tip  An alternate method, On the Formulas tab  In the Calculation group  Click the arrow on Calculation Options  then click Automatic Except Data Tables. Note  When you select this calculation option, data tables are skipped when the rest of the workbook is recalculated. To manually recalculate your data table, select its formulas and then press F9. 
#8
 
 
Re: A Data Table in Microsoft Office Excel
Select the entire data table (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: oneinput tables and twoinput tables.), including all formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).), 

Tags: data table, excel 
Thread Tools  Search this Thread 

Similar Threads for: "A Data Table in Microsoft Office Excel"  
Thread  Thread Starter  Forum  Replies  Last Post 
MS Office Excel 2010: Unable to print PivotTables with nonPivotTable data at bottom of table.  Girl Geek 2012  Windows Software  2  06082012 07:00 PM 
Pivot table invalid data source reference error in Microsoft Excel  CeLeStiA  MS Office Support  2  25022012 09:51 AM 
How to view pivot table external data source in Microsoft Excel 200  LuniA  MS Office Support  2  23022012 11:09 AM 
Microsoft Office Excel cannot create or use the data range reference because it is too complex Error  Finian  MS Office Support  4  31122011 05:11 PM 
Convert Excel data to HTML Table  Chalina  Software Development  2  04082009 11:21 PM 