TWiki Spreadsheet Plugin

Syntax Rules

• Built-in function are of format $FUNCNAME(parameter)
• Functions may be nested, e.g. %CALC{"$SUM( R2:C$COLUMN(0)..R$ROW(-1):C$COLUMN(0) )"}%

• Lists can refer to values and/or table cell ranges, e.g. %CALC{"$SUM( 3, 5, $T(R1:C7), R1:C11..R1:C15 )"}%
• Formulae can only reference cells in the current or preceeding row of the current table; they may not reference cells below the current table row
• Formulae can also be placed outside of tables; they can reference cells in the preceeding table

Built-in Functions

• Example: %CALC{"$DEF(R1:C1..R1:C3)"}%
• Related: $COUNTSTR(), $LISTIF(), $LIST()

EVAL( formula ) -- evaluate a simple mathematical formula

• Addition, substraction, multiplication, division and modulus of numbers are supported. Any nesting is permitted

• Syntax: $EXACT( text1, text2 )
• Example: %CALC{"$EXACT(foo, Foo)"}% returns 0
• Example: %CALC{"$EXACT(foo, $LOWER(Foo))"}% returns 1

EXEC( formula ) -- execute a spreadsheet formula

• 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

• Example: %CALC{"$FIND(f, fluffy)"}% returns 1
• Example: %CALC{"$FIND(f, fluffy, 2)"}% returns 4
• Example: %CALC{"$FIND(@, fluffy, 1)"}% returns 0

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

• Example: %CALC{"$IF($T(R1:C5) > 1000, Over Budget, OK)"}% returns Over Budget if value in R1:C5 is over 1000, OK if not
• Example: %CALC{"$IF($EXACT($T(R1:C2),), empty, $T(R1:C2))"}% returns the content of R1:C2 or empty if empty
• Example: %CALC{"$SET(val, $IF($T(R1:C2) == 0, zero, $T(R1:C2)))"}% sets a variable conditionally

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

• Example: %CALC{"$SUM($LEFT())"}% returns the sum of cells to the left of the current cell
• Related: $ABOVE(), $RIGHT()

LENGTH( text ) -- length of text in bytes

• Syntax: $LENGTH( text )

• Example: %CALC{"$LISTIF($item > 12, 14, 7, 25)"}% returns 14, 25
• Example: %CALC{"$LISTIF($NOT($EXACT($item,)), A, B, , E)"}% returns non-empty elements A, B, E
• Example: %CALC{"$LISTIF($index > 2, A, B, C, D)"}% returns C, D

LISTITEM( index, list ) -- get one element of a list

NOP( text ) -- no-operation

• Syntax: $NOP( text )

• Returns 0 if num is not zero, 1 if zero
• Syntax: $NOT( num )
• Example: %CALC{"$NOT(0)"}% returns 1

ODD( num ) -- test for odd number

PROPERSPACE( text ) -- properly space out WikiWords

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

• Replace num number of characters of text string text, starting at start, with new text new. Starting position is 1; use a negative start to count from the end of the text
• Syntax: $REPLACE( text, start, num, new )
• Example: %CALC{"$REPLACE(abcdefghijk,6,5,*)"}% returns abcde*k

RIGHT( ) -- address range of cells to the right of the current cell

• Example: %CALC{"$SUM($RIGHT())"}% returns the sum of cells to the right of the current cell
• Related: $ABOVE(), $LEFT()

ROUND( formula, digits ) -- round a number

• Evaluates a simple formula and rounds the result up or down to the number of digits if digits is positive; to the nearest integer if digits is missing; or to the left of the decimal point if digits is negative

• Example: %CALC{"$SEARCH([uy], fluffy)"}% returns 3
• Example: %CALC{"$SEARCH([uy], fluffy, 3)"}% returns 6
• Example: %CALC{"$SEARCH([abc], fluffy,)"}% returns 0

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

• Example: %CALC{"$SUBSTITUTE(Q2-2002,2,3)"}% returns Q3-3003
• Example: %CALC{"$SUBSTITUTE(Q2-2002,2,3,3)"}% returns Q2-2003
• Example: %CALC{"$SUBSTITUTE(abc123def,[0-9],9,,r)"}% returns abc999def

SUM( list ) -- sum of a list or range of cells

• Syntax: $TRANSLATE( text, from, to )
• Example: %CALC{"$TRANSLATE(boom,bm,cl)"}% returns cool
• Example: %CALC{"$TRANSLATE(one, two,$comma,;)"}% returns one; two

TIME( text ) -- convert a date string into a serialized date number

• Removes all spaces from text except for single spaces between words
• Syntax: $TRIM( text )
• Example: %CALC{"$TRIM( eat  spaces  )"}% returns eat spaces

UPPER( text ) -- upper case string of a text

FAQ

Can I use CALC in a formatted search?

Specifically, how can I output some conditional text in a FormattedSearch?

Plugin Settings

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

Plugin Info