pgsrc/Help%2FSpreadsheet

   1 Date: Fri, 10 Sep 2010 13:46:13 +0000
   2 Mime-Version: 1.0 (Produced by PhpWiki 1.4.0)
   3 Content-Type: application/x-phpwiki;
   4   pagename=Help%2FSpreadsheet;
   5   flags=PAGE_LOCKED%2CEXTERNAL_PAGE;
   6   markup=2;
   7   charset=UTF-8
   8 Content-Transfer-Encoding: binary
   9
  10 Phpwiki has extented the [[Help:Wikicreole|Wikicreole]] syntax of tables so that some simple
  11 spreadsheet-like calculations can be performed in tables.
  12
  13 == Syntax ==
  14
  15 Five functions are available: SUM, AVERAGE, MAX, MIN and COUNT.
  16
  17 They operate on the current row (R) or column (C).
  18
  19 The function is placed in the cell surrounded by ##@@##.
  20 Other wiki text can be placed in the cell, but there can be only one formula per cell.
  21
  22 {| class="bordered"
  23 |-
  24 ! Formula !! Meaning
  25 |-
  26 | @@=SUM(R)@@ || Sum of cells in current row
  27 |-
  28 | @@=SUM(C)@@ || Sum of cells in current column
  29 |-
  30 | @@=AVERAGE(R)@@ || Average of cells in current row
  31 |-
  32 | @@=AVERAGE(C)@@ || Average of cells in current column
  33 |-
  34 | @@=MAX(R)@@ || Maximum value of cells in current row
  35 |-
  36 | @@=MAX(C)@@ || Maximum value of cells in current column
  37 |-
  38 | @@=MIN(R)@@ || Minimum value of cells in current row
  39 |-
  40 | @@=MIN(C)@@ || Minimum value of cells in current column
  41 |-
  42 | @@=COUNT(R)@@
  43 | Number of cells in current row (numeric or not, excluding headers and current cell)
  44 |-
  45 | @@=COUNT(C)@@
  46 | Number of cells in current column (numeric or not, excluding headers and current cell)
  47 |}
  48
  49 == Examples ==
  50
  51 === Sum ===
  52
  53 {{{
  54 |=Region          |=Telecom Sales  |=Power Sales |=Other       |=TOTAL      |
  55 |=Europe          |            320 |          80 |         120 |@@=SUM(R)@@ |
  56 |=Asia            |            580 |         723 |         564 |@@=SUM(R)@@ |
  57 |=North America   |            235 |          60 |         109 |@@=SUM(R)@@ |
  58 |=South America   |            120 |          35 |          82 |@@=SUM(R)@@ |
  59 |=Antarctica      |              0 |           0 |          12 |@@=SUM(R)@@ |
  60 |=TOTAL           |    @@=SUM(C)@@ | @@=SUM(C)@@ | @@=SUM(C)@@ |Grand total: @@=SUM(R)@@ |
  61 }}}
  62
  63 |=Region          |=Telecom Sales  |=Power Sales |=Other       |=TOTAL      |
  64 |=Europe          |            320 |          80 |         120 |@@=SUM(R)@@ |
  65 |=Asia            |            580 |         723 |         564 |@@=SUM(R)@@ |
  66 |=North America   |            235 |          60 |         109 |@@=SUM(R)@@ |
  67 |=South America   |            120 |          35 |          82 |@@=SUM(R)@@ |
  68 |=Antarctica      |              0 |           0 |          12 |@@=SUM(R)@@ |
  69 |=TOTAL           |    @@=SUM(C)@@ | @@=SUM(C)@@ | @@=SUM(C)@@ |Grand total: @@=SUM(R)@@ |
  70
  71 Total of both colums and rows is calculated automatically by wiki.
  72
  73 === Count and Sum ===
  74
  75 {{{
  76 |=Bug                |=Priority   |=Subject             |=Status     |=Days to fix
  77 | 1231               | Low        | File Open ...       | Open       | 3
  78 | 1232               | High       | Memory Window ...   | Fixed      | 2
  79 | 1233               | Medium     | Usability issue ... | Assigned   | 5
  80 | 1234               | High       | No arrange ...      | Fixed      | 1
  81 | Number of bugs: @@=COUNT(C)@@ | |                     |            | Total: @@=SUM(C)@@ days
  82 }}}
  83
  84 |=Bug                |=Priority   |=Subject             |=Status     |=Days to fix
  85 | 1231               | Low        | File Open ...       | Open       | 3
  86 | 1232               | High       | Memory Window ...   | Fixed      | 2
  87 | 1233               | Medium     | Usability issue ... | Assigned   | 5
  88 | 1234               | High       | No arrange ...      | Fixed      | 1
  89 | Number of bugs: @@=COUNT(C)@@ | |                     |            | Total: @@=SUM(C)@@ days
  90
  91 === Min, Max and Average ===
  92
  93 {{{
  94 |=Location            |=Morning     |=Noon        |=Evening     |=Average temperature |
  95 | Paris               | 7           | 13          | 10          | @@=AVERAGE(R)@@     |
  96 | London              | 3           | 10          | 8           | @@=AVERAGE(R)@@     |
  97 | Berlin              | 9           | 15          | 12          | @@=AVERAGE(R)@@     |
  98 | Tokyo               | 12          | 20          | 16          | @@=AVERAGE(R)@@     |
  99 | Maximum temperature | @@=MAX(C)@@ | @@=MAX(C)@@ | @@=MAX(C)@@ |                     |
 100 | Minimum temperature | @@=MIN(C)@@ | @@=MIN(C)@@ | @@=MIN(C)@@ |                     |
 101 }}}
 102
 103 |=Location            |=Morning     |=Noon        |=Evening     |=Average temperature |
 104 | Paris               | 7           | 13          | 10          | @@=AVERAGE(R)@@     |
 105 | London              | 3           | 10          | 8           | @@=AVERAGE(R)@@     |
 106 | Berlin              | 9           | 15          | 12          | @@=AVERAGE(R)@@     |
 107 | Tokyo               | 12          | 20          | 16          | @@=AVERAGE(R)@@     |
 108 | Maximum temperature | @@=MAX(C)@@ | @@=MAX(C)@@ | @@=MAX(C)@@ |                     |
 109 | Minimum temperature | @@=MIN(C)@@ | @@=MIN(C)@@ | @@=MIN(C)@@ |                     |
 110
 111 == Limitations ==
 112
 113 The formulas are evaluated line per line, from left to right.
 114
 115 So this will work:
 116
 117 {{{
 118 |          10 |         -13 | @@=SUM(R)@@ |
 119 |          15 |          17 | @@=SUM(R)@@ |
 120 | @@=SUM(C)@@ | @@=SUM(C)@@ | @@=SUM(R)@@ |
 121 }}}
 122
 123 |          10 |         -13 | @@=SUM(R)@@ |
 124 |          15 |          17 | @@=SUM(R)@@ |
 125 | @@=SUM(C)@@ | @@=SUM(C)@@ | @@=SUM(R)@@ |
 126
 127 And this will not:
 128
 129 {{{
 130 | @@=SUM(R)@@ |          10 |         -13 |
 131 | @@=SUM(R)@@ |          15 |          17 |
 132 | @@=SUM(R)@@ | @@=SUM(C)@@ | @@=SUM(C)@@ |
 133 }}}
 134
 135 | @@=SUM(R)@@ |          10 |         -13 |
 136 | @@=SUM(R)@@ |          15 |          17 |
 137 | @@=SUM(R)@@ | @@=SUM(C)@@ | @@=SUM(C)@@ |
 138
 139 == Author ==
 140
 141 * Marc-Etienne Vargenau, Alcatel-Lucent
 142
 143 <noinclude>
 144 ----
 145 [[PhpWikiDocumentation]]
 146 </noinclude>