From: flavio@cflav.sublink.org (Flavio Casetta)
Newsgroups: comp.sys.hp48
Subject: Spreadsheet for the SmartROM 1:C
Date: 3 Oct 92 23:31:54 GMT
Organization: Flavio Casetta, Reg'd HP ISV & OEM of handheld computers.

Hi folks,

the third release of the SmartROM is available.
I will not waste time to depict why it is worth buying.

The facts speak for me.

A major feature of the latest release is the possibility of configuring
the MatrixWriter to work as a spreadsheet, as a data browser or simply as 
'Matrix Writer'.

Since SmartROM's Matrix Writer is programmable, you can call the environment
from a program and decide beforehand what the user must do with it.
A typical task I envision ( and many other people as well) is to gather 
data in some way, organize them in an ordered format and extrapolate a result 
(possibly in a graphic format) through the Spreadsheet.

I apologize for the overwhelming usage of 'some' but I don't want
to restrict your phantasy.

Configuring the environment is a matter of setting or clearing 
some user flags and eventually taking over during inizialization
and/or shutdown by means of hooks.
I will not cover in depth how takeover routine should work, because I 
deem that very few guys will ever try to see inside the black box.
Who is interested may ask me for specific information.

The following table summarizes user flags assignements:

Flag     when set                    when clear

32       recalc on                   recalc off
33       symbolics only              any object in a cell
34       locks rows                  unlocks rows
35       locks columns               unlocks columns
36       no editing                  editing allowed
37       enable startup hook         disable startup hook
38       enable shutdown hook        disable shutdown hook
39       alternate display handler   standard display handler

Some combinations of flags are recurrent and grouping them in a single 
command is highly recomendable. The User's manual shows several meaningful
combinations.

When flag 32 is set, each cell containing a symbolic expression or global name
is evaluated and the result displayed (if any); errors are trapped, exceeding
objects removed from the stack.
Since you can set or clear the flags from the command line, you could
effectively create a command called CALCon simply made up of
<< 32 SF >> or its inverse 'CALCoff' << 32 CF >> and use them from the 
command line for disabling or enabling the recalculation.

Referencing cells within the matrix writer can be done in different fashions:

by relative pointers : Mat(MaxRow-1,MaxCol-1) where MaxCol and MaxRow 
                       are the current matrix dimensions
by absolute pointers:  Mat(1,1) (quickest method)

magically:    Mat(~,1)    the cell in the leftmost position in this row
              Mat(~,~-1)  the cell near my left side
              Above       everything in the column above this cell
              Left        everything lying near my left side
              Col(~-1)    the cell above this one
              Row(MaxCol) the rightmost cell

This is the 'dream team' of the Spreadsheet.

We will refer to the '~' placeholder as 'Magic Johnson' because it is really 
the Playmaker.
He always knows for which team is playing (rows or columns) and
his current value, no matter how complex the function is.
All this tremendous flexibility costs a bit in terms of speed, but
not too much indeed.
On the other hand you can freely add rows or columns at no extra cost.

Finding the sum of a column is easy and can be done in a variety of ways:

Sigma represents the corresponding greek character (HP48 char 133);

Sigma(r=begin,end,Mat(r,c))
or 
Sigma(r=1,~-1,Mat(r,~)) or Sigma(r=1,~-1,Col(r))

More exotic functions can be defined on the fly:

Sum the product between odd and even columns:
Sigma(c=begin,end,Mat(r,2*c+1)*Mat(r,2*c))

It is important to remember that frequently used functions had better to
be defined externally for storage saving.

Now I will introduce a feature unique to the HP48 world (for what I've
seen up to date).

The sum of a column (row) can be condensed in a very short form by
transferring the task of interpreting user's desires to an intelligent 
function SumCol defined externally.

SumCol does the following things:

examines the list containing the objects above the current cell, detects 
invalid object types, gets the first interval of contiguous objects 
(suitable for addition) and discard the boudaries (where likely the 
formula itself and garbage strings lie on), then sums remaining cells 
(if any).

SumCol:

<< Above                        Gets above elements
   FALSE + REV FALSE +          Pads with garbage (shortcut)
   { 0 1 6 7 9 13 }             
   CHSET?                       Searches for the black sheeps (always finds 
                                at least two of them astray).
   DROP DUP                     Duplicates mismatches
   0 OVER +                     Prepares before differentiating
   { - } LVOP                   Gets gaps
   1 SRDIFF                     Seeks the first interval
   IF DUP THEN	                 If found then
      1 - SPLIT                    takes the raw interval
      ROT DROP                     and cancels garbage
      CAR
      3 PICK SIZE SWAP -           gets first boundary
      1 +                          adjusts both for SUB
      SWAP ROT SIZE SWAP -         gets second boundary
      1 -
      'k' CAB 'Col(k)' Sigma       makes the summation
                                   it is necessary to use Col here for
                                   ensuring that internal pointers are updated.
   ELSE                         else
      DROP2 DROP 0                 returns 0 
   END                          the end.
>>

to obtain the SumRow function, just replace Above with Left and Col with
Row. Alternatively you could define a generic function Sum where Above or Left
are passed as parameters unevaluated (through the function QUOTE) and 
the program calls the Row or Col function depending on what you passed to it:

'Sum(QUOTE(Above))' or 'Sum(QUOTE(Left))'

could defined as follows :

<< -> block
   << block EVAL                evaluates Above or Left
        FALSE + REV FALSE +
        ...                     the same as before ...
        'k' CAB
        IF block 'Above' SAME
        THEN 'Col(k)'
        ELSE 'Row(k)'
        END Sigma
      ELSE
      ...                       the same as before ...
   >>
>>


A typical scenario where SumCol is well worth using:

          1                2         3          4      5...n-1   n
{
  { "  Products   "     "Price"  "Discount" "Quantity"  ...   "Ext. price" }
  { "------------"      "-----"  "--------" "--------"  ...   "----------" }
  { "SmartROM"           169_$       0           1      ...         175_$  }
  { "SmartConverter"     105_$       0           1      ...         115_$  }
  { "ESA Assembler"        0        100          1      ...           0_$  }
  { "Spreadsheet"          0        100          1      ...           0_$  }
  { "=============="    "===="   "========" "========"  ...   "==========" }
  { "Total"               "-"       "-"         "-"     ...         SumCol }
  { "V.A.T"               ""        ""          ""      ... %(4.5,Row(~-1) }
  { "Credits"             ""        ""          ""      ...        '-25_$' }
  { "Remit Amount"        ""        ""          ""      ...         SumCol }
        ...               ...       ...         ...     ...           ...  }
}
As you probably notice there is neither a reference to the current column 
number, nor to the beginning and ending row: 
all is carried out automatically (and magically).

The first occurrence of SumCol collects only 4 objects from 
column n:

175_$, 115_$, 0_$ and 0_$ (we got the values through a function like this:

'EXTPRICE=%(100-Row(3),Row(2))*Row(4)+...'

the last occurrence of SumCol adds the previous amount, the VAT and the 
Credits.

Note also that we used currency units defined through the pseudo unit '?'.

To do it:
first store 1_? as '$'
then store 1200_? as 'ITL'
.5328_? as 'DFL'

Although writing the formulas extensively may annoy somebody, the 
flexibility allowed by the method lets you implement ** any ** kind
of function.


__
* flavio@cflav.sublink.org (Flavio Casetta)
 ---------------------------------------------------------------------
| The usual disclaimer does not apply      |   +59 440404 (Vox populi) |
| ForCALC's opinions are necessarily mine  |   +59 304490 (Fax dei)    |