Help/Spreadsheet

Note: You are viewing an old revision of this page. View the current version.

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

PhpWikiDocumentation