(11C) (15C) Invisicalc Spreadsheet

[Michael Zinn]

Spreadsheet software for your 11C or 15C!

How to use

* Register 0 contains the number of computed registers, e.g. 3 will mean that registers 1,2,3 will be computed
* Put formulars for registers under the label of the same number, e.g. if register 3 should contain the sum of register 1 and 2 you'd do LBL 3, RCL 1, RCL 2, +, RTN

Example: Tax
Let's say you are freelancing and for each paycheck you have to put 30 percent aside for taxes. You want to have these registers:
4: Paycheck total
5: Tax percentage
1: Tax
2: What's left after tax

Since you want to compute registers 1 and 2, you set R_0 to 2.

Set R_5 to 30 (For the 30% tax)

Write the formulars for registers 1 and 2:

Regiseter 1: Tax

Code:

LBL 1
RCL 4
RCL 5
%
FIX 2
RND
RTN

Register 2: What's left after tax

Code:

LBL 2
RCL 4
RCL 1
-
RTN

Then you put your paycheck total in register 4, run Invisicalc and when it's done, you find the tax and what's left after tax in register 1 and 2.

Invisicalc Source Code

Code:

LBL B:
CF 0  ; Flag[0] is for detecting changes and rerunning until nothing changes
RCL 0 ; Register 0 contains the count of registers to calculate, from 1 to R_0
STO I

LBL .9 ; recalculate all computed registers
  GSB I
  RCL (i)
  x=y? ; did the value change?
    GTO .8

    x<>y
    STO(i)
    SF 0 ; remember change
  LBL .8

  DSE I
  GTO .9
  
F? 0    ; anything changed?
  GTO B ; do another round
RTN     ; done

[Thomas Klemm]

We can define each of the red cells as the average of the four surrounding cells:

\(
\require{color}
\begin{array}{|c|c|c|c|}
\hline
& 5 & 6 & \\
\hline
.2 & \color{red} 1 & \color{red} 2 & 7 \\
\hline
.1 & \color{red} 3 & \color{red} 4 & 8 \\
\hline
& .0 & 9 & \\
\hline
\end{array}
\)

This results in the following programs for cells 1, 2, 3 and 4:

Code:

   001 { 42 21 12 } f LBL B
   002 { 43  5  0 } g CF 0
   003 {    45  0 } RCL 0
   004 {    44 25 } STO I
   005 { 42 21 .9 } f LBL 19
   006 {    32 25 } GSB I
   007 {    45 24 } RCL (i)
   008 { 43 30  5 } g TEST 5
   009 {    22 .8 } GTO 18
   010 {       34 } X<=>Y
   011 {    44 24 } STO (i)
   012 { 43  4  0 } g SF 0
   013 { 42 21 .8 } f LBL 18
   014 { 42  5 25 } f DSE I
   015 {    22 .9 } GTO 19
   016 { 43  6  0 } g F? 0
   017 {    22 12 } GTO B
   018 {    43 32 } g RTN
   019 { 42 21  1 } f LBL 1
   020 {    45  2 } RCL 2
   021 { 45 40  5 } RCL + 5
   022 { 45 40 .2 } RCL + 12
   023 { 45 40  3 } RCL + 3
   024 {        4 } 4
   025 {       10 } /
   026 {    43 32 } g RTN
   027 { 42 21  2 } f LBL 2
   028 {    45  1 } RCL 1
   029 { 45 40  6 } RCL + 6
   030 { 45 40  7 } RCL + 7
   031 { 45 40  4 } RCL + 4
   032 {        4 } 4
   033 {       10 } /
   034 {    43 32 } g RTN
   035 { 42 21  3 } f LBL 3
   036 {    45  1 } RCL 1
   037 { 45 40  4 } RCL + 4
   038 { 45 40 .0 } RCL + 10
   039 { 45 40 .1 } RCL + 11
   040 {        4 } 4
   041 {       10 } /
   042 {    43 32 } g RTN
   043 { 42 21  4 } f LBL 4
   044 {    45  2 } RCL 2
   045 { 45 40  8 } RCL + 8
   046 { 45 40  9 } RCL + 9
   047 { 45 40  3 } RCL + 3
   048 {        4 } 4
   049 {       10 } /
   050 {    43 32 } g RTN

Please note that this leads to circular dependencies.

Example

We can define arbitrary values on the boundary:

2 STO 5
3 STO 6
4 STO 7
5 STO 8
4 STO 9
7 STO .0
5 STO .1
1 STO .2

We have 4 cells to calculate:

4 STO 0
GSB B

Results

RCL 1
2.83333

RCL 2
3.54167

RCL 3
4.79167

RCL 4
4.33333

\(
\begin{array}{|c|c|c|c|}
\hline
& 2 & 3 & \\
\hline
1 & \color{red} 2.83 & \color{red} 3.54 & 4 \\
\hline
5 & \color{red} 4.79 & \color{red} 4.33 & 5 \\
\hline
& 7 & 4 & \\
\hline
\end{array}
\)

Interpretation

Consider the black numbers given as the temperature at the boundary of a sheet of metal.
The red numbers then indicate the temperature inside the sheet.

[Thomas Klemm]

Addendum

Suppose that the cell \(u_{x,y}\) is surrounded as follows:

\(
\require{color}
\begin{array}{|c|c|c|}
\hline
& u_{x,y-1} & \\
\hline
u_{x-1,y} & \color{red} u_{x,y} & u_{x+1,y} \\
\hline
& u_{x,y+1} & \\
\hline
\end{array}
\)

