Data Tables (What-If) in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of a One-Variable Data Table

You have a formula in cell B2: =A2*10. You create a one-variable data table with input values in cells A4:A8 (1, 2, 3, 4, 5). The data table formula is set to use cell A2 as the column input cell. What will be the value in cell B6 after the data table is calculated?

A 20

B 40

C 50

D 30

💡 Hint

Remember the data table substitutes the input cell value with each value from the input range.

The data table uses the value in A6 (which is 3) as input for A2 in the formula =A2*10, so the result is 3*10 = 30.

❓ Function Choice

intermediate

Choosing the Correct Input Cell for a Two-Variable Data Table

You want to create a two-variable data table to analyze the formula =B1*C1. You plan to use values in cells A3:A7 for variable 1 and B2:F2 for variable 2. Which cells should you set as the row and column input cells in the data table dialog?

A Row input cell: C1, Column input cell: B1

B Row input cell: A3, Column input cell: B2

C Row input cell: B1, Column input cell: C1

D Row input cell: B2, Column input cell: A3

💡 Hint

The row input cell corresponds to the variable changing across columns, and the column input cell corresponds to the variable changing down rows.

Since values in A3:A7 are down a column, they correspond to the column input cell (B1). Values in B2:F2 are across a row, so they correspond to the row input cell (C1).

🎯 Scenario

advanced

Analyzing Unexpected Data Table Results

You created a one-variable data table to analyze the formula =SUM(A1:A3) with input values in cells B4:B8. However, all results in the data table show the same value, 15, regardless of the input values. What is the most likely reason?

A The formula uses a range that does not include the input cell.

B The input cell for the data table is not linked to any cell in the formula.

C The data table input values are not numeric.

D The data table was created as a two-variable table instead of one-variable.

💡 Hint

Check if the formula depends on the input cell that the data table changes.

The formula sums a fixed range A1:A3, which does not depend on the input cell changed by the data table, so the result stays constant at 15.

❓ data_analysis

advanced

Interpreting a Two-Variable Data Table Output

You have a two-variable data table analyzing the formula =B1^C1. The row input cell is B1 with values 2, 3, 4 and the column input cell is C1 with values 1, 2, 3. What is the value in the cell where row input is 3 and column input is 2?

A 6

B 8

C 9

D 5

💡 Hint

Calculate 3 raised to the power of 2.

The formula is B1^C1. For row input 3 and column input 2, the result is 3^2 = 9.

🧠 Conceptual

expert

Effect of Circular References in Data Tables

You create a one-variable data table where the formula depends on the input cell, but the input cell's value is also calculated by a formula referencing the data table output. What will happen when you calculate the data table?

A Excel will calculate the data table normally without errors.

B Excel will show a circular reference warning and may not calculate the data table correctly.

C The data table will produce zeros for all outputs.

D Excel will automatically break the circular reference and calculate partial results.

💡 Hint

Think about what happens when a formula depends on itself indirectly.

Circular references cause Excel to warn the user and can prevent correct calculation of data tables because the input depends on the output.

Practice

1. What is the main purpose of a Data Table in Excel's What-If Analysis?

easy

A. To quickly see how changing input values affects the result

B. To create charts from data automatically

C. To sort data in ascending or descending order

D. To protect cells from editing

Solution

1. Step 1: Understand What-If Analysis

   What-If Analysis helps explore different input values to see their effect on results.

2. Step 2: Identify Data Table's role

   Data Tables let you test many input values quickly to observe changes in output.

3. Final Answer:

   To quickly see how changing input values affects the result -> Option A

4. Quick Check:

   Data Tables show output changes for input variations [OK]

Hint: Data Tables show results for many inputs fast [OK]

Common Mistakes:

• Confusing Data Tables with chart creation
• Thinking Data Tables sort data
• Believing Data Tables protect cells

2. Which of the following is the correct way to create a one-variable Data Table in Excel?

easy

A. Apply conditional formatting to the input cells

B. Type multiple formulas manually for each input value

C. Use the Sort feature under Data tab

