5.8. Spreadsheet calculations

---

5.8.1. Overview

Spreadsheet style calculations have been introduced into SDF using the standard [[ ]] syntax with a prefix of + (or =) indicating that the expression is to be evaluated by the calculation routines.

This extension has been loosely modelled on Microsoft Excel® in terms of the initial functions supported and the syntax used.

The spreadsheet expression evaluator and the documentation below was written by Tim Hudson (tjh@cryptsoft.com).

---

5.8.2. Cells and Cellids

Each cell in a table has an cellid which is made up of a single uppercase letter indicating the column index and a number indicating the row index (counting from 1 and excluding the heading rows). The upper left cell is hence A1.

An example grid indicating cellids:

Title1	Title2	Title3	Title4	Title5
A1	B1	C1	D1	E1
A2	B2	C2	D2	E2
A3	B3	C3	D3	E3
...	...	...	...	...
A100	B100	C100	D100	E100

A range of cellids is specified using the syntax cellid1:cellid2. For example: A1:C1 is exactly the same as A1,B1,C1

---

5.8.3. Spreadsheet Expressions

An expression consists of a combination of standard Perl operators and spreadsheet functions and cellids or cellid ranges.

Standard Perl operators include:

• + - * /

---

5.8.4. Spreadsheet Functions

Spreadsheet functions use the syntax FUNCTION(ARG1,ARG2,...ARGN).

The following functions are supported:

• AVERAGE - the average - SUM(ARGS)/COUNT(ARGS)
• SUM - the sum of the args - same as ARG1+ARG2+...+ARGN
• MIN - the minumum argument value
• MAX - the maximum argument value
• COUNT - the number of arguments
• PRODUCT - the product of the args - same as ARG1*ARGN*...*ARGN
• ROWSUM - the SUM of all the cells in the row to the left of the current cell
• ROWPROD - the PRODUCT of all the cells in the row to the left of the current cell
• COLSUM - the SUM of all the cells in the column above the current cell
• COLPROD - the PRODUCT of all the cells in the column above the current cell

---

5.8.5. An Example

A simple example is shown below:

!block table; style="grid"
Count         Price        Total
10            5            [[=A1*B1]]
15            5.23         [[=ROWPROD]]
[[=COLSUM]]   [[=B1+B2]]   [[=COLSUM]]
!endblock

This generates the result below. (Ok, summing two prices is meaningless, but it illustrates the syntax.)

Count	Price	Total
10	5	50.00
15	5.23	78.45
25.00	10.23	128.45

---

5.8.6. Accessing Spreadsheet Values in Paragraph Text

Values are available until the next table is processed so you can refer to data inside normal paragraphs after the table like this [[=A1]] (which evaluates to 10.00).

---

5.8.7. Recursive Expression Evaluation

A spreadsheet expression will recursively evaluate any expressions contained in cells that are used in an expression. In the example above, the expression in cell C3 depends on the results of the expression in cell C1 and C2.

---

5.8.8. Disabling Calculations

Calculation support for a table can be disabled by adding in an attribute of nocalcs. (Without this, the pointers required to table data that are needed when doing spreadsheet calculations occur for each table cell.)