| 2020 doesn't care too much for recursion; it thinks there are circular
references.
Iteration can be achieved in a couple of ways.
The first is through "goal seeking," a process of automatically
modifying one cell, recalculating all other cells, to reach a target.
For example, if the cells were initialized as:
A1: 1.00
A2: (A1)^3-5*(A1)^2+8*(A1)-12
it would continue to modify A1 until it was 3.71618865899311 and the
value of A2 was 0.0000000000. I don't know what algorithm it uses to
determine how to adjust A1 each time.
Iteration can also be performed by use of macros, too. The following
macro calculates a factorial. The topmost row and leftmost column are
not part of the macro; they're column and row identifiers. The macro is
executed by telling the spreadsheet to execute D0. The first macro
command, "#QUIET," just tells it not to display intermediate
calculations.
A B C D
0 6 720 #QUIET
1 #LET([D11]=[A0])
2 #LET([B0]=1)
3 #LABEL(FACTLOOP)
4 #IF([D11]>1)
5 #THEN
6 #LET([B0]=[B0]*[D11])
7 #DECR([D11])
8 #BRANCH(FACTLOOP)
9 #ENDIF
10 #RETURN
11 1
This is a primitive macro. It "blows up" when the initial value (A0) is
greater than 33. It's not position-independent code; that is, it will
only calculate the factorial for the value in A0, it will always put it
in B0, and the macro itself must live in D0..D11.
My hope was to not have to use a macro. Unfortunately, I don't think
there's any other way.
/Dwayne
|