D. Select the output cell and input values, then use Data > What-If Analysis > Data Table

Solution

1. Step 1: Recall Data Table creation steps

   Data Tables are created via Data > What-If Analysis > Data Table, not by typing formulas manually.

2. Step 2: Identify correct method

   Select the output formula cell and input values, then use the Data Table option to generate results.

3. Final Answer:

   Select the output cell and input values, then use Data > What-If Analysis > Data Table -> Option D

4. Quick Check:

   Data Table creation uses menu, not manual formulas [OK]

Hint: Use Data > What-If Analysis > Data Table to create tables [OK]

Common Mistakes:

• Typing formulas manually for each input
• Using Sort instead of Data Table
• Confusing conditional formatting with Data Tables

3. Given a one-variable Data Table where the formula in cell B2 is =A2*10 and the input values in column A are 1, 2, 3, what will be the output values in the Data Table?

medium

A. [0, 0, 0]

B. [10, 20, 30]

C. [100, 200, 300]

D. [1, 2, 3]

Solution

1. Step 1: Understand the formula and inputs

   The formula multiplies the input value in A2 by 10.

2. Step 2: Calculate outputs for each input

   For inputs 1, 2, 3: outputs are 1*10=10, 2*10=20, 3*10=30.

3. Final Answer:

   [10, 20, 30] -> Option B

4. Quick Check:

   Multiply inputs by 10 to get outputs [OK]

Hint: Multiply each input by 10 to find outputs [OK]

Common Mistakes:

• Confusing inputs with outputs
• Multiplying by 100 instead of 10
• Assuming outputs are zero

4. You created a two-variable Data Table but all output cells show #REF! error. What is the most likely cause?

medium

A. The formula cell reference is missing or incorrect in the Data Table setup

B. You forgot to enter input values

C. The worksheet is protected

D. You used a one-variable Data Table instead of two-variable

Solution

1. Step 1: Understand #REF! error in Data Tables

   #REF! usually means a broken or invalid cell reference in the formula.

2. Step 2: Check Data Table setup

   If the formula cell reference is missing or incorrect, the Data Table cannot calculate results, causing #REF! errors.

3. Final Answer:

   The formula cell reference is missing or incorrect in the Data Table setup -> Option A

4. Quick Check:

   Broken formula references cause #REF! errors [OK]

Hint: Check formula cell reference in Data Table setup [OK]

Common Mistakes:

• Assuming missing inputs cause #REF!
• Blaming worksheet protection without checking references
• Confusing one-variable and two-variable tables

5. You want to analyze how changing both price and quantity affects total revenue using a two-variable Data Table. Price values are in row 2 (cells B2:E2) and quantity values in column A (cells A3:A6). The total revenue formula is =B1*C1 where B1 is price and C1 is quantity. How should you set up the Data Table to get correct results?

hard

A. Place the formula in cell A1, select range A1:E5, then use Data Table with Row input cell = C1 and Column input cell = B1

B. Place the formula in cell B1, select range B1:E6, then use Data Table with Row input cell = C1 and Column input cell = B1

C. Place the formula in cell A2, select range A2:E6, then use Data Table with Row input cell = B1 and Column input cell = C1

D. Place the formula in cell A2, select range A2:E6, then use Data Table with Row input cell = C1 and Column input cell = B1

Solution

1. Step 1: Position formula correctly

   Formula must be at the intersection of input values: cell A2 (top-left corner of data table range).

2. Step 2: Select correct range and input cells

   Select A2:E6 to cover all price (row) and quantity (column) values. Set Row input cell = B1 (price) and Column input cell = C1 (quantity) matching formula references.

3. Final Answer:

   Place the formula in cell A2, select range A2:E6, then use Data Table with Row input cell = B1 and Column input cell = C1 -> Option C

4. Quick Check:

   Formula at top-left, correct input cells for row and column [OK]

Hint: Formula top-left; row input = price; column input = quantity [OK]

Common Mistakes:

• Placing formula outside data table range
• Swapping row and column input cells
• Selecting wrong range for Data Table