Then we define:

\(
\begin{align}
u_{x,y} &= \text{average}\left(u_{x,y-1}, u_{x-1,y}, u_{x+1,y}, u_{x,y+1}\right) \\
\\
&= \frac{u_{x,y-1} + u_{x-1,y} + u_{x+1,y} + u_{x,y+1}}{4} \\
\end{align}
\)

From this we conclude:

\(
\begin{align}
4 \cdot u_{x,y} &= u_{x,y-1} + u_{x-1,y} + u_{x+1,y} + u_{x,y+1} \\
0 &= \left[u_{x+1,y} - 2 \cdot u_{x,y} + u_{x-1,y}\right] + \left[u_{x,y+1} - 2 \cdot u_{x,y} + u_{x,y-1}\right] \\
&= \left[(u_{x+1,y} - u_{x,y}) - (u_{x,y} - u_{x-1,y})\right] + \left[(u_{x,y+1} - u_{x,y}) - (u_{x,y} - u_{x,y-1})\right] \\
\end{align}
\)

We can abbreviate this double difference:

\(
\begin{align}
0 &= \left[(u_{x+1,y} - u_{x,y}) - (u_{x,y} - u_{x-1,y})\right] + \left[(u_{x,y+1} - u_{x,y}) - (u_{x,y} - u_{x,y-1})\right] \\
&= \Delta^2_x u_{x,y} + \Delta^2_y u_{x,y} \\
\end{align}
\)

This can be interpreted as the sum of the “heat” flowing into or out of the cell \(u_{x,y}\).
Since this value is \(0\), we have reached a steady state.

Compare this with the Laplace equation:

\(
\begin{align}
0 &= \Delta u \\
&= \frac{\partial^2 u}{\partial x^2} + \frac{\partial^2 u}{\partial y^2} \\
\end{align}
\)

Maximum principle

I'm leaving it as an exercise to conclude that both the maximum and the minimum must be found on the boundary.

Uniqueness of the Solution
How can the maximum principle be used to show the uniqueness of the solution?
Hint: Assume that there are two different solutions.
Of course, they must agree on the boundary.
What can be concluded about the difference of these two solutions?

Excel

Enable iterative calculation
You can enable this option in: File > Options > Formulas

Create the formula
Use a new sheet.
Insert into cell A2 the value 0.
In cell B2 insert the function which calculates the average of the four adjacent cells: =AVERAGE(A2;B1;C2;B3)

Propagate the function
Horizontally
Propagate the formula in B2 horizontally.
To do this, move the cursor over the bottom right corner until + appears.
Now drag this over the cells to the right of B2.
It doesn't really matter how many cells you cover. About 10 is fine.

Vertically
While all of these cells are still active, hover your cursor over the bottom right corner of the rightmost cell again and drag + down.
Again, it doesn't really matter how far down you go.
You should end up with a rectangle filled with zeros.

Fill the border
Use the same technique to propagate the 0 in cell A2 around the boundary of the rectangle.
Make sure not to override the existing values.

Create graphic
Select the whole rectangle.
Use Insert > Charts > Other Charts > Surface > 3D-Surface to create a 3D graph of these values.

Modify values
Change one of the values on the boundary (not in one of the corners).
What happens?
Use different values along the boundary.
How can you modify the graph?

Modify the domain
Circle
You can modify the domain: it doesn't have to be a rectangle.
You can use Bresenham's circle algorithm to define an approximation of a circle inside the rectangle.
Simply delete cells that aren't needed.
You just need to make sure that all values on the boundary are defined values.

Other shapes
How about a disc with a hole? Or other completely irregular shapes?

Example

[Michael Zinn]

(11-12-2024 12:48 AM)Thomas Klemm Wrote:  We can define each of the red cells as the average of the four surrounding cells:

\(
\require{color}
\begin{array}{|c|c|c|c|}
\hline
& 5 & 6 & \\
\hline
.2 & \color{red} 1 & \color{red} 2 & 7 \\
\hline
.1 & \color{red} 3 & \color{red} 4 & 8 \\
\hline
& .0 & 9 & \\
\hline
\end{array}
\)
[...]

Thanks for this great example! Did it terminate (how long did it take?), or did you abort it at some point and got an approximate result? Since InvisiCalc does not detect circular dependencies, it can run forever, though in this case, it converges in theory, but I never tested it and wonder if it could also go into an infinite loop near the final result, where the values "flicker" by some tiny amount forever.

[Thomas Klemm]

(11-12-2024 10:55 AM)Michael Zinn Wrote:  Did it terminate (how long did it take?), or did you abort it at some point and got an approximate result?

Well, I cheated a bit and used: JRPN 15C
It's nice that you can import programs from the clipboard.
Just copy and paste my program list into: File > Import Program > Import from Clipboard

It's a simulator and therefore differs from a real HP-15C.
And I set the speed to 0 ms/Program Instruction.
So the program returns the result immediately.

I tried to solve the Dirichlet problem with several spreadsheet programs.
So far only Excel allowed me to declare circular references.
And it has to be enabled explicitly.

And then we have your little program that just works out of the box.

[Thomas Klemm]

Meanwhile I tried my example in an emulator that runs at the same speed as a real HP-15C.
It took a couple of minutes but I didn't take the time.
It took: 2'05"