How to Create a Two Variable Data Table in Excel

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. 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. 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.

How to create a two-variable data table in Excel 2013

The setup of a two-variable data table.

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 table (one-variable or two-variable data table) to see the effects on other formulas.

Read more: Sensitivity Analysis in Excel Using One or Two Variables Data Table

Creating a two-variable data table in Excel

We have used another example in this article to work with the two-variable data table. 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.

How to create a two-variable data table in Excel 2013

This worksheet calculates the net profit from a direct-mail promotion of a company.

This data table model uses two input cells: the number of mail sent and the expected response rate. The following items appear in the Parameters area:

  • 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.
    We have used the following formula: =IF(B4<200000,0.25,IF(B4<300000,0.18,0.15))
  • Mailing costs per unit: It is a fixed cost, $0.30 per unit mailed.
  • Responses: The number of responses, calculated from the response rate and the number mailed. The formula in this cell is the following: =B4*B5
  • 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: =B10*B11
  • Print + mailing costs: This formula calculates the total cost of the promotion: =B4*(B8+B9)
  • Net Profit: This formula calculates the bottom line — the gross profit minus the printing and mailing costs.

If you enter values for the two input cells, you will see that the net profit varies quite a bit, often going negative to produce a net loss.

The following figure shows the setup of a two-variable data table that summarizes the net profit at various combinations of mail number and response rate. The table locates in the range E4: M14. Cell E4 contains a formula that references the Net profit cell: B14.

How to create a two-variable data table in Excel 2013

Preparing to create a two-input data table [Click on the image to enlarge]

To create the data table, follow the following steps:

  1. Enter the response rate values in G3: N13.
  2. Enter the number mailed values in F4: F13.
  3. Select the range G3: N13 and choose Data ➪ Data Tools ➪ What-If Analysis ➪ Data Table. The Data Table dialog box will appear.
  4. Specify B5 as the Row input cell (the response rate) and cell B4 as the Column input (the number mailed).
  5. Click OK. Excel fills in the data table.

The following figure shows the final result. From this table, you can find out that a few of the combinations of response rate and quantity mailed result in a profit rather than a loss.

How to create a two-variable data table in Excel 2013

The final result of the two-input data table [Click on the image to enlarge].

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

Download Working File

Download the working file from the link below:

mortgage-loan-data-table1.xlsx

direct-mail-data-table.xlsx

Read More…

How to Find Correlation between Two Variables in Excel


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

2 Comments
  1. Reply
    Uche Uche June 23, 2018 at 4:34 PM

    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.

    • Reply
      Kawser June 23, 2018 at 4:53 PM

      Thanks for your feedback, Uche Uche.
      Feeling happy to know that you found the articles helpful.
      Best regards
      Kawser Ahmed

    Leave a reply