Solver V1.2
===========
by Ulrich Hornstein, http://psion.uh-lab.de


DESCRIPTION:
A macro for Psion / EPOC ER5 machines to execute a simple solver function as well as a search for minima or maxima of a given function for the built in Sheet application. It runs a little slow, but still much better than doing a trial and error method by hand.
Keywords: Solver, inverse function, "was wre wenn"

INSTALL:
Copy all files in the zip file into your Macros$ folder and make a shortcut to it with your favourite macro launcher.

USE:
In Sheet, you may have programmed a complicated function. One cell (called 'target cell') shows the result of the calculation. Another cell ('cell to be altered') is the variable that influences the target cell.
1) Search value mode (Solver mode):
The macro determines for you, which value in the cell to be altered is necessary to get a certain value (target value) in the target cell.
2) Search Maximum mode:
The macro determines for you if your function has a maximum value in a given from ... to area. The function is examined in a defineable number of steps, if a maximum exists there. The real maximum of the real function will usually be somewhere around this 'stepped' maximum value. You can zoom around the stepped value and repeat the maximum search until you found the maximum in the desired accuracy. This zooming can be done repeatedly. More steps give more accurate results but take longer to calculate.
3) Search Minimum mode:
same principle as in Maximum mode.

The running macro can be interrupted by pressing "Esc".

EXAMPLE FOR SEARCH VALUE MODE:
Make A5 the cell to be altered. Set the target cell to B5=SIN(A5*PI/180)    (*).
Which value has to be put in A5 to get 0,5 in the target cell B5?
Put the cursor on B5 and call the macro, then select "Search Value". Then edit the fields as follows:
Target cell: B5
Target value: 0.5
cell to be altered: A5
Start value: just guess a value that is near the expected result; we could guess 1.
Tolerance: how exact should the result be? 1e-5 means that the macro's result differs in less than 5 digits from the target value that you asked for.
# of iterations: after how many steps should the calculation stop, even if the result could not be determined to the desired accuracy. We enter 10.
Wait cycles: the macro must wait until all calculations in Sheet have been carried out before new results can be copied for further use in the macro. The default values are safe for a small sheet file. If you have many tens or hundreds of cells to be calculated in the sheet file, you may have to increase the wait cycles accordingly by trial and error. Just watch what happens during the runtime of the macro, you will get the picture.
After entering the above values, press Enter to run the macro. It will calculate within 4 steps of iteration the following:
to obtain 0,5 (with a tolerance of -7,32e-9) from the sine(x) operation, you must enter x=29,999999516. 30 would've been the exact value.

(*) This is of course a very simple example. In the case of a simple sine function, one would use the inverse function arcsine to solve that problem much faster. But such an inverse function does not always exist. The solver function should work for many kinds of functions y=f(x), even if there are hundreds of cells involved to express that function.

EXAMPLE FOR SEARCH MAXIMUM MODE:
Make A5 the cell to be altered. Set the target cell to B5=SIN(A5*PI/180).
Put the cursor on B5 and call the macro, then select "Search Maximum". Then edit the fields as follows:
Target cell: B5
cell to be altered: A5
From: 0
To: 10 (just guess in which area (from..to) a maximum might be)
Number of steps: 5
Wait cycles: see search value mode.
Press Enter to start the macro. Wait for completion. No maximum will be found. So you have to enlarge the search area. Search now from 0 to 100. A Maximum will be found, but it is still vague. So press "zoom". From/to will be adjusted to the values adjacent to the found maximum value. Press enter and wait for completion. Repeat zooming until the maximum shown does not differ much from it's adjacent values. In our case we find after four zoom-ins the maximum to be at 90,016 (instead of the exact value 90.0).
The statement (*) from above is valid here in a similar way.

IMPROVEMENTS:
In certain circumstances the search for value, minimum or maximum does not work ideally. I publish it with source code; anyone who knows how to program it better is invited to do so and send me his/her revised code, which can be published on my website. Please do not publish this code elsewhere, set a link to my site instead.

RISKS:
The macro will change the sheet file by writing into some of it's cells. Make a file backup before you run Solver. Also, don't blame me if the result is not what you expect. It may be incorrect. All usual disclaimers apply. If in doubt, read the general software disclaimer on my website.


Ulrich Hornstein, October 22th 2006
http://psion.uh-lab.de
(includes my public mail address)