In this example we will use linear constraints in xSolverPro to solve the Boggs-Tolle problem number
3 from CUTE. First we set up the spreadsheet with the following objective function:
t1=p[0]-p[1]
t2=p[1]+p[2]-2.0
t3=p[3]-1.0
t4=p[4]-1.0
x=t1*t1+t2*t2+t3*t3+t4*t4
This gives us just one target function for 5 variables, but since we will be setting linear constraints on
the variables that's OK as long as each variable has at least one entry in the table. The way the solver
works, however, forces us to define at least 5 residuals so we just make 4 copies of the function at B2
and copy them into B3 through B6 and add them to the list of residuals. Defining variables and
residuals is done by highlighting the proper cells and selecting 'Add Variables' and 'Add Residuals' as
discussed in the example for solving the polynomial fit problem with xSolverLite.
After setting up the spreadsheet, opening the Setup Screen, pressing the 'Set Constraints' button and
selecting 'Linear' in the 'Constraint Type' radio box, your screen should look like Figure 1.
3 from CUTE. First we set up the spreadsheet with the following objective function:
t1=p[0]-p[1]
t2=p[1]+p[2]-2.0
t3=p[3]-1.0
t4=p[4]-1.0
x=t1*t1+t2*t2+t3*t3+t4*t4
This gives us just one target function for 5 variables, but since we will be setting linear constraints on
the variables that's OK as long as each variable has at least one entry in the table. The way the solver
works, however, forces us to define at least 5 residuals so we just make 4 copies of the function at B2
and copy them into B3 through B6 and add them to the list of residuals. Defining variables and
residuals is done by highlighting the proper cells and selecting 'Add Variables' and 'Add Residuals' as
discussed in the example for solving the polynomial fit problem with xSolverLite.
After setting up the spreadsheet, opening the Setup Screen, pressing the 'Set Constraints' button and
selecting 'Linear' in the 'Constraint Type' radio box, your screen should look like Figure 1.
Figure 1
We are going to use three constraints:
p[0]+3*p[1]=0
p[2]+p[3]-2*p[4]=0
p[1]-p[4]=0
To add them to xSolverPro hit the 'Add' button three times. Your screen should look like Figure 2.
p[0]+3*p[1]=0
p[2]+p[3]-2*p[4]=0
p[1]-p[4]=0
To add them to xSolverPro hit the 'Add' button three times. Your screen should look like Figure 2.
Figure 2
Enter the rest of the coefficients into the linear constraint table as they appear in Figure 3. Now just
click the red box with the white X in the upper right hand corner of the 'Set Solver Constraints'
window and press the 'Run Solver' button. Your results should look like Figure 4.
At this point you may want to save your work. Setting up problems with xSolverPro is not that
difficult, but it can become tedious to set the same problem up over and over. Click the 'Save' button
to save a solve template to a file and recall it later with load. Remember cell references have a
spreadsheet name in them so if you save a template you can only load that template into a workbook
that has spreadsheets with the same name as each of the variables and residuals.
click the red box with the white X in the upper right hand corner of the 'Set Solver Constraints'
window and press the 'Run Solver' button. Your results should look like Figure 4.
At this point you may want to save your work. Setting up problems with xSolverPro is not that
difficult, but it can become tedious to set the same problem up over and over. Click the 'Save' button
to save a solve template to a file and recall it later with load. Remember cell references have a
spreadsheet name in them so if you save a template you can only load that template into a workbook
that has spreadsheets with the same name as each of the variables and residuals.
Figure 4
Constraint functions in xSolverPro are aranged in rows in the 'Set Solver Constraints' screen with
the columns denoting spreadsheet cell addresses and the final result, listed as 'Constrain to'. To
add a constraint like the ones described just above we take the coefficients of each of the function
and place them in a row of the Linear Constraints table.
the columns denoting spreadsheet cell addresses and the final result, listed as 'Constrain to'. To
add a constraint like the ones described just above we take the coefficients of each of the function
and place them in a row of the Linear Constraints table.
Figure 3
p[2] + p[3] - 2*p[4] = 0
0*p[0] + 0*p[1] + 1*p[2] + 1*p[3] + -2*p[4] = 0
0 0 1 1 -2 0
0*p[0] + 0*p[1] + 1*p[2] + 1*p[3] + -2*p[4] = 0
0 0 1 1 -2 0
The function:
can be expressed as
which becomes
like row 2 in figure 3.
can be expressed as
which becomes
like row 2 in figure 3.