1 rizwank 1.1 %META:TOPICINFO{author="PeterThoeny" date="1098612070" format="1.0" version="1.13"}%
2 ---+!! TWiki Spreadsheet Plugin
3
4 This Plugin adds speadsheet capabilities to %WIKITOOLNAME% topics. Formulae like ==%<nop>CALC{"$INT(7/3)"}%== are evaluated at page view time. They can be placed in table cells and outside of tables. In other words, this Plugin provides general formula evaluation capability, not just classic spreadsheet functions.
5
6 <table><tr><td valign="top">
7 __Example:__
8
9 | *Region:* | *Sales:* |
10 | Northeast | 320 |
11 | Northwest | 580 |
12 | South | 240 |
13 | Europe | 610 |
14 | Asia | 220 |
15 | Total: | %CALC{"$SUM( $ABOVE() )"}% |
16
17 </td><td> </td><td valign="top">
18 __Interactive example:__
19
20 <form name="interactive" action="%SCRIPTURL%/view%SCRIPTSUFFIX%/%WEB%/%TOPIC%" method="get">
21 <noautolink>
22 rizwank 1.1 Formula: <code>%<nop>CALC{"</code><input type="text" name="formula" size="40" value="%URLPARAM{ "formula" default="$PROPERSPACE(%WIKINAME%)" encode="entity" }%" /><code>"}%</code> <input type="submit" value="Go" /><br />
23 Result: %CALC{%URLPARAM{ "formula" default="$PROPERSPACE(%WIKINAME%)" }%}%
24
25 </noautolink>
26 </form>
27 </td></tr><tr><td colspan="3">
28 The formula next to "Total" is ==%<nop>CALC{"$SUM( $ABOVE() )"}%==. <br /> (you see the formula instead of the sum in case the Plugin is not installed or not enabled.)
29 </td></tr></table>
30
31 %TOC%
32
33 ---++ Syntax Rules
34
35 The action of this Plugin is triggered by the ==%<nop>CALC{"..."}%== variable, which gets rendered according to the built-in function(s) found between the quotes.
36
37 * Built-in function are of format ==$FUNCNAME(parameter)==
38 * Functions may be nested, e.g. ==%<nop>CALC{"$SUM( R2:C$COLUMN(0)..R$ROW(-1):C$COLUMN(0) )"}%==
39 * Functions are evaluated from left to right, and from inside to outside if nested
40 * The function parameter can be text; a mathematical formula; a cell address; or a range of cell addresses
41 * Multiple parameters form a list; they are separated by a comma, followed by optional space, e.g. ==%<nop>CALC{"$SUM( 3, 5, 7 )"}%==
42 * A table cell can be addressed as ==R1:C1==. Table address matrix:
43 rizwank 1.1 | ==R1:C1== | ==R1:C2== | ==R1:C3== | ==R1:C4== |
44 | ==R2:C1== | ==R2:C2== | ==R2:C3== | ==R2:C4== |
45 * A table cell range is defined by two cell addresses separated by ==".."==, e.g. "row 1 through 20, column 3" is: ==R1:C3..R20:C3==
46 * Lists can refer to values and/or table cell ranges, e.g. ==%<nop>CALC{"$SUM( 3, 5, $T(R1:C7), R1:C11..R1:C15 )"}%==
47 * Formulae can only reference cells in the current or preceeding row of the current table; they may not reference cells below the current table row
48 * Formulae can also be placed outside of tables; they can reference cells in the preceeding table
49
50 ---++ Built-in Functions
51
52 Conventions for Syntax:
53 * Required parameters are indicated in ==( bold )==
54 * Optional parameters are indicated in ==( _bold italic_ )==
55
56 #FuncABOVE
57 ---+++ ABOVE( ) -- address range of cells above the current cell
58 * Syntax: ==$ABOVE( )==
59 * Example: ==%<nop>CALC{"$SUM($ABOVE())"}%== returns the sum of cells above the current cell
60 * Related: =[[#FuncLEFT][$LEFT()]]=, =[[#FuncRIGHT][$RIGHT()]]=
61
62 #FuncABS
63 ---+++ ABS( num ) -- absolute value of a number
64 rizwank 1.1 * Syntax: ==$ABS( num )==
65 * Example: ==%<nop>CALC{"$ABS(-12.5)"}%== returns ==12.5==
66 * Related: =[[#FuncSIGN][$SIGN()]]=, =[[#FuncEVEN][$EVEN()]]=, =[[#FuncODD][$ODD()]]=
67
68 #FuncAND
69 ---+++ AND( list ) -- logcial AND of a list
70 * Syntax: ==$AND( list )==
71 * Example: ==%<nop>CALC{"$AND(1, 0, 1)"}%== returns ==0==
72 * Related: =[[#FuncNOT][$NOT()]]=, =[[#FuncIF][$IF()]]=, =[[#FuncOR][$OR()]]=
73
74 #FuncAVERAGE
75 ---+++ AVERAGE( list ) -- average of a list or a range of cells
76 * Syntax: ==$AVERAGE( list )==
77 * Example: ==%<nop>CALC{"$AVERAGE(R2:C5..R$ROW(-1):C5)"}%== returns the average of column 5, excluding the title row
78 * Related: =[[#FuncLIST][$LIST()]]=, =[[#FuncMAX][$MAX()]]=, =[[#FuncMEDIAN][$MEDIAN()]]=, =[[#FuncMIN][$MIN()]]=
79
80 #FuncCHAR
81 ---+++ CHAR( number ) -- ASCII character represented by number
82 * Syntax: ==$CHAR( number )==
83 * Example: Example: ==%<nop>CALC{"$CHAR(97)"}%== returns ==a==
84 * Related: =[[#FuncCODE][$CODE()]]=
85 rizwank 1.1
86 #FuncCODE
87 ---+++ CODE( text ) -- ASCII numeric value of character
88 * The ASCII numeric value of the first character in text
89 * Syntax: ==$CODE( text )==
90 * Example: ==%<nop>CALC{"$CODE(abc)"}%== returns ==97==
91 * Related: =[[#FuncCHAR][$CHAR()]]=
92
93 #FuncCOLUMN
94 ---+++ COLUMN( offset ) -- current column number
95 * The current table column number with an optional offset
96 * Syntax: ==$COLUMN( _offset_ )==
97 * Example: ==%<nop>CALC{"$COLUMN()"}%== returns ==2== for the second column
98 * Related: =[[#FuncROW][$ROW()]]=, =[[#FuncT][$T()]]=
99
100 #FuncCOUNTITEMS
101 ---+++ COUNTITEMS( list ) -- count individual items in a list
102 * Syntax: ==$COUNTITEMS( list )==
103 * Example: ==%<nop>CALC{"$COUNTITEMS($ABOVE())"}%== returns ==Closed: 1, Open: 2== assuming one cell above the current cell contains ==Closed== and two cells contain ==Open==
104 * Related: =[[#FuncCOUNTSTR][$COUNTSTR()]]=, =[[#FuncLIST][$LIST()]]=
105
106 rizwank 1.1 #FuncCOUNTSTR
107 ---+++ COUNTSTR( list, str ) -- count the number of cells in a list equal to a given string
108 * Count the number of cells in a list equal to a given string (if str is specified), or counts the number of non empty cells in a list
109 * Syntax: ==$COUNTSTR( list, _str_ )==
110 * Example: ==%<nop>CALC{"$COUNTSTR($ABOVE())"}%== counts the number of non empty cells above the current cell
111 * Example: ==%<nop>CALC{"$COUNTSTR($ABOVE(), DONE)"}%== counts the number of cells equal to ==DONE==
112 * Related: =[[#FuncCOUNTITEMS][$COUNTITEMS()]]=, =[[#FuncLIST][$LIST()]]=
113
114 #FuncDEF
115 ---+++ DEF( list ) -- find first non-empty list item or cell
116 * Returns the first list item or cell reference that is not empty
117 * Syntax: ==$DEF( list )==
118 * Example: ==%<nop>CALC{"$DEF(R1:C1..R1:C3)"}%==
119 * Related: =[[#FuncCOUNTSTR][$COUNTSTR()]]=, =[[#FuncLISTIF][$LISTIF()]]=, =[[#FuncLIST][$LIST()]]=
120
121 #FuncEVAL
122 ---+++ EVAL( formula ) -- evaluate a simple formula
123 * Addition, substraction, multiplication, division and modulus of numbers are supported. Any nesting is permitted
124 * Syntax: ==$EVAL( formula )==
125 * Example: ==%<nop>CALC{"$EVAL( (5 * 3) / 2 + 1.1 )"}%== returns ==8.6==
126 * Related: =[[#FuncINT][$INT()]]=, =[[#FuncMOD][$MOD()]]=, =[[#FuncROUND][$ROUND()]]=, =[[#FuncVALUE][$VALUE()]]=
127 rizwank 1.1
128 #FuncEVEN
129 ---+++ EVEN( num ) -- test for even number
130 * Syntax: ==$EVEN( num )==
131 * Example: ==%<nop>CALC{"$EVEN(2)"}%== returns ==1==
132 * Related: =[[#FuncABS][$ABS()]]=, =[[#FuncMOD][$MOD()]]=, =[[#FuncODD][$ODD()]]=, =[[#FuncSIGN][$SIGN()]]=
133
134 #FuncEXACT
135 ---+++ EXACT( text1, text2 ) -- compare two text strings
136 * Compares two text strings and returns ==1== if they are exactly the same, or ==0== if not
137 * Syntax: ==$EXACT( text1, _text2_ )==
138 * Example: ==%<nop>CALC{"$EXACT(foo, Foo)"}%== returns ==0==
139 * Example: ==%<nop>CALC{"$EXACT(foo, $LOWER(Foo))"}%== returns ==1==
140 * Related: =[[#FuncIF][$IF()]]=, =[[#FuncTRIM][$TRIM()]]=
141
142 #FuncEXISTS
143 ---+++ EXISTS( topic ) -- check if topic exists
144 * Topic can be =TopicName= or a =Web.TopicName=
145 * Syntax: ==$EXISTS( topic )==
146 * Example: ==%<nop>CALC{"$EXISTS(<nop>%HOMETOPIC%)"}%== returns ==1==
147 * Example: ==%<nop>CALC{"$EXISTS(<nop>ThisDoesNotExist)"}%== returns ==0==
148 rizwank 1.1
149 #FuncFIND
150 ---+++ FIND( string, text, start ) -- find one string within another string
151 * Finds one text =string=, within another =text=, and returns the number of the starting position of =string=, from the first character of =text=. This search is case sensitive and is not a regular expression search; use =$SEARCH()= for regular expression searching. Starting position is 1; a 0 is returned if nothing is matched.
152 * Syntax: ==$FIND( string, text, _start_ )==
153 * Example: ==%<nop>CALC{"$FIND(f, fluffy)"}%== returns ==1==
154 * Example: ==%<nop>CALC{"$FIND(f, fluffy, 2)"}%== returns ==4==
155 * Example: ==%<nop>CALC{"$FIND(@, fluffy, 1)"}%== returns ==0==
156 * Related: =[[#FuncREPLACE][$REPLACE()]]=, =[[#FuncSEARCH][$SEARCH()]]=
157
158 #FuncFORMAT
159 ---+++ FORMAT( type, prec, number ) -- format a number to a certain type and precision
160 * Type can be COMMA for comma format, DOLLAR for Dollar format, KB for Kilo Byte format, MB for Mega Byte format, KBMB for Kilo/Mega/Giga/Tera Byte auto-adjust format, NUMBER for number, and PERCENT for percent format
161 * Syntax: ==$FORMAT( type, prec, number )==
162 * Example: ==%<nop>CALC{"$FORMAT(COMMA, 2, 12345.6789)"}%== returns ==12,345.68==
163 * Example: ==%<nop>CALC{"$FORMAT(DOLLAR, 2, 12345.67)"}%== returns ==$12,345.68==
164 * Example: ==%<nop>CALC{"$FORMAT(KB, 2, 1234567)"}%== returns ==1205.63 KB==
165 * Example: ==%<nop>CALC{"$FORMAT(MB, 2, 1234567)"}%== returns ==1.18 MB==
166 * Example: ==%<nop>CALC{"$FORMAT(KBMB, 2, 1234567)"}%== returns ==1.18 MB==
167 * Example: ==%<nop>CALC{"$FORMAT(KBMB, 2, 1234567890)"}%== returns ==1.15 GB==
168 * Example: ==%<nop>CALC{"$FORMAT(NUMBER, 1, 12345.67)"}%== returns ==12345.7==
169 rizwank 1.1 * Example: ==%<nop>CALC{"$FORMAT(PERCENT, 1, 0.1234567)"}%== returns ==12.3%==
170 * Related: =[[#FuncROUND][$ROUND()]]=
171
172 #FuncFORMATTIME
173 ---+++ FORMATTIME( serial, text ) -- convert a serialized date into a date string
174 * The following variables in =text= are expanded: =$second= (seconds, 00..59); =$minute= (minutes, 00..59); =$hour= (hours, 00..23); =$day= (day of month, 01..31); =$month= (month, 01..12); =$mon= (month in text format, Jan..Dec); =$year= (4 digit year, 1999); =$ye= (2 digit year, 99), =$wd= (day number of the week, 1 for Sunday, 2 for Monday, etc), =$wday= (day of the week, Sun..Sat), =$weekday= (day of the week, Sunday..Saturday), =$yearday= (day of the year, 1..365, or 1..366 in leap years). Date is assumed to be server time; add =GMT= to indicate Greenwich time zone.
175 * Syntax: ==$FORMATTIME( serial, text )==
176 * Example: ==%<nop>CALC{"$FORMATTIME(0, $year/$month/$day GMT)"}%== returns ==1970/01/01 GMT==
177 * Related: =[[#FuncFORMATGMTIME][$FORMATGMTIME()]]=, =[[#FuncTIME][$TIME()]]=, =[[#FuncTIMEADD][$TIMEADD()]]=, =[[#FuncTIMEDIFF][$TIMEDIFF()]]=, =[[#FuncTODAY][$TODAY()]]=
178
179 #FuncFORMATGMTIME
180 ---+++ FORMATGMTIME( serial, text ) -- convert a serialized date into a GMT date string
181 * The date string represents the time in Greenwich time zone. Same variable expansion as in =$FORMATTIME()=.
182 * Syntax: ==$FORMATGMTIME( serial, text )==
183 * Example: ==%<nop>CALC{"$FORMATGMTIME(1041379200, $day $mon $year)"}%== returns ==01 Jan 2003==
184 * Related: =[[#FuncFORMATTIME][$FORMATTIME()]]=, =[[#FuncTIME][$TIME()]]=, =[[#FuncTIMEADD][$TIMEADD()]]=, =[[#FuncTIMEDIFF][$TIMEDIFF()]]=, =[[#FuncTODAY][$TODAY()]]=
185
186 #FuncGET
187 ---+++ GET( name ) -- get the value of a previously set variable
188 * Specify the variable name (alphanumeric characters and underscores). An empty string is returned if the variable does not exist. Use =$SET()= to set a variable first. Unlike table ranges, variables live for the time of the page view and persist across tables, i.e. you can use it to summarize results across several tables.
189 * Syntax: ==$GET( name )==
190 rizwank 1.1 * Example: ==%<nop>CALC{"$GET(my_total)"}%== returns the value of the =my_total= variable
191 * Related: =[[#FuncSET][$SET()]]=, =[[#FuncSETM][$SETM()]]=
192
193 #FuncIF
194 ---+++ IF( condition, value if true, value if 0 ) -- return a value based on a condition
195 * The condition can be a number (where ==0== means condition not met), or two numbers with a comparison operator ==<== (less than), ==<=<nop>== (less than or equal), ==<nop>==<nop>== (equal), ==<nop>!=<nop>== (not equal), ==>=<nop>== (greater than or equal), ==>== (greater than).
196 * Syntax: ==$IF( condition, value if true, value if 0 )==
197 * Example: ==%<nop>CALC{"$IF($T(R1:C5) > 1000, Over Budget, OK)"}%== returns ==Over Budget== if value in R1:C5 is over 1000, ==OK== if not
198 * Example: ==%<nop>CALC{"$IF($EXACT($T(R1:C2),), empty, $T(R1:C2))"}%== returns the content of R1:C2 or ==empty== if empty
199 * Example: ==%<nop>CALC{"$SET(val, $IF($T(R1:C2) == 0, zero, $T(R1:C2)))"}%== sets a variable conditionally
200 * Related: =[[#FuncAND][$AND()]]=, =[[#FuncEXACT][$EXACT()]]=, =[[#FuncLISTIF][$LISTIF()]]=, =[[#FuncNOT][$NOT()]]=, =[[#FuncOR][$OR()]]=
201
202 #FuncINT
203 ---+++ INT( formula ) -- evaluate formula and round down to nearest integer
204 * Addition, substraction, multiplication, division and modulus of numbers are supported. Any nesting is permitted
205 * Syntax: ==$INT( formula )==
206 * Example: ==%<nop>CALC{"$INT(10 / 4)"}%== returns ==2==
207 * Related: =[[#FuncEVAL][$EVAL()]]=, =[[#FuncROUND][$ROUND()]]=, =[[#FuncVALUE][$VALUE()]]=
208
209 #FuncLEFT
210 ---+++ LEFT( ) -- address range of cells to the left of the current cell
211 rizwank 1.1 * Syntax: ==$LEFT( )==
212 * Example: ==%<nop>CALC{"$SUM($LEFT())"}%== returns the sum of cells to the left of the current cell
213 * Related: =[[#FuncABOVE][$ABOVE()]]=, =[[#FuncRIGHT][$RIGHT()]]=
214
215 #FuncLENGTH
216 ---+++ LENGTH( text ) -- length of text in bytes
217 * Syntax: ==$LENGTH( text )==
218 * Example: ==%<nop>CALC{"$LENGTH(abcd)"}%== returns ==4==
219 * Related: =[[#FuncLISTSIZE][$LISTSIZE()]]=
220
221 #FuncLIST
222 ---+++ LIST( range ) -- convert content of a cell range into a list
223 * Convert the content of a range of cells into a flat list, delimited by comma. Cells containing commas are merged into the list
224 * Syntax: ==$LIST( range )==
225 * Example: ==%<nop>CALC{"$LIST($LEFT())"}%== returns ==Apples, Lemons, Oranges, Kiwis== assuming the cells to the left contain ==| Apples | Lemons, Oranges | Kiwis |==
226 * Related: =[[#FuncAVERAGE][$AVERAGE()]]=, =[[#FuncCOUNTITEMS][$COUNTITEMS()]]=, =[[#FuncCOUNTSTR][$COUNTSTR()]]=, =[[#FuncDEF][$DEF()]]=, =[[#FuncLISTIF][$LISTIF()]]=, =[[#FuncLISTITEM][$LISTITEM()]]=, =[[#FuncLISTJOIN][$LISTJOIN()]]=, =[[#FuncLISTMAP][$LISTMAP()]]=, =[[#FuncLISTREVERSE][$LISTREVERSE()]]=, =[[#FuncLISTSIZE][$LISTSIZE()]]=, =[[#FuncLISTSORT][$LISTSORT()]]=, =[[#FuncLISTUNIQUE][$LISTUNIQUE()]]=, =[[#FuncMAX][$MAX()]]=, =[[#FuncMEDIAN][$MEDIAN()]]=, =[[#FuncMIN][$MIN()]]=, =[[#FuncPRODUCT][$PRODUCT()]]=, =[[#FuncSUM][$SUM()]]=, =[[#FuncSUMDAYS][$SUMDAYS()]]=, =[[#FuncSUMPRODUCT][$SUMPRODUCT()]]=
227
228 #FuncLISTIF
229 ---+++ LISTIF( condition, list ) -- remove elements from a list that do not meet a condition
230 * In addition to the condition described in =[[#FuncIF][$IF()]]=, you can use ==$item== to indicate the current element, and ==$index== for the list index, starting at 1
231 * Syntax: ==$LISTIF( condition, list )==
232 rizwank 1.1 * Example: ==%<nop>CALC{"$LISTIF($item > 12, 14, 7, 25)"}%== returns ==14, 25==
233 * Example: ==%<nop>CALC{"$LISTIF($NOT($EXACT($item,)), A, B, , E)"}%== returns non-empty elements ==A, B, E==
234 * Example: ==%<nop>CALC{"$LISTIF($index > 2, A, B, C, D)"}%== returns ==C, D==
235 * Related: =[[#FuncIF][$IF()]]=, =[[#FuncLIST][$LIST()]]=, =[[#FuncLISTITEM][$LISTITEM()]]=, =[[#FuncLISTMAP][$LISTMAP()]]=, =[[#FuncLISTREVERSE][$LISTREVERSE()]]=, =[[#FuncLISTSIZE][$LISTSIZE()]]=, =[[#FuncLISTSORT][$LISTSORT()]]=, =[[#FuncLISTUNIQUE][$LISTUNIQUE()]]=, =[[#FuncSUM][$SUM()]]=
236
237 #FuncLISTITEM
238 ---+++ LISTITEM( index, list ) -- get one element of a list
239 * Index is 1 to size of list; use a negative number to count from the end of the list
240 * Syntax: ==$LISTITEM( index, list )==
241 * Example: ==%<nop>CALC{"$LISTITEM(2, Apple, Orange, Apple, Kiwi)"}%== returns ==Orange==
242 * Example: ==%<nop>CALC{"$LISTITEM(-1, Apple, Orange, Apple, Kiwi)"}%== returns ==Kiwi==
243 * Related: =[[#FuncCOUNTITEMS][$COUNTITEMS()]]=, =[[#FuncCOUNTSTR][$COUNTSTR()]]=, =[[#FuncLIST][$LIST()]]=, =[[#FuncLISTIF][$LISTIF()]]=, =[[#FuncLISTMAP][$LISTMAP()]]=, =[[#FuncLISTREVERSE][$LISTREVERSE()]]=, =[[#FuncLISTSIZE][$LISTSIZE()]]=, =[[#FuncLISTSORT][$LISTSORT()]]=, =[[#FuncLISTUNIQUE][$LISTUNIQUE()]]=, =[[#FuncSUM][$SUM()]]=
244
245 #FuncLISTJOIN
246 ---+++ LISTJOIN( separator, list ) -- convert a list into a string
247 * By default, list items are separated by a comma and a space. Use this function to indicate a specific =separator= string, which may include =$comma= for comma, =$n= for newline, and =$sp= for space.
248 * Syntax: ==$LISTJOIN( separator, list )==
249 * Example: ==%<nop>CALC{"$LISTJOIN($n, Apple, Orange, Apple, Kiwi)"}%== returns the four items separated by new lines
250 * Related: =[[#FuncLIST][$LIST()]]=, =[[#FuncLISTSIZE][$LISTSIZE()]]=
251
252 #FuncLISTMAP
253 rizwank 1.1 ---+++ LISTMAP( formula, list ) -- evaluate and update each element of a list
254 * In the formula you can use ==$item== to indicate the element; ==$index== to show the index of the list, starting at 1
255 * Syntax: ==$LISTMAP( formula, list )==
256 * Example: ==%<nop>CALC{"$LISTMAP($index: $EVAL(2 * $item), 3, 5, 7, 11)"}%== returns ==1: 6, 2: 10, 3: 14, 4: 22==
257 * Related: =[[#FuncCOUNTITEMS][$COUNTITEMS()]]=, =[[#FuncCOUNTSTR][$COUNTSTR()]]=, =[[#FuncLIST][$LIST()]]=, =[[#FuncLISTIF][$LISTIF()]]=, =[[#FuncLISTITEM][$LISTITEM()]]=, =[[#FuncLISTREVERSE][$LISTREVERSE()]]=, =[[#FuncLISTSIZE][$LISTSIZE()]]=, =[[#FuncLISTSORT][$LISTSORT()]]=, =[[#FuncLISTUNIQUE][$LISTUNIQUE()]]=, =[[#FuncSUM][$SUM()]]=
258
259 #FuncLISTREVERSE
260 ---+++ LISTREVERSE( list ) -- opposite order of a list
261 * Syntax: ==$LISTREVERSE( list )==
262 * Example: ==%<nop>CALC{"$LISTREVERSE(Apple, Orange, Apple, Kiwi)"}%== returns ==Kiwi, Apple, Orange, Apple==
263 * Related: =[[#FuncCOUNTITEMS][$COUNTITEMS()]]=, =[[#FuncCOUNTSTR][$COUNTSTR()]]=, =[[#FuncLIST][$LIST()]]=, =[[#FuncLISTIF][$LISTIF()]]=, =[[#FuncLISTITEM][$LISTITEM()]]=, =[[#FuncLISTMAP][$LISTMAP()]]=, =[[#FuncLISTSIZE][$LISTSIZE()]]=, =[[#FuncLISTSORT][$LISTSORT()]]=, =[[#FuncLISTUNIQUE][$LISTUNIQUE()]]=, =[[#FuncSUM][$SUM()]]=
264
265 #FuncLISTSIZE
266 ---+++ LISTSIZE( list ) -- number of elements in a list
267 * Syntax: ==$LISTSIZE( list )==
268 * Example: ==%<nop>CALC{"$LISTSIZE(Apple, Orange, Apple, Kiwi)"}%== returns ==4==
269 * Related: =[[#FuncCOUNTITEMS][$COUNTITEMS()]]=, =[[#FuncCOUNTSTR][$COUNTSTR()]]=, =[[#FuncLIST][$LIST()]]=, =[[#FuncLISTIF][$LISTIF()]]=, =[[#FuncLISTITEM][$LISTITEM()]]=, =[[#FuncLISTJOIN][$LISTJOIN()]]=, =[[#FuncLISTMAP][$LISTMAP()]]=, =[[#FuncLISTREVERSE][$LISTREVERSE()]]=, =[[#FuncLISTSORT][$LISTSORT()]]=, =[[#FuncLISTUNIQUE][$LISTUNIQUE()]]=, =[[#FuncSUM][$SUM()]]=
270
271 #FuncLISTSORT
272 ---+++ LISTSORT( list ) -- sort a list
273 * Sorts a list in ASCII order, or numerically if all elements are numeric
274 rizwank 1.1 * Syntax: ==$LISTSORT( list )==
275 * Example: ==%<nop>CALC{"$LISTSORT(Apple, Orange, Apple, Kiwi)"}%== returns ==Apple, Apple, Kiwi, Orange==
276 * Related: =[[#FuncCOUNTITEMS][$COUNTITEMS()]]=, =[[#FuncCOUNTSTR][$COUNTSTR()]]=, =[[#FuncLIST][$LIST()]]=, =[[#FuncLISTIF][$LISTIF()]]=, =[[#FuncLISTITEM][$LISTITEM()]]=, =[[#FuncLISTMAP][$LISTMAP()]]=, =[[#FuncLISTREVERSE][$LISTREVERSE()]]=, =[[#FuncLISTSIZE][$LISTSIZE()]]=, =[[#FuncLISTUNIQUE][$LISTUNIQUE()]]=, =[[#FuncSUM][$SUM()]]=
277
278 #FuncLISTUNIQUE
279 ---+++ LISTUNIQUE( list ) -- remove all duplicates from a list
280 * Syntax: ==$LISTUNIQUE( list )==
281 * Example: ==%<nop>CALC{"$LISTUNIQUE(Apple, Orange, Apple, Kiwi)"}%== returns ==Apple, Orange, Kiwi==
282 * Related: =[[#FuncCOUNTITEMS][$COUNTITEMS()]]=, =[[#FuncCOUNTSTR][$COUNTSTR()]]=, =[[#FuncLIST][$LIST()]]=, =[[#FuncLISTIF][$LISTIF()]]=, =[[#FuncLISTITEM][$LISTITEM()]]=, =[[#FuncLISTMAP][$LISTMAP()]]=, =[[#FuncLISTREVERSE][$LISTREVERSE()]]=, =[[#FuncLISTSIZE][$LISTSIZE()]]=, =[[#FuncLISTSORT][$LISTSORT()]]=, =[[#FuncSUM][$SUM()]]=
283
284 #FuncLOWER
285 ---+++ LOWER( text ) -- lower case string of a text
286 * Syntax: ==$LOWER(text)==
287 * Example: ==%<nop>CALC{"$LOWER( $T(R1:C5) )"}%== returns the lower case string of the text in cell ==R1:C5==
288 * Related: =[[#FuncPROPER][$PROPER()]]=, =[[#FuncPROPERSPACE][$PROPERSPACE()]]=, =[[#FuncTRIM][$TRIM()]]=, =[[#FuncUPPER][$UPPER()]]=
289
290 #FuncMAX
291 ---+++ MAX( list ) - biggest value of a list or range of cells
292 * Syntax: ==$MAX( list )==
293 * Example: To find the biggest number to the left of the current cell, write: ==%<nop>CALC{"$MAX($LEFT())"}%==
294 * Related: =[[#FuncLIST][$LIST()]]=, =[[#FuncMEDIAN][$MEDIAN()]]=, =[[#FuncMIN][$MIN()]]=, =[[#FuncPERCENTILE][$PERCENTILE()]]=
295 rizwank 1.1
296 #FuncMEDIAN
297 ---+++ MEDIAN( list ) -- median of a list or range of cells
298 * Syntax: ==$MEDIAN( list )==
299 * Example: ==%<nop>CALC{"$MEDIAN(3, 9, 4, 5)"}%== returns ==4.5==
300 * Related: =[[#FuncLIST][$LIST()]]=, =[[#FuncMAX][$MAX()]]=, =[[#FuncMIN][$MIN()]]=, =[[#FuncPERCENTILE][$PERCENTILE()]]=
301
302 #FuncMIN
303 ---+++ MIN( list ) -- smallest value of a list or range of cells
304 * Syntax: ==$MIN( list )==
305 * Example: ==%<nop>CALC{"$MIN(15, 3, 28)"}%== returns ==3==
306 * Related: =[[#FuncLIST][$LIST()]]=, =[[#FuncMAX][$MAX()]]=, =[[#FuncMEDIAN][$MEDIAN()]]=, =[[#FuncPERCENTILE][$PERCENTILE()]]=
307
308 #FuncMOD
309 ---+++ MOD( num, divisor ) -- reminder after dividing ==num== by ==divisor==
310 * Syntax: ==$MOD( num, divisor )==
311 * Example: ==%<nop>CALC{"$MOD(7, 3)"}%== returns ==1==
312 * Related: =[[#FuncEVAL][$EVAL()]]=
313
314 #FuncNOP
315 ---+++ NOP( text ) -- no-operation
316 rizwank 1.1 * Useful to change the order of Plugin execution. For example, it allows preprossing to be done before =%<nop>SEARCH{}%= is evaluated. The percent character '%' can be escaped with =$per=
317 * Syntax: ==$NOP( text )==
318
319 #FuncNOT
320 ---+++ NOT( num ) -- reverse logic of a number
321 * Returns 0 if ==num== is not zero, 1 if zero
322 * Syntax: ==$NOT( num )==
323 * Example: ==%<nop>CALC{"$NOT(0)"}%== returns ==1==
324 * Related: =[[#FuncAND][$AND()]]=, =[[#FuncIF][$IF()]]=, =[[#FuncOR][$OR()]]=
325
326 #FuncODD
327 ---+++ ODD( num ) -- test for odd number
328 * Syntax: ==$ODD( num )==
329 * Example: ==%<nop>CALC{"$ODD(2)"}%== returns ==0==
330 * Related: =[[#FuncABS][$ABS()]]=, =[[#FuncEVEN][$EVEN()]]=, =[[#FuncMOD][$MOD()]]=, =[[#FuncSIGN][$SIGN()]]=
331
332 #FuncOR
333 ---+++ OR( list ) -- logcial OR of a list
334 * Syntax: ==$OR( list )==
335 * Example: ==%<nop>CALC{"$OR(1, 0, 1)"}%== returns ==1==
336 * Related: =[[#FuncAND][$AND()]]=, =[[#FuncIF][$IF()]]=, =[[#FuncNOT][$NOT()]]=
337 rizwank 1.1
338 #FuncPERCENTILE
339 ---+++ PERCENTILE( num, list ) -- percentile of a list or range of cells
340 * Calculates the num-th percentile, useful to establish a threshold of acceptance. num is the percentile value, range 0..100
341 * Syntax: ==$PERCENTILE( num, list )==
342 * Example: ==%<nop>CALC{"$PERCENTILE(75, 400, 200, 500, 100, 300)"}%== returns ==450==
343 * Related: =[[#FuncLIST][$LIST()]]=, =[[#FuncMAX][$MAX()]]=, =[[#FuncMEDIAN][$MEDIAN()]]=, =[[#FuncMIN][$MIN()]]=
344
345 #FuncPRODUCT
346 ---+++ PRODUCT( list ) -- product of a list or range of cells
347 * Syntax: ==$PRODUCT( list )==
348 * Example: To calculate the product of the cells to the left of the current one use ==%<nop>CALC{"$PRODUCT($LEFT())"}%==
349 * Related: =[[#FuncLIST][$LIST()]]=, =[[#FuncPRODUCT][$PRODUCT()]]=, =[[#FuncSUM][$SUM()]]=, =[[#FuncSUMPRODUCT][$SUMPRODUCT()]]=
350
351 #FuncPROPER
352 ---+++ PROPER( text ) -- properly capitalize trxt
353 * Capitalize letters that follow any character other than a letter; convert all other letters to lowercase letters
354 * Syntax: ==$PROPER( text )==
355 * Example: ==%<nop>CALC{"PROPER(a small STEP)"}%== returns ==A Small Step==
356 * Example: ==%<nop>CALC{"PROPER(f1 (formula-1))"}%== returns ==F1 (Formula-1)==
357 * Related: =[[#FuncLOWER][$LOWER()]]=, =[[#FuncPROPERSPACE][$PROPERSPACE()]]=, =[[#FuncTRIM][$TRIM()]]=, =[[#FuncUPPER][$UPPER()]]=
358 rizwank 1.1
359 #FuncPROPERSPACE
360 ---+++ PROPERSPACE( text ) -- properly space out <nop>WikiWords
361 * Properly spaces out %TWIKIWEB%.WikiWords preceeded by white space, parenthesis, or ==][==. Words listed in the DONTSPACE %TWIKIWEB%.%WIKIPREFSTOPIC% variable or DONTSPACE Plugins setting are excluded
362 * Syntax: ==$PROPERSPACE( text )==
363 * Example: Assuming DONTSPACE contains <nop>MacDonald: ==%<nop>CALC{"PROPERSPACE(Old <nop>MacDonald had a <nop>ServerFarm, <nop>EeEyeEeEyeOh"}%== returns ==Old <nop>MacDonald had a Server Farm, Ee Eye Ee Eye Oh==
364 * Related: =[[#FuncLOWER][$LOWER()]]=, =[[#FuncPROPER][$PROPER()]]=, =[[#FuncTRIM][$TRIM()]]=, =[[#FuncUPPER][$UPPER()]]=
365
366 #FuncRAND
367 ---+++ RAND( max ) -- random number
368 * Random number, evenly distributed between 0 and ==max==, or 0 and 1 if max is not specified
369 * Syntax: ==$RAND( max )==
370 * Related: =[[#FuncEVAL][$EVAL()]]=
371
372 #FuncREPEAT
373 ---+++ REPEAT(text) -- repeat text a number of times
374 * Syntax: ==$REPEAT( text )==
375 * Example: ==%<nop>CALC{"$REPEAT(/\, 5)"}%== returns ==/\/\/\/\/\==
376
377 #FuncREPLACE
378 ---+++ REPLACE( text, start, num, new ) -- replace part of a text string
379 rizwank 1.1 * Replace =num= number of characters of text string =text=, starting at =start=, with new text =new=. Starting position is 1; use a negative =start= to count from the end of the text
380 * Syntax: ==$REPLACE( text, start, num, new )==
381 * Example: ==%<nop>CALC{"$REPLACE(abcdefghijk,6,5,*)"}%== returns ==abcde*k==
382 * Related: =[[#FuncFIND][$FIND()]]=, =[[#FuncSEARCH][$SEARCH()]]=, =[[#FuncSUBSTITUTE][$SUBSTITUTE()]]=, =[[#FuncTRANSLATE][$TRANSLATE()]]=
383
384 #FuncRIGHT
385 ---+++ RIGHT( ) -- address range of cells to the right of the current cell
386 * Syntax: ==$RIGHT( )==
387 * Example: ==%<nop>CALC{"$SUM($RIGHT())"}%== returns the sum of cells to the right of the current cell
388 * Related: =[[#FuncABOVE][$ABOVE()]]=, =[[#FuncLEFT][$LEFT()]]=
389
390 #FuncROUND
391 ---+++ ROUND( formula, digits ) -- round a number
392 * Evaluates a simple ==formula== and rounds the result up or down to the number of digits if ==digits== is positive; to the nearest integer if digits is missing; or to the left of the decimal point if digits is negative
393 * Syntax: ==$ROUND( formula, digits )==
394 * Example: ==%<nop>CALC{"$ROUND(3.15, 1)"}%== returns ==3.2==
395 * Example: ==%<nop>CALC{"$ROUND(3.149, 1)"}%== returns ==3.1==
396 * Example: ==%<nop>CALC{"$ROUND(-2.475, 2)"}%== returns ==-2.48==
397 * Example: ==%<nop>CALC{"$ROUND(34.9, -1)"}%== returns ==30==
398 * Related: =[[#FuncINT][$INT()]]=, =[[#FuncFORMAT][$FORMAT()]]=
399
400 rizwank 1.1 #FuncROW
401 ---+++ ROW( offset ) -- current row number
402 * The current table row number with an optional offset
403 * Syntax: ==$ROW( _offset_ )==
404 * Example: To get the number of rows excluding table heading (first row) and summary row (last row you are in), write: ==%<nop>CALC{"$ROW(-2)"}%==
405 * Related: =[[#FuncCOLUMN][$COLUMN()]]=, =[[#FuncT][$T()]]=
406
407 #FuncSEARCH
408 ---+++ SEARCH( string, text, start ) -- search a string within a text
409 * Finds one text =string=, within another =text=, and returns the number of the starting position of =string=, from the first character of =text=. This search is a %TWIKIWEB%.RegularExpression search; use =$FIND()= for non-regular expression searching. Starting position is 1; a 0 is returned if nothing is matched
410 * Syntax: ==$SEARCH( string, text, _start_ )==
411 * Example: ==%<nop>CALC{"$SEARCH([uy], fluffy)"}%== returns ==3==
412 * Example: ==%<nop>CALC{"$SEARCH([uy], fluffy, 3)"}%== returns ==6==
413 * Example: ==%<nop>CALC{"$SEARCH([abc], fluffy,)"}%== returns ==0==
414 * Related: =[[#FuncFIND][$FIND()]]=, =[[#FuncREPLACE][$REPLACE()]]=
415
416 #FuncSET
417 ---+++ SET( name, value ) -- set a variable for later use
418 * Specify the variable name (alphanumeric characters and underscores) and the value. The value may contain a formula; formulas are evaluated before the variable assignment. This function returns no output. Use =$GET()= to retrieve variables. Unlike table ranges, variables live for the time of the page view and persist across tables, i.e. you can use it to summarize results across several tables
419 * Syntax: ==$SET( name, value )==
420 * Example: ==%<nop>CALC{"$SET(my_total, $SUM($ABOVE()))"}%== sets the =my_total= variable to the sum of all table cells located above the current cell and returns an empty string
421 rizwank 1.1 * Related: =[[#FuncGET][$GET()]]=, =[[#FuncSETM][SETM()]]=
422
423 #FuncSETM
424 ---+++ SETM( name, formula ) -- update an existing variable based on a formula
425 * Specify the variable name (alphanumeric characters and underscores) and the formula. The formula must start with an operator to ==+== (add), ==-== (subtract), ==*== (multiply), or ==/== (divide) something to the variable. This function returns no output. Use =$GET()= to retrieve variables
426 * Syntax: ==$SETM( name, formula )==
427 * Example: ==%<nop>CALC{"$SETM(total, + $SUM($LEFT()))"}%== adds the sum of all table cells on the left to the =total= variable, and returns an empty string
428 * Related: =[[#FuncGET][$GET()]]=, =[[#FuncSET][$SET()]]=
429
430 #FuncSIGN
431 ---+++ SIGN( num ) -- sign of a number
432 * Returns -1 if ==num== is negative, 0 if zero, or 1 if positive
433 * Syntax: ==$SIGN( num )==
434 * Example: ==%<nop>CALC{"$SIGN(-12.5)"}%== returns ==-1==
435 * Related: =[[#FuncABS][$ABS()]]=, =[[#FuncEVAL][$EVAL()]]=, =[[#FuncEVEN][$EVEN()]]=, =[[#FuncINT][$INT()]]=, =[[#FuncNOT][$NOT()]]=, =[[#FuncODD][$ODD()]]=
436
437 #FuncSUBSTITUTE
438 ---+++ SUBSTITUTE( text, old, new, instance, option ) -- substitute text
439 * Substitutes =new= text for =old= text in a =text= string. =instance= specifies which occurance of =old= you want to replace. If you specify =instance=, only that instance is replaced. Otherwise, every occurance is changed to the new text. A literal search is performed by default; a %TWIKIWEB%.RegularExpression search if the =option= is set to ==r==
440 * Syntax: ==$SUBSTITUTE( text, old, _new_, _instance_, _option_ )==
441 * Example: ==%<nop>CALC{"$SUBSTITUTE(Good morning, morning, day)"}%== returns ==Good day==
442 rizwank 1.1 * Example: ==%<nop>CALC{"$SUBSTITUTE(Q2-2002,2,3)"}%== returns ==Q3-3003==
443 * Example: ==%<nop>CALC{"$SUBSTITUTE(Q2-2002,2,3,3)"}%== returns ==Q2-2003==
444 * Example: ==%<nop>CALC{"$SUBSTITUTE(abc123def,[0-9],9,,r)"}%== returns ==abc999def==
445 * Related: =[[#FuncREPLACE][$REPLACE()]]=, =[[#FuncTRANSLATE][$TRANSLATE()]]=
446
447 #FuncSUM
448 ---+++ SUM( list ) -- sum of a list or range of cells
449 * Syntax: ==$SUM( list )==
450 * Example: To sum up column 5 excluding the title row, write ==%<nop>CALC{"$SUM(R2:C5..R$ROW(-1):C5)"}%== in the last row; or simply ==%<nop>CALC{"$SUM($ABOVE())"}%==
451 * Related: =[[#FuncLIST][$LIST()]]=, =[[#FuncPRODUCT][$PRODUCT()]]=, =[[#FuncSUMPRODUCT][$SUMPRODUCT()]]=, =[[#FuncWORKINGDAYS][$WORKINGDAYS()]]=
452
453 #FuncSUMDAYS
454 ---+++ SUMDAYS( list ) -- sum the days in a list or range of cells
455 * The total number of days in a list or range of cells containing numbers of hours, days or weeks. The default unit is days; units are indicated by a ==h==, ==hours==, ==d==, ==days==, ==w==, ==weeks== suffix. One week is assumed to have 5 working days, one day 8 hours
456 * Syntax: ==$SUMDAYS( list )==
457 * Example: ==%<nop>CALC{"$SUMDAYS(2w, 1, 2d, 4h)"}%== returns ==13.5==, the evaluation of =(2*5 + 1 + 2 + 4/8)=
458 * Related: =[[#FuncSUM][$SUM()]]=, =[[#FuncTIME][$TIME()]]=, =[[#FuncFORMATTIME][$FORMATTIME()]]=
459
460 #FuncSUMPRODUCT
461 ---+++ SUMPRODUCT( list, list ) -- scalar product on ranges of cells
462 * Syntax: ==$SUMPRODUCT( list, list, _list..._ )==
463 rizwank 1.1 * Example: ==%<nop>CALC{"$SUM(R2:C1..R4:C1, R2:C5..R4:C5)"}%== evaluates and returns the result of ==($T(R2:C1) * $T(R2:C5) + $T(R3:C1) * $T(R3:C5) + $T(R4:C1) * $T(R4:C5))==
464 * Related: =[[#FuncLIST][$LIST()]]=, =[[#FuncPRODUCT][$PRODUCT()]]=, =[[#FuncSUM][$SUM()]]=
465
466 #FuncT
467 ---+++ T( address ) -- content of a cell
468 * Syntax: ==$T( address )==
469 * Example: ==%<nop>CALC{"$T(R1:C5)"}%== returns the text in cell ==R1:C5==
470 * Related: =[[#FuncCOLUMN][$COLUMN()]]=, =[[#FuncROW][$ROW()]]=
471
472 #FuncTRANSLATE
473 ---+++ TRANSLATE( text, from, to ) -- translate text from one set of characters to another
474 * The translation is done =from= a set =to= a set, one character by one. The =text= may contain commas; all three parameters are required. In the =from= and =to= parameters you can write =$comma= to escape comma, =$sp= to escape space
475 * Syntax: ==$TRANSLATE( text, from, to )==
476 * Example: ==%<nop>CALC{"$TRANSLATE(boom,bm,cl)"}%== returns ==cool==
477 * Example: ==%<nop>CALC{"$TRANSLATE(one, two,$comma,;)"}%== returns ==one; two==
478 * Related: =[[#FuncREPLACE][$REPLACE()]]=, =[[#FuncSUBSTITUTE][$SUBSTITUTE()]]=
479
480 #FuncTIME
481 ---+++ TIME( text ) -- convert a date string into a serialized date number
482 * Serialized date is seconds since the Epoch, e.g. midnight, 01 Jan 1970. Current time is taken if the date string is empty. Supported date formats: =31 Dec 2009=; =31 Dec 2009 GMT=; =31 Dec 09=; =31-Dec-2009=; =31/Dec/2009=; =2009/12/31=; =2009-12-31=; =2009/12/31=; =2009/12/31 23:59=; =2009/12/31 - 23:59=; =2009-12-31-23-59=; =2009/12/31 - 23:59:59=; =2009.12.31.23.59.59=. Date is assumed to be server time; add =GMT= to indicate Greenwich time zone
483 * Syntax: ==$TIME( _text_ )==
484 rizwank 1.1 * Example: ==%<nop>CALC{"$TIME(2003/10/14 GMT)"}%== returns ==1066089600==
485 * Related: =[[#FuncFORMATTIME][$FORMATTIME()]]=, =[[#FuncFORMATGMTIME][$FORMATGMTIME()]]=, =[[#FuncTIMEADD][$TIMEADD()]]=, =[[#FuncTIMEDIFF][$TIMEDIFF()]]=, =[[#FuncTODAY][$TODAY()]]=, =[[#FuncWORKINGDAYS][$WORKINGDAYS()]]=
486
487 #FuncTIMEADD
488 ---+++ TIMEADD( serial, value, unit ) -- add a value to a serialized date
489 * The unit is seconds if not specified; unit can be =second=, =minute=, =hour=, =day=, =week=, =month=, =year=. Note: An approximation is used for month and year calculations
490 * Syntax: ==$TIMEADD( serial, value, _unit_ )==
491 * Example: ==%<nop>CALC{"$TIMEADD($TIME(), 2, week)"}%== returns the serialized date two weeks from now
492 * Related: =[[#FuncFORMATTIME][$FORMATTIME()]]=, =[[#FuncFORMATGMTIME][$FORMATGMTIME()]]=, =[[#FuncTIME][$TIME()]]=, =[[#FuncTIMEDIFF][$TIMEDIFF()]]=, =[[#FuncTODAY][$TODAY()]]=
493
494 #FuncTIMEDIFF
495 ---+++ TIMEDIFF( serial_1, serial_2, unit ) -- time difference between two serialized dates
496 * The unit is seconds if not specified; unit can be specified as in =$TIMEADD()=. Note: An approximation is used for month and year calculations. Use =$FORMAT()= or =$INT()= to format real numbers
497 * Syntax: ==$TIMEDIFF( serial_1, serial_2, _unit_ )==
498 * Example: ==%<nop>CALC{"$TIMEDIFF($TIME(), $EVAL($TIME()+90), minute)"}%== returns ==1.5==
499 * Related: =[[#FuncFORMAT][$FORMAT()]]=, =[[#FuncFORMATTIME][$FORMATTIME()]]=, =[[#FuncFORMATGMTIME][$FORMATGMTIME()]]=, =[[#FuncINT][$INT()]]=, =[[#FuncTIME][$TIME()]]=, =[[#FuncTIMEADD][$TIMEADD()]]=, =[[#FuncTODAY][$TODAY()]]=, =[[#FuncWORKINGDAYS][$WORKINGDAYS()]]=
500
501 #FuncTODAY
502 ---+++ TODAY( ) -- serialized date of today at midnight GMT
503 * In contrast, the related =$TIME()= returns the serialized date of today at the current time, e.g. it includes the number of seconds since midnight GMT
504 * Syntax: ==$TODAY( )==
505 rizwank 1.1 * Example: ==%<nop>CALC{"$TODAY()"}%== returns the number of seconds since Epoch
506 * Related: =[[#FuncFORMATTIME][$FORMATTIME()]]=, =[[#FuncFORMATGMTIME][$FORMATGMTIME()]]=, =[[#FuncTIME][$TIME()]]=, =[[#FuncTIMEADD][$TIMEADD()]]=, =[[#FuncTIMEDIFF][$TIMEDIFF()]]=
507
508 #FuncTRIM
509 ---+++ TRIM( text ) -- trim spaces from text
510 * Removes all spaces from text except for single spaces between words
511 * Syntax: ==$TRIM( text )==
512 * Example: ==%<nop>CALC{"$TRIM( eat spaces )"}%== returns ==eat spaces==
513 * Related: =[[#FuncEXACT][$EXACT()]]=, =[[#FuncPROPERSPACE][$PROPERSPACE()]]=
514
515 #FuncUPPER
516 ---+++ UPPER( text ) -- upper case string of a text
517 * Syntax: ==$UPPER( text )==
518 * Example: ==%<nop>CALC{"$UPPER($T(R1:C5))"}%== returns the upper case string of the text in cell ==R1:C5==
519 * Related: =[[#FuncLOWER][$LOWER()]]=, =[[#FuncPROPER][$PROPER()]]=, =[[#FuncPROPERSPACE][$PROPERSPACE()]]=, =[[#FuncTRIM][$TRIM()]]=
520
521 #FuncVALUE
522 ---+++ VALUE( text ) -- convert text to number
523 * Extracts a number from ==text==. Returns ==0== if not found
524 * Syntax: ==$VALUE( text )==
525 * Example: ==%<nop>CALC{"$VALUE(US$1,200)"}%== returns ==1200==
526 rizwank 1.1 * Example: ==%<nop>CALC{"$VALUE(<nop>PrjNotebook1234)"}%== returns ==1234==
527 * Example: ==%<nop>CALC{"$VALUE(Total: -12.5)"}%== returns ==-12.5==
528 * Related: =[[#FuncEVAL][$EVAL()]]=, =[[#FuncINT][$INT()]]=
529
530 #FuncWORKINGDAYS
531 ---+++ WORKINGDAYS( serial_1, serial_2 ) -- working days between two serialized dates
532 * Working days are Monday through Friday (sorry, Israel!)
533 * Syntax: ==$WORKINGDAYS( serial_1, serial_2 )==
534 * Example: ==%<nop>CALC{"$WORKINGDAYS($TIME(2004/07/15), $TIME(2004/08/03))"}%== returns ==13==
535 * Related: =[[#FuncSUMDAYS][$SUMDAYS()]]=, =[[#FuncTIME][$TIME()]]=, =[[#FuncTIMEDIFF][$TIMEDIFF()]]=
536
537 ---++ Bug Tracking Example
538
539 | *Bug#:* | *Priority:* | *Subject:* | *Status:* | *Days to fix* |
540 | Bug:1231 | Low | File Open ... | Open | 3 |
541 | Bug:1232 | High | Memory Window ... | Fixed | 2 |
542 | Bug:1233 | Medium | Usability issue ... | Assigned | 5 |
543 | Bug:1234 | High | No arrange ... | Fixed | 1 |
544 | Total: %CALC{"$ROW(-2)"}% \
545 | %CALC{"$COUNTITEMS( R2:C$COLUMN()..R$ROW(-1):C$COLUMN() )"}% | . \
546 | %CALC{"$COUNTITEMS( R2:C$COLUMN()..R$ROW(-1):C$COLUMN() )"}% \
547 rizwank 1.1 | Total: %CALC{"$SUM( R2:C$COLUMN()..R$ROW(-1):C$COLUMN() )"}% |
548
549 The last row is defined as:
550
551 <verbatim>
552 | Total: %CALC{"$ROW(-2)"}% \
553 | %CALC{"$COUNTITEMS( R2:C$COLUMN()..R$ROW(-1):C$COLUMN() )"}% | . \
554 | %CALC{"$COUNTITEMS( R2:C$COLUMN()..R$ROW(-1):C$COLUMN() )"}% \
555 | Total: %CALC{"$SUM( R2:C$COLUMN()..R$ROW(-1):C$COLUMN() )"}% |
556 </verbatim>
557
558 Above table is created manually. Another Plugin could build the table dynamically, e.g. by pulling data out of a bug tracking system. The Spreadsheet Plugin can be used to display table data statistics.
559
560 ---++ Plugin Settings
561
562 Plugin settings are stored as preferences variables. To reference
563 a plugin setting write ==%<nop><plugin>_<setting>%==, i.e. ==%<nop>SPREADSHEETPLUGIN_SHORTDESCRIPTION%==
564
565 * One line description, is shown in the %TWIKIWEB%.TextFormattingRules topic:
566 * Set SHORTDESCRIPTION = Add spreadsheet calculation like ="$SUM( $ABOVE() )"= to tables located in %WIKITOOLNAME% topics.
567
568 rizwank 1.1 * Debug plugin: (See output in =data/debug.txt=)
569 * Set DEBUG = 0
570
571 * Do not handle =%<nop>CALC{}%= tag in included topic while including topic: (default: 1)
572 * Set SKIPINCLUDE = 1
573
574 <noautolink>
575 * [[%TWIKIWEB%.WikiWord][WikiWords]] to exclude from being spaced out by the ==$PROPERSPACE(text)== function. This comma delimited list can be overloaded by a DONTSPACE preferences variable:
576 * Set DONTSPACE = CodeWarrior, MacDonald, McIntosh, RedHat, SuSE
577 </noautolink>
578
579
580 ---++ Plugin Installation Instructions
581
582 __Note:__ You do not need to install anything on the browser to use this plugin. Below installation instructions are for the administrator who needs to install this plugin on the TWiki server.
583
584 * Download the ZIP file from the <nop>%TOPIC% home
585 * Unzip ==SpreadSheetPlugin.zip== in your twiki installation directory. Content:
586 | *File:* | *Description:* |
587 | ==data/TWiki/%TOPIC%.txt== | Plugin topic |
588 | ==data/TWiki/%TOPIC%.txt,v== | Plugin topic repository |
589 rizwank 1.1 | ==lib/TWiki/Plugins/%TOPIC%.pm== | Plugin Perl module |
590 * Test if the "Total" in the first table in this topic is correct.
591
592 ---++ Plugin Info
593
594 | Plugin Author: | TWiki:Main/PeterThoeny |
595 | Plugin Version: | 18 Oct 2004 |
596 | Change History: | <!-- specify latest version first --> |
597 | 24 Oct 2004: | Added $EXISTS(), contributed by TWiki:Main/RodrigoChandia; added $PERCENTILE() |
598 | 18 Oct 2004: | Added $LISTJOIN() |
599 | 26 Sep 2004: | Added $FORMAT(KB), $FORMAT(MB), contributed by TWiki:Main/ArthurClemens; added $FORMAT(KBMB), $EVEN(), $ODD() |
600 | 17 Jul 2004: | Added $WORKINGDAYS(), contributed by TWiki:Main/CrawfordCurrie |
601 | 24 May 2004: | Refactored documentation (no code changes) |
602 | 03 Apr 2004: | Added $ABS(), $LISTIF(); fixed $VALUE() to remove leading zeros; changed $FIND() and $SEARCH() to return 0 instead of empty string if no match |
603 | 21 Mar 2004: | Added $LISTITEM(); fixed call to unofficial function |
604 | 16 Mar 2004: | Added $LISTMAP(), $LISTREVERSE(), $LISTSIZE(), $LISTSORT(), $LISTUNIQUE(), $SETM(); retired $COUNTUNIQUE() in favor of $COUNTITEMS($LISTUNIQUE()); fixed evaluation order issue of $IF(); fixed missing eval error messages suppressed since version 06 Mar 2004; redirect stderr messages to warning |
605 | 08 Mar 2004: | Added $LIST() |
606 | 06 Mar 2004: | Added $AND(), $MOD(), $NOT(), $OR(), $PRODUCT(), $PROPER(), $PROPERSPACE(), $RAND(), $REPEAT(), $SIGN(), $VALUE(); added digits parameter to $ROUND(); renamed $MULT() to $PRODUCT(); $MULT() is deprecated and undocumented |
607 | 27 Feb 2004: | Added $COUNTUNIQUE() |
608 | 24 Oct 2003: | Added $SET(), $GET(), $MEDIAN(); added $SUMPRODUCT(), inspired by TWiki:Main/RobertWithrow; added $SUMDAYS(), contributed by TWiki:Main/SvenDowideit |
609 | 21 Oct 2003: | Added support for lists =(1, 2, 3)= and lists of table ranges =(R1:C1..R1:C5, R3:C1..R3:C5)= for all functions that accept a table range; added $TIMEADD(); in $TIMEDIFF() added week unit; in $FORMATTIME() changed $weekday to $wd and added $wday and $weekday |
610 rizwank 1.1 | 14 Oct 2003: | Added $TIME(), $TODAY(), $FORMATTIME(), $FORMATGMTIME(), $TIMEDIFF() |
611 | 13 Oct 2003: | Added $MULT(), contributed by TWiki:Main/GerritJanBaarda |
612 | 30 Jul 2003: | Added $TRANSLATE() |
613 | 19 Jul 2003: | Added $FIND(), $NOP(), $REPLACE(), $SEARCH(), $SUBSTITUTE(), contributed by TWiki:Main/PaulineCheung |
614 | 19 Apr 2003: | Added $COUNTSTR(), $EXACT(), $IF(), $ROUND(), $TRIM(); added $FORMAT(), contributed by TWiki:Main/JimStraus; support =%= modulus operator in $EVAL(), $INT(), and $ROUND(); fixed bug in $DEF() |
615 | 07 Jun 2002: | Added $DEF(), contributed by TWiki:Main/MartinFuzzey; allow values with HTML formatting like =<u>102</u>=, suggested by TWiki:Main/GladeDiviney; added SKIPINCLUDE setting |
616 | 12 Mar 2002: | Support for multiple functions per nesting level |
617 | 15 Jan 2002: | Added $CHAR(), $CODE() and $LENGTH() |
618 | 12 Nov 2001: | Added $RIGHT() |
619 | 12 Aug 2001: | Fixed bug of disappearing multi-column cells |
620 | 19 Jul 2001: | Fixed incorrect $SUM() calculation of cell with value =0= |
621 | 14 Jul 2001: | Changed to plug & play |
622 | 01 Jun 2001: | Fixed insecure dependencies for $MIN() and $MAX() |
623 | 16 Apr 2001: | Fixed div by 0 bug in $AVERAGE() |
624 | 17 Mar 2001: | Initial version with $ABOVE(), $AVERAGE(), $COLUMN(), $COUNTITEMS(), $EVAL(), $INT(), $LEFT(), $LOWER(), $MAX(), $MIN(), $ROW(), $SUM(), $T(), $UPPER() |
625 | CPAN Dependencies: | none |
626 | TWiki:Plugins/Benchmark: | %TWIKIWEB%.GoodStyle 99%, %TWIKIWEB%.FormattedSearch 99%, %TOPIC% 95% |
627 | Other Dependencies: | none |
628 | Perl Version: | 5.000 and up |
629 | Plugin Home: | http://TWiki.org/cgi-bin/view/Plugins/%TOPIC% |
630 | Feedback: | http://TWiki.org/cgi-bin/view/Plugins/%TOPIC%Dev |
631 rizwank 1.1
632 __Related Topics:__ %TWIKIWEB%.TWikiPreferences, %TWIKIWEB%.TWikiPlugins
633
634 -- TWiki:Main/PeterThoeny - 24 Oct 2004
635
|
Return to
SpreadSheetPlugin.txt
CVS log
Up to [RizwankCVS] / geekymedia_web / twiki / data / TWiki