 The magazine of the Melbourne PC User Group Interval Solver 97 for MS Excel 97 Bob Burt bobburt@melbpc.org.au |
 |
It was with some trepidation that I approached the task of reviewing this add-in for Microsoft Excel 97. This is mainly because the knowledge required to have conceived, created and made full use of it is well beyond my understanding of mathematics. However, a second difficulty is that the mathematical expressions used in the manual to acquaint the prospective user with its major benefits really require further explanation and simplication. This would make the article too cumbersome (and too boring) for the average reader. So some terms, which may be new to you, are included without further explanation. The more curious reader can visit the following Internet site to obtain much more detail: http://www.delisoft.com/ExcelProducts/IntervalSolver
You can also download a free Evaluation Kit from the site.
Why Interval Solver?
Spreadsheets normally require the use of precise numbers, but most company projections as to future values of sales, cash flows and other decision-making information require the use of Interval Mathematics in order to compute safe bounds for function values. For example, you could be required to use the interest rate for next year to prepare projection data, which you might estimate to be between, say, 5% and 6%. You would represent this as the interval [5.0,6.0].
So Interval Solver (IS) encompasses the idea of Interval
Constraint Solving and extends spreadsheet usage to models that deal with uncertain data and involve
problem-solving under constraints which you determine. You could say it manages uncertainty.
The package
The add-in is provided on two 1.44 MB diskettes, which are supplied in a sachet included inside the User's Guide and Reference Manual.
Installation
You simply run Setup from the first disk and follow the instructions, changing to the second disk when asked to do so.
I was provided with Version 3.0.0.1 (Build 216), but on the advice of one of
the co-authors of IS, I visited their Internet site and downloaded a free upgrade, which, after installation,
converted my copy to Version 3.0.0.2 (Build 331).
What's new in version 3?
- The power of IS and Excel's Solver have been combined (see later)
- Logical constraints are supported (Truth (Boolean) values added)
- Integer-intervals of the form [x..y] are supported
- New arithmetical functions are included
- IS formulae may now contain user-defined names as permitted in Excel
- Solving is terminated dynamically by pressing Esc
- Ordinary formulae can be transformed automatically into interval formulae and vice-versa.
When you start Excel after the add-in has been installed the About Solver welcome dialog box opens and you have a choice of options
- Tutorial for IS, including pointers to example sheets
- Online Help
- User's Guide in electronic form
- Support button for sending email directly to Delisoft Ltd, Finland.
An additional menu, Interval is added to Excel and a floating IS Toolbox appears.
Using Interval Solver
As IS covers entirely new ground for most users of spreadsheets, it pays to study the tutorial and look at the example sheets. The following workbooks are included (the figures in parentheses indicate the number of example sheets in each)
- Business applications (4)
- Calculus (4)
- Electrical Design (2)
- Engineering applications (2)
- Geometry (2)
- IS features (3)
- Logical reasoning (2)
- Number theory (2)
- Optimisation (2)
- Physics applications (2).
- Budgeting
- Cash flow
- Discounting
- Portfolio management.
The Solver add-in
Excel already has a powerful optimisation tool, the Solver add-in. However, Solver and IS are complementary--IS provides the new mathematical approach to problem-solving and uses the simple Excel interface to formulate and bound your problems. Also, an IS model can be automatically transformed into a Solver model to exploit its computational power.
In passing, I should mention that if your copy of Excel 97 is derived from Microsoft Office 97 you will find that Excel does not, by default, install the add-in. You would expect to find Solver in the add-in subdirectory on the CD-ROM. However, somewhat strangely, it resides in its own subdirectory, Solver. You need to copy the add-in, plus its DLL to the appropriate subdirectory, in most cases this being C:\MSOffice\Excel\Library\Solver.
Goal Seeking
Excel also has the Goal Seek... command on the Tools menu for back solving. However, only one argument value can be searched for, whereas values for any number of cells can be traced with IS. The value, if found, is precise and a real number (not an integer), whereas IS can back solve intervals which may be real or integer, as you determine. Finally, as in the example sheet provided, you can show that Goal Seek... can fail even in simple tasks.
Conclusion
Since the early days of Visicalc, spreadsheet software has, as you would expect, incorporated many helpful and interesting features. However, all have rigidly retained the simple concept of computing output values from input values via a set of mathematical functions. Interval Solver provides a brilliant breakthrough in spreadsheet computations. While the fixed value inputs associated traditionally with spreadsheets can be accessed as before, the concept has been broadened to incorporate the latest technical developments in Interval Analysis and Artificial Intelligence. Uncertain data is represented as feasible value ranges, commonly known as intervals. With the example sheets provided, you will find that quite complex mathematical problems can be easily structured and resolved.
The authors, Dr Eero Hyvonen and Mr Stefano De Pascale, are research scientists at VTT Technical Research Centre of Finland, currently on leave to apply interval constraint technology to industry and business.
The authors can be reached by e-mail at eero.hyvonen@delisoft.com and stefano.depascale@delisoft.com
respectively.
Reprinted from the April 1999 issue of PC Update, the magazine of Melbourne PC User Group, Australia
