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.