We can create one variable data table or two variable data table with the What-If Analysis feature. This tutorial will walk all levels of excel users on how to create a one variable data table and a two variable data table.
Figure 1 – How to make data table in Excel
What is a One Variable and Two Variable Data Table?
A one variable data table is a data table that has only one column or row of input values and multiple results while a two variable data table has two input values with only a single result.
How to Create a One-Variable Data Table
We will use the table in figure 2 to create a one-variable data table. We want to see how the cost per unit of item we produce will change. When the cost per unit of item increases, we might not be able to favorably compete with our competitors and when it reduces, we can compete. The table helps us to make business decisions.
Figure 2 – How to Create a One-Variable Data Table
- The formula in Cell B11 is
=B8/B10 - We will input the name of the cell containing the formula in Cell E5 and press enter
Figure 3 – One variable data table in Excel
- We will select Cell D5 to Cell E10
Figure 4 – How to create a one variable data table
- We will click on the Data tab and click on the What-If Analysis in the Forecast group
Figure 5 – Click What-If Analysis
- We will select Data Table from the drop-down
Figure 6 – Data table Excel dialog box
- We have only one variable that will change and that is the Regular Production Cost
- We will insert the Cell reference (B10) for the Column input Cell
Figure 7 – Column Input Cell
- We will press OK
Figure 8 – One variable data table
The values show how the cost of producing a unit of metal rod will change when will produce only 800, 900, 950, 700, and 750 units respectively.
How to Create a Two-Variable Data Table
We will use the table in figure 9 to create a two-variable data table. We want to see how the cost per unit of item we produce will change taking into consideration the Regular Production cost and the markup. The markup tells us how much we can add to the production cost so we can set a favorable selling price.
- The formula in Cell B11 is
=B8/B10 - The formula in Cell B13 is
=B11+(B11*B12) - We will input the name of the cell containing the formula in Cell E5 and press enter
Figure 9 – How to Create a two-variable data
- We will select Cell D5 to Cell F10
Figure 10 – How to create a two variable data table
- We will click on the Data tab and click on the What-If Analysis in the Forecast group
Figure 11 – Click What-If Analysis
- We will select Data Table from the drop-down
Figure 12 – Data table Excel dialog box
- We have only two variables that will change and that is the Regular Production Cost and the markup
- We will insert the Cell reference (B10) for the Column input Cell and Cell reference (B12) for the Row input cell
Figure 13 – Data table Excel dialog box
- We will press OK
Figure 14 – Two variable data table
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment