TWiki Spreadsheet Plugin

ABS( num ) -- absolute value of a number

• Syntax: $ABS( num )
• Example: %CALC{"$ABS(-12.5)"}% returns 12.5

AND( list ) -- logcial AND of a list

• Related: $COUNTSTR(), $LISTIF(), $LIST()

• Addition, substraction, multiplication, division and modulus of numbers are supported. Any nesting is permitted
• Syntax: $EVAL( formula )
• Example: %CALC{"$EVAL( (5 * 3) / 2 + 1.1 )"}% returns 8.6

EXACT( text1, text2 ) -- compare two text strings

• Example: %CALC{"$EXACT(foo, $LOWER(Foo))"}% returns 1
• Related: $IF(), $TRIM()

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.

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

• 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

• Example: %CALC{"$FORMAT(NUMBER, 1, 12345.67)"}% returns 12345.7
• Example: %CALC{"$FORMAT(PERCENT, 1, 0.1234567)"}% returns 12.3%
• Related: $ROUND()

• 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.
• Syntax: $GET( name )
• Example: %CALC{"$GET(my_total)"}% returns the value of the my_total variable

IF( condition, value if true, value if 0 ) -- return a value based on a condition

• Convert the content of a range of cells into a flat list, delimited by comma. Cells containing commas are merged into the list
• Syntax: $LIST( range )
• Example: %CALC{"$LIST($LEFT())"}% returns Apples, Lemons, Oranges, Kiwis assuming the cells to the left contain | Apples | Lemons, Oranges | Kiwis |

LISTIF( condition, list ) -- remove elements from a list that do not meet a condition

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

LISTMAP( formula, list ) -- evaluate and update each element of a list

• In the formula you can use $item to indicate the element; $index to show the index of the list, starting at 1

LISTSIZE( list ) -- number of elements in a list

• Syntax: $LISTSIZE( list )
• Example: %CALC{"$LISTSIZE(Apple, Orange, Apple, Kiwi)"}% returns 4

LISTSORT( list ) -- sort a list

MAX( list ) - biggest value of a list or range of cells

• Syntax: $MAX( list )
• Example: To find the biggest number to the left of the current cell, write: %CALC{"$MAX($LEFT())"}%

MEDIAN( list ) -- median of a list or range of cells

• Syntax: $MEDIAN( list )
• Example: %CALC{"$MEDIAN(3, 9, 4, 5)"}% returns 4.5

MIN( list ) -- smallest value of a list or range of cells

• Syntax: $MIN( list )
• Example: %CALC{"$MIN(15, 3, 28)"}% returns 3

MOD( num, divisor ) -- reminder after dividing num by divisor

• Syntax: $MOD( num, divisor )
• Example: %CALC{"$MOD(7, 3)"}% returns 1

NOP( text ) -- no-operation

• Example: %CALC{"$NOT(0)"}% returns 1
• Related: $AND(), $IF(), $OR()

OR( list ) -- logcial OR of a list

• Syntax: $OR( list )
• Example: %CALC{"$OR(1, 0, 1)"}% returns 1
• Related: $AND(), $IF(), $NOT()

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

• Syntax: $PRODUCT( list )

• Related: $LIST(), $PRODUCT(), $SUM(), $SUMPRODUCT()

• Capitalize letters that follow any character other than a letter; convert all other letters to lowercase letters
• Syntax: $PROPER( text )
• Example: %CALC{"PROPER(a small STEP)"}% returns A Small Step

PROPERSPACE( text ) -- properly space out WikiWords

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

• Related: $LOWER(), $PROPER(), $TRIM(), $UPPER()

SET( name, value ) -- set a variable for later use

• Syntax: $SET( name, value )
• Example: %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

SETM( name, formula ) -- update an existing variable based on a formula

• Returns -1 if num is negative, 0 if zero, or 1 if positive
• Syntax: $SIGN( num )
• Example: %CALC{"$SIGN(-12.5)"}% returns -1

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

SUMPRODUCT( list, list ) -- scalar product on ranges of cells

• Syntax: $SUMPRODUCT( list, list, list... )

• Related: $LIST(), $PRODUCT(), $SUM()

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

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

• Related: $FORMATTIME(), $FORMATGMTIME(), $TIME(), $TIMEDIFF(), $TODAY()

• Debug plugin: (See output in data/debug.txt)
  • Set DEBUG = 0

• • Set SKIPINCLUDE = 1

• Test if the "Total" in the first table in this topic is correct.

Plugin Info