Help/Spreadsheet
Phpwiki has extended the Wikicreole syntax of tables so that some simple spreadsheet-like calculations can be performed in tables.
Syntax
Five functions are available: SUM, AVERAGE, MAX, MIN and COUNT.
They operate on the current row (R) or column (C).
The function is placed in the cell surrounded by @@. Other wiki text can be placed in the cell, but there can be only one formula per cell.
Formula |
Meaning |
---|---|
@@=SUM(R)@@ |
Sum of cells in current row |
@@=SUM(C)@@ |
Sum of cells in current column |
@@=AVERAGE(R)@@ |
Average of cells in current row |
@@=AVERAGE(C)@@ |
Average of cells in current column |
@@=MAX(R)@@ |
Maximum value of cells in current row |
@@=MAX(C)@@ |
Maximum value of cells in current column |
@@=MIN(R)@@ |
Minimum value of cells in current row |
@@=MIN(C)@@ |
Minimum value of cells in current column |
@@=COUNT(R)@@ |
Number of cells in current row (numeric or not, excluding headers and current cell) |
@@=COUNT(C)@@ |
Number of cells in current column (numeric or not, excluding headers and current cell) |
Examples
Sum
|=Region |=Telecom Sales |=Power Sales |=Other |=TOTAL | |=Europe | 320 | 80 | 120 |@@=SUM(R)@@ | |=Asia | 580 | 723 | 564 |@@=SUM(R)@@ | |=North America | 235 | 60 | 109 |@@=SUM(R)@@ | |=South America | 120 | 35 | 82 |@@=SUM(R)@@ | |=Antarctica | 0 | 0 | 12 |@@=SUM(R)@@ | |=TOTAL | @@=SUM(C)@@ | @@=SUM(C)@@ | @@=SUM(C)@@ |Grand total: @@=SUM(R)@@ |
Region | Telecom Sales | Power Sales | Other | TOTAL |
---|---|---|---|---|
Europe | 320 | 80 | 120 | 520 |
Asia | 580 | 723 | 564 | 1867 |
North America | 235 | 60 | 109 | 404 |
South America | 120 | 35 | 82 | 237 |
Antarctica | 0 | 0 | 12 | 12 |
TOTAL | 1255 | 898 | 887 | Grand total: 3040 |
Total of both colums and rows is calculated automatically by wiki.
Count and Sum
|=Bug |=Priority |=Subject |=Status |=Days to fix | 1231 | Low | File Open ... | Open | 3 | 1232 | High | Memory Window ... | Fixed | 2 | 1233 | Medium | Usability issue ... | Assigned | 5 | 1234 | High | No arrange ... | Fixed | 1 | Number of bugs: @@=COUNT(C)@@ | | | | Total: @@=SUM(C)@@ days
Bug | Priority | Subject | Status | Days to fix |
---|---|---|---|---|
1231 | Low | File Open ... | Open | 3 |
1232 | High | Memory Window ... | Fixed | 2 |
1233 | Medium | Usability issue ... | Assigned | 5 |
1234 | High | No arrange ... | Fixed | 1 |
Number of bugs: 4 | Total: 11 days |
Min, Max and Average
|=Location |=Morning |=Noon |=Evening |=Average temperature | | Paris | 7 | 13 | 10 | @@=AVERAGE(R)@@ | | London | 3 | 10 | 8 | @@=AVERAGE(R)@@ | | Berlin | 9 | 15 | 12 | @@=AVERAGE(R)@@ | | Tokyo | 12 | 20 | 16 | @@=AVERAGE(R)@@ | | Maximum temperature | @@=MAX(C)@@ | @@=MAX(C)@@ | @@=MAX(C)@@ | | | Minimum temperature | @@=MIN(C)@@ | @@=MIN(C)@@ | @@=MIN(C)@@ | |
Location | Morning | Noon | Evening | Average temperature |
---|---|---|---|---|
Paris | 7 | 13 | 10 | 10 |
London | 3 | 10 | 8 | 7 |
Berlin | 9 | 15 | 12 | 12 |
Tokyo | 12 | 20 | 16 | 16 |
Maximum temperature | 12 | 20 | 16 | |
Minimum temperature | 3 | 10 | 8 |
Limitations
The formulas are evaluated line per line, from left to right.
So this will work:
| 10 | -13 | @@=SUM(R)@@ | | 15 | 17 | @@=SUM(R)@@ | | @@=SUM(C)@@ | @@=SUM(C)@@ | @@=SUM(R)@@ |
10 | -13 | -3 |
15 | 17 | 32 |
25 | 4 | 29 |
And this will not:
| @@=SUM(R)@@ | 10 | -13 | | @@=SUM(R)@@ | 15 | 17 | | @@=SUM(R)@@ | @@=SUM(C)@@ | @@=SUM(C)@@ |
-3 | 10 | -13 |
32 | 15 | 17 |
0 | 25 | 4 |
Author
- Marc-Etienne Vargenau, Alcatel-Lucent