| |
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
- EQUAL( text1, text2 ) -- compare two text strings, case-insensitive
- EVAL( formula ) -- evaluate a simple mathematical formula
- EVEN( num ) -- test for even number
- EXACT( text1, text2 ) -- compare two text strings, case-sensitive
- 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
- NOTE( text ) -- note, return empty string
- 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
|
| | HASHREVERSE( name ) -- reverse a hash
- All keys of a hash become values, and vice versa. Hash keys are always unique, e.g. multiple identical values will be reduced to one key. This function returns no output.
- Syntax:
$HASHREVERSE( name )
|
|
<
< |
- Examples:
%CALCULATE{$LIST2HASH(age, Jane, 26, Tim, 27, Sam, 27)}%
%CALCULATE{$HASHREVERSE(age)}% reverses the hash and returns nothing
%CALCULATE{$HASH2LIST(age, $key: $value)}% returns 26: Jane, 27: Tim
|
>
> |
- Examples:
%CALCULATE{$LIST2HASH(age, Jane, 26, Tim, 27, Sam, 28)}%
%CALCULATE{$HASHREVERSE(age)}% reverses the hash and returns nothing
%CALCULATE{$HASH2LIST(age, $key: $value)}% returns 26: Jane, 27: Tim, 28: Sam
|
| |
|
| | LISTEACH( formula, list ) -- evaluate and update each element of a list
- Specify a formula that should be applied to list element;
$item indicates the element, $index the index of the list, starting at 1. If $item is omitted, the item is appended to the formula. This function returns no output.
- Syntax:
$LISTEACH( formula, list )
|
|
>
> |
- Deprecated syntax:
$LISTMAP( formula, list )
|
| |
- Example:
%CALCULATE{$LISTEACH($index: $EVAL(2 * $item), 3, 5, 7, 11)}% returns 1: 6, 2: 10, 3: 14, 4: 22
- Related:
$COUNTITEMS(), $COUNTSTR(), $LIST(), $LISTIF(), $LISTITEM(), $LISTNONEMPTY(), $LISTREVERSE(), $LISTSIZE(), $LISTSORT(), $LISTUNIQUE(), $SPLIT(), $SUM(), $WHILE()
|
| |
PRODUCT( list ) -- product of a list or range of cells |
|
>
> |
- Deprecated syntax:
$MULT( list )
|
| | |
| |
| Plugin Author: |
TWiki:Main.PeterThoeny |
|
|
<
< |
| Copyright: |
© 2001-2015 Peter Thoeny, TWiki.org
© 2008-2015 TWiki:TWiki.TWikiContributor
© 2015 Wave Systems Corp. |
|
>
> |
| Copyright: |
© 2001-2018 Peter Thoeny, TWiki.org
© 2008-2018 TWiki:TWiki.TWikiContributor
© 2015 Wave Systems Corp. |
|
| |
|
|
<
< |
| Plugin Version: |
2015-06-07 |
|
>
> |
| Plugin Version: |
2018-07-05 |
|
| | <--/twistyPlugin twikiMakeVisibleInline--> |
|
>
> |
| 2018-07-05: |
TWikibug:Item7841: Copyright update to 2018 |
| 2017-10-21: |
TWikibug:Item7826: Test case for HASHREVERSE now works with Perl's hash key randomization; TWikibug:Item7827: Workaround for SPLIT with unthreaded Perl |
| 2017-10-19: |
TWikibug:Item7640: Fix bug for unit tests issue: DEC2HEX and DEC2BIN create longer strings with 64bit Perl |
| 2016-07-05: |
TWikibug:Item7746: Fix bug LIST2HASH fails with empty list entries |
| 2016-05-10: |
TWikibug:Item7703: Document deprecated LISTMAP() and MULT() |
| 2016-01-08: |
TWikibug:Item7708: Copyright update to 2016 |
|
| |
| 2015-06-07: |
TWikibug:Item7656: Rename COMMENT() to NOTE() - if someone uses COMMENT we can restore it as an undocumented function |
| 2015-05-18: |
TWikibug:Item7656: Add COMMENT(), EQUAL() |
| 2015-02-24: |
TWikibug:Item7617: Add range check in SUBSTRING() |
|
Copyright © 1999-2024 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.