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
