How to create a two variable data table in Excel mac

For versions of Excel: Excel for Office 365, Excel for Office 365 for Mac, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2008 for Mac, Excel 2007, Excel 2016 for Mac

Two Variable Data Table

Data tables are a built-in tool in Excel that allow you to "stress test" spreadsheet model outputs by automatically recalculating values of the formula for an output value under various assumptions. Data Tables allow you test "what-if" scenarios independently of the base case model. For example you might want to test the impact of changing price or cost on profit.

Calculate Multiple Results with Data Table

As the name suggests a two variable or two way data tables allow you the change two inputs and examine the impact on outcomes. Results can be graphed or have conditional formatting applied for easy interpretation.

You can download the spreadsheet used in this tutorial here

Two-Variable Data Table is a very significant tool for what-if data analysis. With the help of two-variable data tables, we can find all possible trends that can arrive by changing different values. For example, if we know the annual sales of a company, its percentage of expenses and growth. So, by keeping annual sales constant we can find the projected sales of a company by varying expenses and growth. Let’s learn how to create a two-variable data table.

Two-Variable Data Table

A data table is a table in which we can find multiple values by adding some fields and formulas. A two-variable data table is a table in which value changes exactly according to two parameters and the rest of the parameters are kept constant.  

Structure of two-variable data table

This could better be understood with an example, given the data set of a line, its x co-ordinate, the slope of a line, and its intercept. Find the different values of y co-ordinate keeping intercept as constant.

Following are the steps to create a two-variable data table:

Step 1: In cell B7, write the formula for finding the y co-ordinate of a line i.e. y = m*x + c. 

Step 2: Copy the same formula in cell D3. 

Step 3: Write X Coordinates row-wise i.e. D4:D8. Write slope values column-wise i.e. E3:H3.

Step 4: Select the range in which you want to make a data table i.e. D3:H8. 

Step 5: Go to the Data tab, and click on What-If analysis. A list appears. Click on the data table.

Step 6: A dialogue box appears. Select the cells used in the formula in cell D3. For the row input cell select cell B4 and column input cell select cell B5. Click Ok. 

Step 7: The two-variable data table is created. All the cells ranging from E4: H8 have different values of y coordinates for different x coordinates and slopes. 

In my last post, I taught you how to create a Single Variable Data Tables in Excel. As promised, in this tutorial I will cover creating a two variables data table. This allows you to flex two different variables simultaneously to see the output.

This tutorial assumes that you are comfortable with creating a single variable data table.

We’ll once again be looking at our Compound Interest example where you will be calculate what the amount you will receive at a certain interest rate after a specific period of time. You can also skip to the bottom of this post to download the example.

Create a new excel file and lay it out as shown in the screenshot above. Alternatively, you can start with the excel file from our previous example. In cell A5, enter the formula =B1*(1+B2)^B3.

As you can see, the $5,000 invested at 7.5% for 5 years will give $7,178.15.

A reminder of the formula for calculating compound interest:

A = P * (1 + r/n) ^ nt

Where:

• P = principal amount (initial investment)
• r = annual interest rate (as a decimal)
• n = number of times the interest is compounded per year
• t = number of years
• A = amount after time t

We will be varying the annual interest rate and the number of years to find the varying results.

After you have created the file according to the screenshot above, select the data range A5:F10.

In Excel, go to Data > What-If Analysis > Data Table or you can use the shortcut key Alt + D + T in this order in Windows.

This will popup a window where you will be asked to enter Row Input Cell and the Column Input Cell. Select the Column Input Cell as $B$2 and the Row Input Cell as $B$3 and hit OK.

The cells will be populated as shown in the screenshot below. As usual, you can choose to format the same currency.

How does it work?

Cells B6:F10 hold the formula {=TABLE(B3,B2)}.

Here, the values B4 to F4 are substituted for B3 in the formula in cell A5 and values A6 to A10 are substituted for B2 in the formula in cell A5 and the results are then populated accordingly.

Hence, for 1 year at 8%, the amount is $5,400, for 3 years at 10%, the amount is $6,655.00 and so on.

You can download the sample XLSX file for your reference.

Download “Two Variable Data Tables example” TwoVariableDataTable.zip – Downloaded 2858 times – 4 KB

One Variable Data Table | Two Variable Data Table

Instead of creating different scenarios, you can create a data table to quickly try out different values for formulas. You can create a one variable data table or a two variable data table.

Assume you own a book store and have 100 books in storage. You sell a certain % for the highest price of $50 and a certain % for the lower price of $20. If you sell 60% for the highest price, cell D10 below calculates a total profit of 60 * $50 + 40 * $20 = $3800.

To create a one variable data table, execute the following steps.

1. Select cell B12 and type =D10 (refer to the total profit cell).

2. Type the different percentages in column A.

3. Select the range A12:B17.

We are going to calculate the total profit if you sell 60% for the highest price, 70% for the highest price, etc.

4. On the Data tab, in the Forecast group, click What-If Analysis.

5. Click Data Table.

6. Click in the 'Column input cell' box (the percentages are in a column) and select cell C4.

We select cell C4 because the percentages refer to cell C4 (% sold for the highest price). Together with the formula in cell B12, Excel now knows that it should replace cell C4 with 60% to calculate the total profit, replace cell C4 with 70% to calculate the total profit, etc.

Note: this is a one variable data table so we leave the Row input cell blank.

7. Click OK.

Result.

Conclusion: if you sell 60% for the highest price, you obtain a total profit of $3800, if you sell 70% for the highest price, you obtain a total profit of $4100, etc.

Note: the formula bar indicates that the cells contain an array formula. Therefore, you cannot delete a single result. To delete the results, select the range B13:B17 and press Delete.

Two Variable Data Table

To create a two variable data table, execute the following steps.

1. Select cell A12 and type =D10 (refer to the total profit cell).

2. Type the different unit profits (highest price) in row 12.

3. Type the different percentages in column A.

4. Select the range A12:D17.

We are going to calculate the total profit for the different combinations of 'unit profit (highest price)' and '% sold for the highest price'.

5. On the Data tab, in the Forecast group, click What-If Analysis.

6. Click Data Table.

7. Click in the 'Row input cell' box (the unit profits are in a row) and select cell D7.

8. Click in the 'Column input cell' box (the percentages are in a column) and select cell C4.

We select cell D7 because the unit profits refer to cell D7. We select cell C4 because the percentages refer to cell C4. Together with the formula in cell A12, Excel now knows that it should replace cell D7 with $50 and cell C4 with 60% to calculate the total profit, replace cell D7 with $50 and cell C4 with 70% to calculate the total profit, etc.

9. Click OK.

Result.

Conclusion: if you sell 60% for the highest price, at a unit profit of $50, you obtain a total profit of $3800, if you sell 80% for the highest price, at a unit profit of $60, you obtain a total profit of $5200, etc.

Note: the formula bar indicates that the cells contain an array formula. Therefore, you cannot delete a single result. To delete the results, select the range B13:D17 and press Delete.