Difference: SpreadSheetPlugin (30 vs. 31)

Revision 312016-05-30 - RodrigoReeves

Line: 1 to 1
	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 Can I use CALCULATE in a formatted search? What are hashes and what are they used for? How can I easily repeat a formula in a table? CALC in Included Topics Bug Tracking Example Plugin Settings Plugin Installation Instructions Plugin Info
Line: 298 to 298
	Example: `%CALCULATE{$FILTER(f, fluffy)}%` returns `luy` - filter out a character multiple times Example: `%CALCULATE{$FILTER(an Franc, San Francisco)}%` returns `Sisco` - cut a string Example: `%CALCULATE{$FILTER($sp, Cat and Mouse)}%` returns `CatandMouse` - remove all spaces
Changed:
< <	Example: `%CALCULATE{$FILTER([^0-9], Project-ID-1234)}%` returns `1234` - filter in digits, e.g. keep only digits
> >	Example: `%CALCULATE{$FILTER([^0-9], Main/Project-ID-1234)}%` returns `1234` - filter in digits, e.g. keep only digits
	Example: `%CALCULATE{$FILTER([^a-zA-Z0-9 ], Stupid mistake*%@^! Fixed)}%` returns `Stupid mistake Fixed` - keep only alphanumeric characters and spaces Example: `%CALCULATE{$FILTER([^a-zA-Z0-9], $PROPER(an EXELLENT idea.))}%` returns `AnExcellentIdea` - turn a string into a WikiWord topic name Related: `$FIND()`, `$INSERTSTRING()`, `$LEFTSTRING()`, `$PROPER()`, `$REPLACE()`, `$RIGHTSTRING()`, `$SEARCH()`, `$SUBSTITUTE()`, `$SUBSTRING()`, `$TRANSLATE()`, `$TRIM()`

View topic | History: r31 < r30 < r29 < r28 | More topic actions...

Copyright © 1999-2024 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback
Note: Please contribute updates to this topic on TWiki.org at TWiki:TWiki.SpreadSheetPlugin.