Solver for optimization in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to set the objective cell in Solver.

Excel

SolverOk SetCell:=Range("[1]"), MaxMinVal:=1

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

AD1

BC1

CB1

DA1

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a cell that does not contain the objective formula.

Using a cell outside the data range.

The objective cell is usually the cell where the result to optimize is calculated. Here, "A1" is the correct cell reference.

2fill in blank

medium

Complete the code to add a constraint in Solver.

Excel

SolverAdd CellRef:=Range("B2"), Relation:=2, FormulaText:=Range("[1]")

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

AC2

BA2

CD2

DE2

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong cell reference for the constraint value.

Confusing the cell to constrain with the constraint limit.

The constraint compares cell B2 to cell C2. So the formula text should be "C2".

3fill in blank

hard

Fix the error in the Solver code to solve the model.

Excel

SolverSolve UserFinish:= [1]

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

A0

B1

CTrue

DFalse

Attempts:

3 left

💡 Hint

Common Mistakes

Using False instead of True causes Solver to skip showing results.

Using numeric values instead of Boolean.

Setting UserFinish to True displays the Solver Results dialog after solving. False suppresses the results dialog.

4fill in blank

hard

Fill both blanks to set Solver to minimize the objective and use the GRG Nonlinear solving method.

Excel

SolverOk SetCell:=Range("A1"), MaxMinVal:=[1], Engine:=[2]

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

A1

B2

C3

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using 1 for MaxMinVal to minimize instead of maximize.

Choosing the wrong engine number for the solving method.

MaxMinVal=2 means minimize the objective. Engine=2 selects the GRG Nonlinear solving method.

5fill in blank

hard

Fill all three blanks to add a constraint that cell B3 must be less than or equal to 100.

Excel

SolverAdd CellRef:=Range("[1]"), Relation:=[2], FormulaText:=[3]

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

AB3

B1

C100

D2

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong relation number for less than or equal to.

Putting the cell reference or formula text in the wrong place.

CellRef is "B3". Relation 2 means less than or equal to. FormulaText is 100, the limit.