This is the first article of the five-part series on Data Analysis in Excel. In this section, I will show you how to create and use a One Variable Data Table in Excel.
Other articles in the Data Analysis series:
Watch Video – One-Variable Data Table in Excel
One variable Data Table in Excel is most suited in situations when you want to see how the final result changes when you change one of the input variables. If you want to change two variables, use two variable data table, or Scenario Manager.
When to use One Variable Data Table in Excel
Suppose you have a data set as shown below:
In the above data set, we have the Loan Amount, Interest Rate, and Number of Monthly payments.
The Monthly Payment value is calculated based on these three variables (it is in red as it is an outflow of money). The following formula is used to calculate the Monthly Payment:
=PMT(B2/12,B3,B1) Now you may want to do an analysis to see what number of monthly payments suits your condition, and how would the monthly payment vary based on it. For example, you may want to keep the tenure of your loan (number of monthly payments) such that your Monthly Payment amount is not more than $1,000.
A quick way is to change the data in this table and the formula would automatically update. However, if you want to test if for many different Number of Monthly Payments, then a One Variable Data Table in Excel would be the way to go.
Setting Up a One Variable Data Table in Excel
Here are the steps to set up a One variable data table in Excel:
- In a column, have all the different values of Number of Monthly Payments that you want to test. In this example, we are testing for 72, 84, 96… 240.
- Type =B4 in cell E1, which is one row above the values in the adjacent column to the right. This is a construct that needs to be followed when you work with one variable data table in Excel. Also, make sure that the value in cell E1 is dependent on Number of Monthly Payments. It won’t work if you manually enter the value in cell E1.
In this case, cell E1 refers to cell B4, which has a value calculated using a formula which uses cells B1, B2 and B3.
- Now the data is all set to be used in the One Variable data table.
- Select the data (D1:E16). Go to Data Tab –> Data Tools –> What if Analysis –> Data Table.
- In the Data Table dialogue box, refer to cell B3 in Column input cell field. Since we are using a one-variable data table, leave the Row input cell empty. Also, we chose Column input cell option as the data is structured in a column.
- Click OK. As soon as you click OK, it will automatically fill the cells adjacent to the variable data points (number of monthly payments).
For example, if you wanted to calculated what number of monthly payments you should use to have the monthly payment less than $1,000. You can easily select 144 months (12 years).
Note:
- Once you have calculated the values using data table, it can not be undone using Control + Z. You can however manually select all the values and delete it.
- You can not delete/modify any one cell in the entire set of calculated values. Since this is an array, you will have to delete all the values.
- You can do the same calculation by arranging the data horizontally. The only difference would be that you will have to use Row input cell in the data table dialogue box (instead of column input cell).
Download File.. Try it yourself
You May Also Like the Following Excel Tutorials:
2 thoughts on “Data Analysis [Part 1 of 5] – One Variable Data Table in Excel”
=PMT(B2/12;B3;B1) Without comma. (excel 2007)
No…………. (with comma ,)