Wilkinson House, Jordan Hill Road
OX2 8DR Oxford
New NAG and Excel example: Evaluation of nonlinear expressions using Excel
Routines which take only simple parameters such as scalar values, vectors and matrices can have all their arguments input via the Excel worksheet however for routines that require nonlinear expression such as those routines with callback functions it isn't so straightforward. One method is to simply write the callback in VBA however you lose the interactivity gained from using Excel as every time you want to change the problem you would have to edit the VBA code. This isn't a big deal but there is another way!
The VBA function evaluate() can be used for just this purpose and, along with a few tricks, allows natural input of nonlinear expressions via the worksheet. The demo for the NAG optimization routine e04jcf takes advantage of this method. As you can see the objective function is expressed naturally on the sheet along with the simple input parameter required for the routine e04jcf:
Those with a keen eye will have picked up on the fact that the objective function is described in terms of X1...X4 but the initial estimation is given in cells B11...B14. The trick is that once the initial estimate has been read from the worksheet and stored in a VBA array, it gets copied back to the sheet in cells X1...X4 before the NAG routine e04jcf is called. Of course you could describe the objective function in terms of which-ever cells you like. It should be noted that this is not the most efficient method due to the extra copying of data that is required so it may not be suitable if your problem is large.
This technique can be exploited with many other routines in the NAG Library such as root finding, quadrature, systems of nonlinear equations and many more. Once a worksheet has been created, it can be used by anyone who is familiar with Excel even if they do not know VBA.
Tip: You can hide column X by right-clicking the column header and selecting hide or set the font colour to white if you don't want the intermediate values displayed. The demo for e04jcf can be downloaded from our Excel page.
The use of information published here for personal information and editorial processing is generally free of charge. Please clarify any copyright issues with the stated publisher before further use. In the event of publication, please send a specimen copy to firstname.lastname@example.org.