DATA TABLES
A data table allows you to study the effects a range of values has on a formula. This is a timesaver as you only need to set the formula up once. A one-variable data table is a range of cells that shows the results of substituting different values in one or more formulas. Data tables are actually array formulas which allows them to perform multiple calculations at once. In other words, you can see a number of different scenarios at a single glance.
There are one-input and two-input tables. We are just going to cover one-input tables today.
ONE INPUT DATA TABLES
Input values can be either listed down a column or across a row. Formulas used in the data table must refer to the input cell which is the cell in which each input value in the data table will be substituted. Any cell can be the input cell.
Type the list of values you want to substitute in the input cell either down one column or across one row.
To Create A One-Input Data Tables (General Steps for Excel 2007)
1. Type the list of values you want to substitute in the input cell either down one column or across one row.
2. Enter the formula you want to use. If the data table is in column format, enter the formula in the first blank cell above and to the right of the top of the table. If the data table is in row format, type the formula in a blank cell to the left of the first value and one cell below the row that contains the values.
3. Select the data table, including the formula.
4. Click the Data Ribbon.
5. Click the What-if Analysis button on the Data Tools group and select Data Table from the menu.
6. Enter the input cell (the value that you want to substitute with the values from your data table). If the data table is in a column, enter the cell reference in the Column Input text box. If the data table is in a row, enter the cell reference in the Row Input text box.
7. Click OK.
Clear as mud? Okay - let's go through a specific example.
We are going to create a one variable data table. The variable that is going to change is the interest rate. This is also considered the input cell. The Data Table Calculation will look at the PMT formulas, the input cell (interest rate) and the cell we want to change (payment amount).
Basically, we have a column of interest rates and we want to determine what the monthly payment will be at each different rate.
(Click on the picture for a larger view. Sorry, I need to figure out how to add files out here). Email me if you want the file.
- Observe the cell range D6.D12 which contains the interst rates. (highlighted in green).We have created a data table that we are going to use for substitute down payment values. We want to see how different rates will affect the monthly payment value.
- In cell E6 type: =B14 and press Enter. [B14 is the PMT calculation]
- The formula in cell E6 is the one whose values we want to display which in this case is the Payment Amt.
- Select the cell range D6:E12 and click the Data Ribbon.
- Click the What-if Analysis button and select Data Table.
- Click in the Column Input Box and type: B8
- Click OK
Below is data table showing our results based upon the down payment values in our data table and our interest rates.
Look at all the different payments or scenarios if you will that you can see at once. Tomorrow or is it Thursday, I will show you an example of a 2 input variable. In that example, we will have interest rates and years as the 2 variables that change. Hey- this should be very handy if you are thinking of buying a new car or a new house however if you think about it, you can apply this to a lot of business situations. I'll give you an example tomorrow but see what you can come up with on your own.
Patricia
No comments:
Post a Comment