The Data Table tool in Excel is an excellent feature that enables you to perform sensitivity analyses on a model in the background. In this article, we will guide you through the process of using the One Variable Data and Two Variable Data Tables in Excel.

• One Variable Data
• Two Variable Data

Let's delve into these two methods in detail!

Download the Excel Workbook below to follow along and understand how to use the One Variable Data and Two Variable Data in Excel –

download excel workbookOneTwo-Variable-Data.xlsx

One Variable Data

We have a simple spreadsheet with data to calculate the monthly payment for a loan. It includes the Loan Amount, Interest Rate, Number of Monthly Payments, Monthly Payment, and the Total Interest Paid Over Period.

To calculate the Monthly Payments, use this formula:

=-PMT(B7/12,B6,B5)

To calculate the Total Interest Paid Over Period, use this formula:

=-CUMIPMT(B7/12,B6,B5,1,B6,0)

Our focus is on the interest rate, which is currently set at 10%. We want to determine the payments if the rate changes to 9%, 8%, 11%, and so on.

Manually, you could enter 8% into the Interest Rate cell to see the new Monthly Payment. However, by setting up the One Variable Data Table, Excel can automate this process for you.

Using this data, we can calculate the required Monthly Payments.

Let's create a column with interest rates of 8%, 9%, 10%, 11%, and 12%. You can add more if needed.

Above the Interest Rate column and to the right, enter = and select the Monthly Payment cell. To the right of that, enter = and select the Total Interest Paid Over Period.

Now, select the outputs at the top (Monthly Payment and Total Interest Paid Over Period), as well as the Interest Rate inputs to the left.

Navigate to Data, What-If Analysis, Data Table.

For the Column input cell, select the 10% (B7) cell. This tells Excel, in conjunction with the formula in cell C11, to replace cell B7 with 8% to calculate the Monthly Payment, replace cell B7 with 9% to calculate the Monthly Payment, and so forth.

Press OK. Excel will immediately generate a table with the corresponding figures for the Interest Rates.

The advantage of this Data Table is that any changes to the inputs (the light blue cells) will automatically update the outputs (grey cells).

Conclusion: if the Interest Rate changes to 8%, the Monthly Payment will be 506.17; if it becomes 9%, the Monthly Payment will be 518.95, and so on.

Two Variable Data

A Single variable data table is useful on its own. However, if you want to see how the Interest and the Number of Monthly Payments affect your payments, you can use Data Tables.

We will set up the table with the Interest charge as the column of input cells.

Above and to the right, enter your Number of Monthly Payments.

The output cell should be placed above the Interest Rate inputs. Enter = and click on the Monthly Payment cell (B8).

Select the table, ensuring to include both the input and the output.

Go to Data, What-if Analysis, then Data Table.

For the Row input cell, select the Number of Monthly Payments cell. For the Column input cell, select the Interest Rate cell.

Click OK.

As you can see, it will display the Monthly Payments for scenarios like 24 months at 8%, 36 months at 11%, and so on.

There you have it! By correctly setting up your data, defining the input cell, and creating the One Variable Data Table, you can efficiently analyze the impact of different input values on the result. Whether you're dealing with financial data or any other type of data involving input variables, One Variable Data Tables can assist you in making informed decisions based on your data.

The above is the detailed content of How to Use the One Variable Data in Microsoft Excel. For more information, please follow other related articles on the PHP Chinese website!