| |
Page contents
- Syntax Rules
- Use CALC or CALCULATE
- Built-in Spreadsheet Plugin Functions
- ABOVE( ) -- address range of cells above the current cell
- ABS( num ) -- absolute value of a number
- ADDLIST( name, list ) -- append a list to a list variable
- AND( list ) -- logical AND of a list
- AVERAGE( list ) -- average of a list or a range of cells
- BIN2DEC( num ) -- convert a binary number to decimal
- BITXOR( text ) -- bit-wise XOR of text
- CEILING( num ) -- return the smallest integer following a number
- CHAR( number ) -- ASCII character represented by number
- CODE( text ) -- ASCII numeric value of character
- COLUMN( offset ) -- current column number
- COUNTITEMS( list ) -- count individual items in a list
- COUNTSTR( list, str ) -- count the number of cells
- DEC2BIN( num, width ) -- convert a decimal number to binary
- DEC2HEX( num, width ) -- convert a decimal number to hexadecimal
- DEC2OCT( num, width ) -- convert a decimal number to octal
- DEF( list ) -- find first non-empty list item or cell
- EMPTY( text ) -- test for empty text
- EVAL( formula ) -- evaluate a simple mathematical formula
- EVEN( num ) -- test for even number
- EXACT( text1, text2 ) -- compare two text strings
- EXEC( formula ) -- execute a spreadsheet formula
- EXISTS( topic ) -- check if topic exists
- EXP( num ) -- exponent (e) raised to the power of a number
- FILTER( expression, text ) -- filter out characters from text
- FIND( string, text, start ) -- find one string within another string
- FLOOR( num ) -- return the largest integer preceding a number
- FORMAT( type, precision, number ) -- format a number
- FORMATGMTIME( serial, text ) -- convert a serialized date
- FORMATTIME( serial, text ) -- convert a serialized date
- FORMATTIMEDIFF( unit, precision, time, option ) -- convert elapsed time
- GET( name ) -- get the value of a variable
- GETHASH( name, key ) -- get the value of a previously set hash key
- GETLIST( name ) -- get the list from a list variable
- HASH2LIST( name, format ) -- convert a hash to a list
- HASHCOPY( from, to ) -- copy a hash
- HASHEACH( formula, name ) -- evaluate and update each hash element
- HASHEXISTS( name, key ) -- test if a hash exists
- HASHREVERSE( name ) -- reverse a hash
- HEX2DEC( num ) -- convert a hexadecimal number to decimal
- HEXDECODE( hexcode ) -- convert hexadecimal code to string
- HEXENCODE( text ) -- encode text into hexadecimal code
- IF( condition, then, else ) -- return a value based on a condition
- INSERTSTRING( text, start, new ) -- insert a string into a text string
- INT( formula ) -- evaluate formula and return integer truncated towards 0
- ISDIGIT( text ) -- test for digits
- ISLOWER( text ) -- test for lower case text
- ISUPPER( text ) -- test for upper case text
- ISWIKIWORD( text ) -- test for WikiWord
- LEFT( ) -- address range of cells to the left of the current cell
- LEFTSTRING( text, num ) -- extract characters at the beginning
- LENGTH( text ) -- length of text in bytes
- LIST( range ) -- convert content of a cell range into a list
- LIST2HASH( name, list ) -- create a hash from a list
- LISTEACH( formula, list ) -- evaluate and update each element of a list
- LISTIF( condition, list ) -- remove elements from a list
- LISTITEM( index, list ) -- get one element of a list
- LISTJOIN( separator, list ) -- convert a list into a string
- LISTNONEMPTY( list ) -- remove all empty elements from a list
- LISTRAND( list ) -- get one random element of a list
- LISTREVERSE( list ) -- opposite order of a list
- LISTSHUFFLE( list ) -- shuffle element of a list in random order
- LISTSIZE( list ) -- number of elements in a list
- LISTSORT( list ) -- sort a list
- LISTTRUNCATE( size, list ) -- truncate list to size
- LISTUNIQUE( list ) -- remove all duplicates from a list
- LN( num ) -- natural logarithm of a number
- LOG( num, base ) -- logarithm of a number to a given base
- LOWER( text ) -- lower case string of a text
- MAX( list ) - biggest value of a list or range of cells
- MEDIAN( list ) -- median of a list or range of cells
- MIN( list ) -- smallest value of a list or range of cells
- MOD( num, divisor ) -- reminder after dividing num by divisor
- NOEXEC( formula ) -- do not execute a spreadsheet formula
- NOP( text ) -- no-operation
- NOT( num ) -- reverse logic of a number
- OCT2DEC( num ) -- convert an octal number to decimal
- ODD( num ) -- test for odd number
- OR( list ) -- logical OR of a list
- PERCENTILE( num, list ) -- percentile of a list or range of cells
- PI( ) -- mathematical constant Pi, 3.14159265358979
- PRODUCT( list ) -- product of a list or range of cells
- PROPER( text ) -- properly capitalize text
- PROPERSPACE( text ) -- properly space out WikiWords
- RAND( max ) -- random number
- RANDSTRING( set, format ) -- random string & password generator
- REPEAT( text, num ) -- repeat text a number of times
- REPLACE( text, start, num, new ) -- replace part of a text string
- RIGHT( ) -- address range of cells to the right of the current cell
- RIGHTSTRING( text, num ) -- extract characters at the end
- ROUND( formula, digits ) -- round a number
- ROW( offset ) -- current row number
- SEARCH( string, text, start ) -- search a string within a text
- SET( name, value ) -- set a variable for later use
- SETHASH( name, key, value ) -- set a hash value for later use
- SETIFEMPTY( name, value ) -- set a variable only if empty
- SETLIST( name, list ) -- save a list for later use
- SETM( name, formula ) -- modify an existing variable
- SETMHASH( name, key, formula ) -- modify an existing hash
- SIGN( num ) -- sign of a number
- SPLIT( separator, text ) -- split a string into a list
- SQRT( num ) -- square root of a number
- STDEV( list ) -- standard deviation based on a sample
- STDEVP( list ) -- standard deviation based on the entire population
- SUBSTITUTE( text, old, new, instance, option ) -- substitute text
- SUBSTRING( text, start, num ) -- extract a substring out of a text string
- SUM( list ) -- sum of a list or range of cells
- SUMDAYS( list ) -- sum the days in a list or range of cells
- SUMPRODUCT( list, list ) -- scalar product on ranges of cells
- T( address ) -- content of a cell
- TIME( text ) -- convert a date string into a serialized date number
- TIMEADD( serial, value, unit ) -- add a value to a serialized date
- TIMEDIFF( serial_1, serial_2, unit ) -- time difference
- TODAY( ) -- serialized date of today at midnight GMT
- TRANSLATE( text, from, to ) -- translate text
- TRIM( text ) -- trim spaces from text
- UPPER( text ) -- upper case string of a text
- VALUE( text ) -- convert text to number
- VAR( list ) -- variance based on a sample
- VARP( list ) -- variance based on the entire population
- WHILE( condition, do ) -- do something while a condition is true
- WORKINGDAYS( serial_1, serial_2 ) -- working days
- XOR( list ) -- logical XOR of a list
- FAQ
- CALC in Included Topics
- Bug Tracking Example
- Plugin Settings
- Plugin Installation Instructions
- Plugin Info
|
| |
|
|
>
> | |
| | |
|
<
< | |
>
> | |
| | |
|
<
< | |
>
> | |
| | |
|
<
< | |
>
> | |
| | |
|
>
> | |
| |
|
| |
- Syntax:
$BITXOR( text )
- Example:
%CALCULATE{$BITXOR(A123)}% returns ¾ÎÍÌ
- Example:
%CALCULATE{$BITXOR($BITXOR(anything))}% returns anything
|
|
<
< | |
>
> | |
| |
CEILING( num ) -- return the smallest integer following a number |
| |
- Syntax:
$INSERTSTRING( text, start, new )
- Example:
%CALCULATE{$INSERTSTRING(abcdefg, 2, XYZ)}% returns abXYZcdefg
- Example:
%CALCULATE{$INSERTSTRING(abcdefg, -2, XYZ)}% returns abcdeXYZfg
|
|
<
< | |
>
> | |
| |
INT( formula ) -- evaluate formula and return integer truncated towards 0 |
| |
- Syntax:
$LEFTSTRING( text, num )
- Example:
%CALCULATE{$LEFTSTRING(abcdefg)}% returns a
- Example:
%CALCULATE{$LEFTSTRING(abcdefg, 5)}% returns abcde
|
|
<
< | |
>
> |
- Related:
$BITXOR(), $FILTER(), $FIND(), $INSERTSTRING(), $RANDSTRING(), $REPLACE(), $RIGHTSTRING(), $SEARCH(), $SUBSTITUTE(), $SUBSTRING(), $TRANSLATE()
|
| |
LENGTH( text ) -- length of text in bytes |
| | LISTRAND( list ) -- get one random element of a list
- Syntax:
$LISTRAND( list )
- Example:
%CALCULATE{$LISTRAND(Apple, Orange, Apple, Kiwi)}% returns one of the four elements
|
|
<
< |
- Related:
$COUNTITEMS(), $COUNTSTR(), $LIST(), $LISTEACH(), $LISTIF(), $LISTITEM(), $LISTSHUFFLE(), $LISTSIZE(), $LISTSORT(), $LISTUNIQUE(), $RAND(), $SUM()
|
>
> |
- Related:
$COUNTITEMS(), $COUNTSTR(), $LIST(), $LISTEACH(), $LISTIF(), $LISTITEM(), $LISTSHUFFLE(), $LISTSIZE(), $LISTSORT(), $LISTUNIQUE(), $RAND(), $RANDSTRING(), $SUM()
|
| |
LISTREVERSE( list ) -- opposite order of a list |
| | LISTSHUFFLE( list ) -- shuffle element of a list in random order
- Syntax:
$LISTSHUFFLE( list )
- Example:
%CALCULATE{$LISTSHUFFLE(Apple, Orange, Apple, Kiwi)}% returns the four elements in random order
|
|
<
< |
- Related:
$COUNTITEMS(), $COUNTSTR(), $LIST(), $LISTEACH(), $LISTIF(), $LISTITEM(), $LISTRAND(), $LISTSIZE(), $LISTSORT(), $LISTUNIQUE(), $RAND(), $SUM()
|
>
> |
- Related:
$COUNTITEMS(), $COUNTSTR(), $LIST(), $LISTEACH(), $LISTIF(), $LISTITEM(), $LISTRAND(), $LISTSIZE(), $LISTSORT(), $LISTUNIQUE(), $RAND(), $RANDSTRING(), $SUM()
|
| |
LISTSIZE( list ) -- number of elements in a list |
| | RAND( max ) -- random number
- Random number, evenly distributed between 0 and
max, or 0 and 1 if max is not specified
- Syntax:
$RAND( max )
|
|
<
< | |
>
> |
RANDSTRING( set, format ) -- random string & password generator
- Generate a random string from a
set of characters; the set may contain sequences like a..z; default is a..zA..Z0..9_. The format defines the string length or the output format; specify a number to indicate the length of the random string; default is 8 characters. Alternatively, specify a format string with x as placeholders for random characters, such xxxx-xxxx-xxxx-xxxx.
- Syntax:
$RANDSTRING( set, format )
- Example:
%CALCULATE{$RANDSTRING()}% returns a random string with 8 characters composed of alphanumeric characters and underscores
- Example:
%CALCULATE{$RANDSTRING(A..NP..Z1..9, xxxx-xxxx-xxxx-xxxx)}% returns four sets of random strings, separated by dashes, where each set has four characters composed of uppercase letters and numbers, excluding letter O and number 0
- Related:
$INSERTSTRING(), $SUBSTRING(), $LISTRAND(), $LISTSHUFFLE(), $RAND(), $REPEAT()
|
| |
REPEAT( text, num ) -- repeat text a number of times
- Syntax:
$REPEAT( text, num )
- Example:
%CALCULATE{$REPEAT(/\, 5)}% returns /\/\/\/\/\
|
|
<
< | |
>
> | |
| |
REPLACE( text, start, num, new ) -- replace part of a text string |
| |
- Syntax:
$RIGHTSTRING( text, num )
- Example:
%CALCULATE{$RIGHTSTRING(abcdefg)}% returns g
- Example:
%CALCULATE{$RIGHTSTRING(abcdefg, 5)}% returns cdefg
|
|
<
< | |
>
> |
- Related:
$BITXOR(), $FILTER(), $FIND(), $INSERTSTRING(), $LEFTSTRING(), $RANDSTRING(), $REPLACE(), $SEARCH(), $SUBSTITUTE(), $SUBSTRING(), $TRANSLATE()
|
| |
ROUND( formula, digits ) -- round a number |
| |
- Example:
%CALCULATE{$SUBSTITUTE(Q2-2012, 2, 3)}% returns Q3-3013
- Example:
%CALCULATE{$SUBSTITUTE(Q2-2012,2, 3, 3)}% returns Q2-2013
- Example:
%CALCULATE{$SUBSTITUTE(abc123def, [0-9], 9, , r)}% returns abc999def
|
|
<
< | |
>
> |
- Related:
$BITXOR(), $FILTER(), $HEXDECODE(), $HEXENCODE(), $INSERTSTRING(), $LEFTSTRING(), $RANDSTRING(), $REPLACE(), $RIGHTSTRING(), $SUBSTRING(), $TRANSLATE()
|
| |
SUBSTRING( text, start, num ) -- extract a substring out of a text string
- Extract
num number of characters of text string text, starting at start. Starting position is 1; use a negative start to count from the end of the text. All parameters are required - the text may contain commas.
- Syntax:
$SUBSTRING( text, start, num )
- Example:
%CALCULATE{$SUBSTRING(abcdefghijk, 3, 5)}% returns cdefg
|
|
<
< | |
>
> | |
| |
SUM( list ) -- sum of a list or range of cells |
| |
|
|
<
< |
| Plugin Version: |
2014-09-23 |
|
>
> |
| Plugin Version: |
2014-10-23 |
|
| | <--/twistyPlugin twikiMakeVisibleInline--> |
|
>
> |
| 2014-10-23: |
TWikibug:Item7583 : Add RANDSTRING(); better layout in interactive example |
|
| |
| 2014-09-23: |
TWikibug:Item7553: Allow newlines and indent around functions and function parameters |
| 2014-09-22: |
TWikibug:Item7552: Allow newlines in triple-quoted strings |
| 2014-03-04: |
TWikibug:Item7445: Add FORMAT(CURRENY, ...) with support for currency symbol |
|
Copyright © 1999-2025 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.