# Difference: SpreadSheetPlugin (15 vs. 16)

#### Revision 162007-10-13 - PeterThoeny

Line: 1 to 1

This Plugin adds spreadsheet capabilities to TWiki topics. Formulae like `%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.

Line: 121 to 121

### EVAL( formula ) -- evaluate a simple mathematical formula

• Addition, substraction, multiplication, division and modulus of numbers are supported. Any nesting is permitted
>
>
• Numbers may be decimal integers (`1234`), binary integers (`0b1110011`), octal integers (`01234`), hexadecimal integers (`0x1234`) or of exponential notation (`12.34e-56`)

• Syntax: `\$EVAL( formula )`
• Example: `%CALC{"\$EVAL( (5 * 3) / 2 + 1.1 )"}%` returns `8.6`
• Related: `\$EXEC()`, `\$INT()`, `\$MOD()`, `\$ROUND()`, `\$VALUE()`
Line: 150 to 151

### EXISTS( topic ) -- check if topic exists

Changed:
<
<
• Topic can be `TopicName` or a `Web.TopicName`
>
>
• Topic can be `TopicName` or a `Web.TopicName`. Current web is used if web is not specified.

• Syntax: `\$EXISTS( topic )`
• Example: `%CALC{"\$EXISTS(WebHome)"}%` returns `1`
• Example: `%CALC{"\$EXISTS(ThisDoesNotExist)"}%` returns `0`
>
>

### EXP( num ) -- exponent (e) raised to the power of a number

• EXP is the inverse of the LN function
• Syntax: `\$EXP( num )`
• Example: `%CALC{"\$EXP(1)"}%` returns `2.71828182845905`
• Related: `\$LN()`, `\$LOG()`

### FIND( string, text, start ) -- find one string within another string

• 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.
Line: 165 to 173

• Related: `\$REPLACE()`, `\$SEARCH()`

Changed:
<
<

### FORMAT( type, prec, number ) -- format a number to a certain type and precision

• 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
>
>

### FORMAT( type, precision, number ) -- format a number to a certain type and precision

• Supported `type`:
• `COMMA` for comma format, such as `12,345.68`
• `DOLLAR` for Dollar format, such as `\$12,345.68`
• `KB` for Kilo Byte format, such as `1205.63 KB`
• `MB` for Mega Byte format, such as `1.18 MB`
• `KBMB` for Kilo/Mega/Giga/Tera Byte auto-adjust format
• `NUMBER` for number, such as `12345.7`
• `PERCENT` for percent format, such as `12.3%`
• The `precision` indicates the the number of digits after the dot

• Syntax: `\$FORMAT( type, prec, number )`
• Example: `%CALC{"\$FORMAT(COMMA, 2, 12345.6789)"}%` returns `12,345.68`
• Example: `%CALC{"\$FORMAT(DOLLAR, 2, 12345.67)"}%` returns `\$12,345.68`
Line: 176 to 192

• Example: `%CALC{"\$FORMAT(KBMB, 2, 1234567890)"}%` returns `1.15 GB`
• Example: `%CALC{"\$FORMAT(NUMBER, 1, 12345.67)"}%` returns `12345.7`
• Example: `%CALC{"\$FORMAT(PERCENT, 1, 0.1234567)"}%` returns `12.3%`
Changed:
<
<
• Related: `\$ROUND()`
>
>
• Related: `\$FORMATTIME()`, `\$FORMATTIMEDIFF()`, `\$ROUND()`

### FORMATGMTIME( serial, text ) -- convert a serialized date into a GMT date string

• The date string represents the time in Greenwich time zone. Same variable expansion as in `\$FORMATTIME()`.
• Syntax: `\$FORMATGMTIME( serial, text )`
• Example: `%CALC{"\$FORMATGMTIME(1041379200, \$day \$mon \$year)"}%` returns `01 Jan 2003`
• Related: `\$FORMATTIME()`, `\$FORMATTIMEDIFF()`, `\$TIME()`, `\$TIMEADD()`, `\$TIMEDIFF()`, `\$TODAY()`

### FORMATTIME( serial, text ) -- convert a serialized date into a date string

• 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.
• Syntax: `\$FORMATTIME( serial, text )`
• Example: `%CALC{"\$FORMATTIME(0, \$year/\$month/\$day GMT)"}%` returns `1970/01/01 GMT`
Changed:
<
<
• Related: `\$FORMATGMTIME()`, `\$TIME()`, `\$TIMEADD()`, `\$TIMEDIFF()`, `\$TODAY()`
>
>
• Related: `\$FORMATGMTIME()`, `\$TIME()`, `\$FORMATTIMEDIFF()`, `\$TIMEADD()`, `\$TIMEDIFF()`, `\$TODAY()`

Changed:
<
<

### FORMATGMTIME( serial, text ) -- convert a serialized date into a GMT date string

• The date string represents the time in Greenwich time zone. Same variable expansion as in `\$FORMATTIME()`.
• Syntax: `\$FORMATGMTIME( serial, text )`
• Example: `%CALC{"\$FORMATGMTIME(1041379200, \$day \$mon \$year)"}%` returns `01 Jan 2003`
• Related: `\$FORMATTIME()`, `\$TIME()`, `\$TIMEADD()`, `\$TIMEDIFF()`, `\$TODAY()`
>
>

### FORMATTIMEDIFF( unit, precision, time ) -- convert elapsed time to a string

• Convert elapsed `time` to a human readable format, such as: `12 hours and 3 minutes`
• The input `unit` can be `second`, `minute`, `hour`, `day`, `month`, `year`. Note: An approximation is used for month and year calculations.
• The `precision` indicates the number of output units to use
• Syntax: `\$FORMATTIMEDIFF( unit, precision, time )`
• Example: `%CALC{"\$FORMATTIMEDIFF(min, 1, 200)"}%` returns `3 hours`
• Example: `%CALC{"\$FORMATTIMEDIFF(min, 2, 200)"}%` returns `3 hours and 20 minutes`
• Example: `%CALC{"\$FORMATTIMEDIFF(min, 1, 1640)"}%` returns `1 day`
• Example: `%CALC{"\$FORMATTIMEDIFF(min, 2, 1640)"}%` returns `1 day and 3 hours`
• Example: `%CALC{"\$FORMATTIMEDIFF(min, 3, 1640)"}%` returns `1 day, 3 hours and 20 minutes`
• Related: `\$FORMATTIME()`, `\$TIME()`, `\$TIMEADD()`, `\$TIMEDIFF()`

Line: 211 to 240

### INT( formula ) -- evaluate formula and round down to nearest integer

• Addition, substraction, multiplication, division and modulus of numbers are supported. Any nesting is permitted
>
>
• Numbers may be decimal integers (`1234`), binary integers (`0b1110011`), octal integers (`01234`), hexadecimal integers (`0x1234`) or of exponential notation (`12.34e-56`)
• If you expect a single decimal integer value with leading zeros, use `\$INT( \$VALUE( number ) )`

• Syntax: `\$INT( formula )`
• Example: `%CALC{"\$INT(10 / 4)"}%` returns `2`
>
>
• Example: `%CALC{"\$INT(\$VALUE(09))"}%` returns `9`

• Related: `\$EVAL()`, `\$ROUND()`, `\$VALUE()`

Line: 309 to 341

• Example: `%CALC{"\$LISTUNIQUE(Apple, Orange, Apple, Kiwi)"}%` returns `Apple, Orange, Kiwi`
• Related: `\$COUNTITEMS()`, `\$COUNTSTR()`, `\$LIST()`, `\$LISTIF()`, `\$LISTITEM()`, `\$LISTMAP()`, `\$LISTREVERSE()`, `\$LISTSIZE()`, `\$LISTSORT()`, `\$SUM()`
>
>

### LN( num ) -- natural logarithm of a number

• LN is the inverse of the EXP function
• Syntax: `\$LN( num )`
• Example: `%CALC{"\$LN(10)"}%` returns `2.30258509299405`
• Related: `\$EXP()`, `\$LOG()`

### LOG( num, base ) -- logarithm of a number to a given base

• base-10 logarithm of a number (if base is 0 or not specified), else logarithm of a number to the given base
• Syntax: `\$LOG( num, base )`
• Example: `%CALC{"\$LOG(1000)"}%` returns `3`
• Example: `%CALC{"\$LOG(16, 2)"}%` returns `4`
• Related: `\$EXP()`, `\$LN()`

### LOWER( text ) -- lower case string of a text

• Syntax: `\$LOWER(text)`
Line: 377 to 424

• Example: `%CALC{"\$PERCENTILE(75, 400, 200, 500, 100, 300)"}%` returns `450`
• Related: `\$LIST()`, `\$MAX()`, `\$MEDIAN()`, `\$MIN()`
>
>

### PI( ) -- mathematical constant Pi, 3.14159265358979

• Syntax: `\$PI( )`
• Example: `%CALC{"\$PI()"}%` returns `3.14159265358979`

### PRODUCT( list ) -- product of a list or range of cells

• Syntax: `\$PRODUCT( list )`
Line: 387 to 439

### PROPER( text ) -- properly capitalize text

• Capitalize letters that follow any character other than a letter; convert all other letters to lowercase letters
• Syntax: `\$PROPER( text )`
Changed:
<
<
• Example: `%CALC{"PROPER(a small STEP)"}%` returns `A Small Step`
• Example: `%CALC{"PROPER(f1 (formula-1))"}%` returns `F1 (Formula-1)`
>
>
• Example: `%CALC{"\$PROPER(a small STEP)"}%` returns `A Small Step`
• Example: `%CALC{"\$PROPER(f1 (formula-1))"}%` returns `F1 (Formula-1)`

• Related: `\$LOWER()`, `\$PROPERSPACE()`, `\$TRIM()`, `\$UPPER()`

### PROPERSPACE( text ) -- properly space out WikiWords

Changed:
<
<
• Properly spaces out WikiWords preceeded by white space, parenthesis, or `][`. Words listed in the DONTSPACE TWikiPreferences variable or DONTSPACE Plugins setting are excluded
>
>
• Properly spaces out WikiWords preceeded by white space, parenthesis, or `][`. Words listed in the DONTSPACE TWikiPreferences variable or DONTSPACE Plugins setting are excluded

• Syntax: `\$PROPERSPACE( text )`
• Example: Assuming DONTSPACE contains MacDonald: `%CALC{"\$PROPERSPACE(Old MacDonald had a ServerFarm, EeEyeEeEyeOh)"}%` returns `Old MacDonald had a Server Farm, Ee Eye Ee Eye Oh`
• Related: `\$LOWER()`, `\$PROPER()`, `\$TRIM()`, `\$UPPER()`
Line: 441 to 493

### SEARCH( string, text, start ) -- search a string within a text

Changed:
<
<
• 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 RegularExpression search; use `\$FIND()` for non-regular expression searching. Starting position is 1; a 0 is returned if nothing is matched
>
>
• 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 RegularExpression search; use `\$FIND()` for non-regular expression searching. Starting position is 1; a 0 is returned if nothing is matched

• Syntax: `\$SEARCH( string, text, start )`
• Example: `%CALC{"\$SEARCH([uy], fluffy)"}%` returns `3`
• Example: `%CALC{"\$SEARCH([uy], fluffy, 3)"}%` returns `6`
Line: 476 to 528

• Example: `%CALC{"\$SIGN(-12.5)"}%` returns `-1`
• Related: `\$ABS()`, `\$EVAL()`, `\$EVEN()`, `\$INT()`, `\$NOT()`, `\$ODD()`
>
>

### SQRT( num ) -- square root of a number

• Syntax: `\$SQRT( num )`
• Example: `%CALC{"\$SQRT(16)"}%` returns `4`

### SUBSTITUTE( text, old, new, instance, option ) -- substitute text

Changed:
<
<
• 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 RegularExpression search if the `option` is set to `r`
>
>
• 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 RegularExpression search if the `option` is set to `r`

• Syntax: `\$SUBSTITUTE( text, old, new, instance, option )`
• Example: `%CALC{"\$SUBSTITUTE(Good morning, morning, day)"}%` returns `Good day`
• Example: `%CALC{"\$SUBSTITUTE(Q2-2002,2,3)"}%` returns `Q3-3003`
Line: 524 to 581

• 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
• Syntax: `\$TIME( text )`
• Example: `%CALC{"\$TIME(2003/10/14 GMT)"}%` returns `1066089600`
Changed:
<
<
• Related: `\$FORMATTIME()`, `\$FORMATGMTIME()`, `\$TIMEADD()`, `\$TIMEDIFF()`, `\$TODAY()`, `\$WORKINGDAYS()`
>
>
• Related: `\$FORMATGMTIME()`, `\$FORMATTIME()`, `\$FORMATTIMEDIFF()`, `\$TIMEADD()`, `\$TIMEDIFF()`, `\$TODAY()`, `\$WORKINGDAYS()`

### TIMEADD( serial, value, unit ) -- add a value to a serialized date

Changed:
<
<
• 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
>
>
• 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

• Syntax: `\$TIMEADD( serial, value, unit )`
• Example: `%CALC{"\$TIMEADD(\$TIME(), 2, week)"}%` returns the serialized date two weeks from now
• Related: `\$FORMATTIME()`, `\$FORMATGMTIME()`, `\$TIME()`, `\$TIMEDIFF()`, `\$TODAY()`

### TIMEDIFF( serial_1, serial_2, unit ) -- time difference between two serialized dates

Changed:
<
<
• 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
>
>
• 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()`, `\$FORMATTIMEDIFF()` or `\$INT()` to format real numbers

• Syntax: `\$TIMEDIFF( serial_1, serial_2, unit )`
• Example: `%CALC{"\$TIMEDIFF(\$TIME(), \$EVAL(\$TIME()+90), minute)"}%` returns `1.5`
Changed:
<
<
• Related: `\$FORMAT()`, `\$FORMATTIME()`, `\$FORMATGMTIME()`, `\$INT()`, `\$TIME()`, `\$TIMEADD()`, `\$TODAY()`, `\$WORKINGDAYS()`
>
>
• Related: `\$FORMAT()`, `\$FORMATGMTIME()`, `\$FORMATTIME()`, `\$FORMATTIMEDIFF()`, `\$INT()`, `\$TIME()`, `\$TIMEADD()`, `\$TODAY()`, `\$WORKINGDAYS()`

Line: 580 to 637

### Can I use CALC in a formatted search?

Changed:
<
<
Specifically, how can I output some conditional text in a FormattedSearch?
>
>
Specifically, how can I output some conditional text in a FormattedSearch?

Changed:
<
<
You need to escape the CALC so that it executes once per search hit. This can be done by escaping the `%` signs of `%CALC{...}%` with `\$percnt`. For example, to execute `\$IF(\$EXACT(\$formfield(Tested), Yes), %PUBURL%/%TWIKIWEB%/TWikiDocGraphics/choice-yes.gif, %PUBURL%/%TWIKIWEB%/TWikiDocGraphics/choice-no.gif)` in the `format=""` parameter, write this:
>
>
You need to escape the CALC so that it executes once per search hit. This can be done by escaping the `%` signs of `%CALC{...}%` with `\$percnt`. For example, to execute `\$IF(\$EXACT(\$formfield(Tested), Yes), %PUBURL%/%SYSTEMWEB%/TWikiDocGraphics/choice-yes.gif, %PUBURL%/%SYSTEMWEB%/TWikiDocGraphics/choice-no.gif)` in the `format=""` parameter, write this:

Changed:
<
<
`%SEARCH{ .... format="| \$topic | \$percntCALC{\$IF(\$EXACT(\$formfield(Tested), Yes), %PUBURL%/%TWIKIWEB%/TWikiDocGraphics/choice-yes.gif, %PUBURL%/%TWIKIWEB%/TWikiDocGraphics/choice-no.gif)}\$percnt |" }%`
>
>
`%SEARCH{ .... format="| \$topic | \$percntCALC{\$IF(\$EXACT(\$formfield(Tested), Yes), %PUBURL%/%SYSTEMWEB%/TWikiDocGraphics/choice-yes.gif, %PUBURL%/%SYSTEMWEB%/TWikiDocGraphics/choice-no.gif)}\$percnt |" }%`

### How can I easily repeat a formula in a table?

Line: 627 to 684
Plugin settings are stored as preferences variables. To reference a plugin setting write `%<plugin>_<setting>%`, i.e. `%SPREADSHEETPLUGIN_SHORTDESCRIPTION%`
Changed:
<
<
>
>

• Set SHORTDESCRIPTION = Add spreadsheet calculation like `"\$SUM( \$ABOVE() )"` to TWiki tables and other topic text

• Debug plugin: (See output in `data/debug.txt`)
Line: 637 to 694

• Set SKIPINCLUDE = 1

Changed:
<
<
• WikiWords to exclude from being spaced out by the `\$PROPERSPACE(text)` function. This comma delimited list can be overloaded by a DONTSPACE preferences variable:
>
>
• WikiWords to exclude from being spaced out by the `\$PROPERSPACE(text)` function. This comma delimited list can be overloaded by a DONTSPACE preferences variable:

Line: 658 to 715

## Plugin Info

 Plugin Author: TWiki:Main/PeterThoeny
Changed:
<
<
>
>

Changed:
<
<
 Plugin Version: 23 Jan 2007 (r12607)
>
>
 Plugin Version: 13 Oct 2007 (15270)

 Change History: `<-- specify latest version first -->`
>
>
 13 Oct 2007: Added \$FORMATTIMEDIFF() 09 Sep 2007: Enhanced documentation for \$EVAL() and \$INT() 02 Jun 2007: Added VarCALC to have `%CALC{}%` listed in TWikiVariables 14 Apr 2007: Fixing bug in \$EXISTS() that required full `web.topic` instead of just `topic` 11 Mar 2007: Fixing bug in \$VALUE() and \$INT(), introduced by version 09 Mar 2007 09 Mar 2007: Added \$EXP(), \$LN(), \$LOG(), \$PI(), \$SQRT(); fixed \$ROUND() bug, contributed by TWiki:Main/SergejZnamenskij

 23 Jan 2007: Enhanced documentation 18 Dec 2006: Added \$LISTRAND(), \$LISTSHUFFLE(), \$LISTTRUNCATE(); fixed spurious newline at end of topic, contributed by TWiki:Main/MichaelDaum 10 Oct 2006: Enhanced documentation
Line: 697 to 760

 16 Apr 2001: Fixed div by 0 bug in \$AVERAGE() 17 Mar 2001: Initial version with \$ABOVE(), \$AVERAGE(), \$COLUMN(), \$COUNTITEMS(), \$EVAL(), \$INT(), \$LEFT(), \$LOWER(), \$MAX(), \$MIN(), \$ROW(), \$SUM(), \$T(), \$UPPER() CPAN Dependencies: none
Changed:
<
<
 TWiki:Plugins/Benchmark: GoodStyle 99%, FormattedSearch 99%, SpreadSheetPlugin 95%
>
>
 TWiki:Plugins/Benchmark: GoodStyle 99%, FormattedSearch 99%, SpreadSheetPlugin 95%

Changed:
<
<
Related Topics: TWikiPreferences, TWikiPlugins
>
>
Related Topics: TWikiPreferences, TWikiPlugins, VarCALC

Changed:
<
<
-- TWiki:Main/PeterThoeny - 23 Jan 2007
>
>
-- TWiki:Main/PeterThoeny - 13 Oct 2007

Copyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback