Data Tables (What-If) in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to create a one-variable data table that uses the input values in column A.

Excel

Select the output cell and enter the formula =[1] before creating the data table.

Drag options to blanks, or click blank then click option'

AB2*C2

BSUM(A2:A10)

CAVERAGE(B2:B10)

DIF(A2>10, 1, 0)

Attempts:

3 left

💡 Hint

Common Mistakes

Using aggregate functions like SUM or AVERAGE instead of the calculation formula.

Entering a formula that does not depend on the input cell.

The formula =B2*C2 calculates the output based on input values, which is necessary before creating a one-variable data table.

2fill in blank

medium

Complete the step to set up a two-variable data table with row input cell in B1 and column input cell in A2.

Excel

After selecting the data table range, open the Data Table dialog and enter [1] as the Row input cell.

Drag options to blanks, or click blank then click option'

AA1

BB2

CA2

DB1

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing the row input cell with the column input cell.

Selecting a cell outside the input range.

The row input cell should be B1 as it corresponds to the values in the row of the data table.

3fill in blank

hard

Fix the error in the data table setup by choosing the correct column input cell.

Excel

In the Data Table dialog, the Column input cell should be [1] to correctly link the column values.

Drag options to blanks, or click blank then click option'

AB1

BA2

CB2

DA1

Attempts:

3 left

💡 Hint

Common Mistakes

Using the row input cell as the column input cell.

Selecting a cell that does not contain input values.

The column input cell is A2 because it corresponds to the values in the first column of the data table.

4fill in blank

hard

Fill the blank to create a one-variable data table using input values in column A and output formula in B2.

Excel

Select the range including output and input values, then use Data Table with Column input cell [1] (leave Row input cell blank).

Drag options to blanks, or click blank then click option'

AB1

BA2

CA1

DB2

Attempts:

3 left

💡 Hint

Common Mistakes

Filling both input cells when only one is needed.

Using the wrong cell references for input or output.

For a one-variable data table with input values in column A, leave the Row input cell blank, and set the Column input cell to A1 where the original input value is located (the cell referenced by the formula).

5fill in blank

hard

Fill all three blanks to create a two-variable data table with formula in C3, row input cell in B1, and column input cell in A2.

Excel

Select the range including formula and input values, enter formula =[1] in C3, then set Row input cell [2] and Column input cell [3] in Data Table dialog.

Drag options to blanks, or click blank then click option'

AB2*C2

BB1

CA2

DA1

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up row and column input cells.

Entering the formula in the wrong cell.

The formula =B2*C2 is entered in C3. The row input cell is B1 and the column input cell is A2 to correctly link the data table inputs.

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