Dear Sirs,
Long ago, in a galaxy far away... I was the developer of the Saturn-Calc
Spreadsheet application for the PDP-11 (RT-11,TSX,RSX,RSTS O/S), VAX (VMS),
and PC (DOS only). Though a couple of $million worth were sold it met its
demise with the demise of DEC and the rise of Excel, it nonetheless had some
computing capabilities I long for today.
The most significant of these capabilities were provided by several
spreadsheet functions. I do not know if your parser and execute engine would
be capable of providing these but I'd like to mention them to you:
1) PUTR(expression, range,range...)
This function allowed you to evaluate the provided 'expression' once for
each cell in the ranges, with the result being put in the cell being
evaluated. The result was the sum of all evaluated results.
2) ADDVAL( expression, range,range)
Similar to the above, but simply returned the sum of the individual
expression evaluations.
3) EROW() and ECOL() - these were the key to the above functions. With each
cell evaluated, these would change to represent the cell reference being
evaluated from the ranges in PUTR and ADDVAL. You could then use these for
additional computed cell references.
With the above functions, our typical users had only to create a couple of
formulas for many spreadsheets. Take for example the following spreadsheet:
Row A B C
Quantity Price
1 Ordered Each Total
2 2 3.00 6.00 formula: A2*B2
3 4 4.00 16.00 formula: A2*B2
4 5 5.00 25.00 formula: A2*B2
5 TOTAL 47.00 formula: SUM(C2:C4)
Using the PUTR function this would be:
Row A B C
Quantity Price
1 Ordered Each Total
2 2 3.00 6.00 no formula
3 4 4.00 16.00 no formula
4 5 5.00 25.00 no formula
5 TOTAL 47.00 formula: PUTR(A[EROW()]*B[EROW()],C2:C4)
This meant that a change to formulas would not require changing the formulas
on each row if changes were made. If you wanted to add a column computing
discounts for various quantities, you'd only have to modify one formula.
Obviously, the "static" cell references in the PUTR would change with row
insertions and deletions, etc. as with most formulas.
Note the use of [] for computed cell referencing instead of CELL(expr,expr).
This allowed: A[rowexpr] or [colexpr]123 or [colexpr,rowexpr]
Anyhow, its an extremely powerful set of functions ( PUTR,ADDVAL,ECOL,EROW
), that I have yet to see duplicated.
I also had extremely easy to create and use user defined function capability
that allowed simply defining a parameterized function within any (usually
named) cell (generally hidden), that didn't require going into a separate
macro language. I also allowed the specification of a user library that was
simply a separate user defined spreadsheet that held these functions within
named cells. Local Functions were referenced as: $A$1(parameters), or name:
$MyFunction(...), and library functions were referenced as
$$MyFunction(...).
Functions were defined by something like
=#OptionalFunctName(paramname,parmname2)=formula where in the formula the
parameters were referenced by something like #paramname
Anyhow, just my two cents worth for something to consider. If I ever have
the time I might myself investigate if its possible to add such to the
OpenOffice calc product.
- Norm Byers (Consultant in the Microsoft world... - C++,C#)
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
