How to Create a Two Variable Data Table in Excel (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

This article is part of my series: What-If Analysis in Excel – A Step-by-Step Complete Guide. In this article, we’re going to create a two-variable data table in Excel. A two-variable data table lets you use two cells as input. In the following figure, we have shown you a setup of a two-variable data table.

How to create a two-variable data table in Excel

The setup of a two-variable data table.

Although you may find that this setup looks similar to a one-variable data table, the two-variable data table has one important difference: The two-variable data table can show the results of only one formula at a time. On the other hand, in a one-variable data table, you can place any number of formulas, or references to formulas, across the top row of the table. In a two-variable table, this top row holds the values for the second input cell. The upper-left cell of the table contains a reference to the single result formula.

We have worked with a mortgage loan worksheet in our one-variable table article. We could create a two-variable data table using that mortgage loan worksheet that would show the results of a formula (say, monthly payment) for various combinations of two input cells (such as interest rate and down payment percent). You can create multiple data tables (one-variable or two-variable data table) to see the effects on other formulas.


How to Create a Two-Variable Data Table in Excel: 3 Examples

Here, we have used some examples in this article to work with the two-variable data table. Furthermore, we will show you 3 examples of how to create a two-variable data table in Excel.


1. Creating Two Variable Data Table for Direct Mail Profit Model

In this example, a company wants to make a direct-mail promotion to sell its product. This worksheet calculates the net profit from the direct-mail promotion.

This data table model uses two input cells: the number of mail sent and the expected response rate. Also, there are some more items that appear in the Parameters area.

Creating Two Variable Data Table for Direct Mail Profit Model in Excel

Now, we will explain how we find those parameters.

  • Printing Costs per Unit: This is the cost to print a single mail. You know, the unit cost varies with the quantity: $0.25 each for quantities less than 200,000; $0.18 each for quantities of 200,001 through 300,000; and $0.15 each for quantities of more than 300,000.
  • Thus, we have used the following formula in cell C9.
=IF(C5<200000,0.25, IF(C5<300000,0.18, 0.15))
  • Mailing Costs per Unit: It is a fixed cost, $0.30 per unit mailed.

  • Responses: The number of responses, is calculated from the response rate and the number mailed.
  • So, the formula in this cell is the following:
=C5*C6

  • Profit per Response: It is also a fixed value. The company knows that it will realize an average profit of $18.50 per order.
  • Gross Profit: This is a simple formula that multiplies the profit-per-response by the number of responses using this formula:
=C11*C12

  • Print + Mailing Costs: This formula calculates the total cost of the promotion:
=C5*(C9+C10)

  • Net Profit: This formula calculates the bottom line – the gross profit minus the printing and mailing costs.
  • Thus, we have used the following formula in cell C15.
=C13-C14

Now, the following figure shows the setup of a two-variable data table that summarizes the net profit at various combinations of mail numbers and response rates.

  • Firstly, cell F4 contains a formula that references the Net Profit cell: C15.

  • Here, enter the Response Rate values in G4: N4.
  • Then, insert the Number of Mail values in F5: F14.
  • Now, select the data range F4:N14.
  • Then, from the Data tab >> Go to the What-If Analysis command.
  • After that, choose the Data Table option.

At this time, the Data Table dialog box will appear.

  • Specify cell C6 as the Row input cell (Response Rate).
  • After that, select cell C5 as the Column input cell (Number Mailed) and click OK.

Here, as you can see, Excel fills the data table. In addition, the following figure shows the final result. Moreover, you can find out that a few of the combinations of response rate and quantity mailed result in a profit rather than a loss from this table.

Same as the one-variable data table, this data table is also dynamic. Here, you can change the formula in cell F4 to refer to another cell (Gross Profit). Or, you can enter some different values for Response Rate and Number Mailed.

Finally, Created Two Variable Data Table in Excel


2. Making Two Variable Data Table of Loan Payment

Here, we will demonstrate another example of creating a two-variable data table for loan payments in Excel. Furthermore, for the data table firstly we will calculate the Monthly Payment.

  • Select cell C12 where you want to calculate the Monthly Payment.
  • Then, use the corresponding formula in cell C12.
=PMT(C8/12,C7,-C11)
  • Press ENTER to get the result.

At this time, you can see the amount of the Monthly Payment.

Making Two Variable Data Table of Loan Payment in Excel

Formula Breakdown

Here, we have used the PMT function which calculates the payment based on a loan with a constant interest rate and regular payment.

  • Here, C8 denotes the annual interest rate of 5.25%.
  • C7 denotes the total payment period in terms of the month which is 220.
  • C11 denotes the present value which is $400,000.

Now, the following figure shows the setup of a two-variable data table that summarizes the monthly payment at various combinations of Interest Rate and Down Payment Percentage.

  • Firstly, cell F4 contains a formula that references the Monthly Payment cell: C12.

  • Enter the Down Payment Percentage in G4: J4.
  • Then, insert the Interest Rate in F5: F13.
  • Select the data range F4:J13.
  • From the Data tab >> Go to the What-If Analysis command.
  • Finally, choose the Data Table option.

At this time, the Data Table dialog box will appear.

  • Now, specify cell C6 as the Row input cell (Down Payment).
  • After that, select cell C8 as the Column input cell (Interest Rate) and click OK.

Here, as you can see, Excel fills in the data table.

Lastly, we have highlighted the cells that contain the target monthly payment.

Two variable data table of Loan Payment


3. Creating Two Variable Data Table of Future Value

Here, we will demonstrate another example of creating a two-variable data table for Future Value in Excel. Furthermore, for the data table firstly we will calculate the Future Value.

  • Select a different cell C12 where you want to calculate the Future Value.
  • Use the corresponding formula in cell C12.
=FV(C8/12,C6*C7,-C5)
  • Subsequently, press ENTER to get the result.

At this time, you can see the amount of the Future Value.

Creating Two Variable Data Table of Future Value in Excel

Formula Breakdown

  • Here, the FV function will return a Future Value of the periodic investment.
  • Now, C8 denotes the Annual Interest Rate.
  • Then, C6 denotes the total time period as Year.
  • Finally, C5 denotes the monetary value that you are paying at present.

Now, we will create a two-variable data table that summarizes the Future Value at various combinations of Interest Rate and Number of Years.

Here, we have attached the final data table.

 Two Variable Data Table of Future Value


Download Working File

Download the working file from the link below:


Conclusion

We hope you found this article helpful. Here, we have explained 3 suitable examples to create a two variable data table in Excel. You can visit our website to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Data Table in Excel | What-If Analysis in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

2 Comments
  1. I Have been following your Data analysis articles such as how to create variable tables. I just downloaded the lessons because of the nature of my work as a journalist.

    Now that I have started actually studying them, I found your method of teaching worth commending. They are just what you termed them, “step by step” and they are actually straight to the point, easy to follow, easy to understand.

    I also want to thank the owner of trumpexcel.com/excel website who directed my attention to your web when I was studying his 100 excel questions series.

    Your work is really commendable.

    Thanks.
    Uche Uche.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo