"$AND(list)" | The logcial AND of a list. Example: %CALC{"$AND(1, 0, 1)"}% returns 0 |
"$AVERAGE(list)" | The average of the content of a range of cells. Example: To get the average of column 5 excluding the title row, write in the last row: %CALC{"$AVERAGE( R2:C5..R$ROW(-1):C5 )"}% | The average of a list or a range of cells. Example: To get the average of column 5 excluding the title row, write in the last row: %CALC{"$AVERAGE( R2:C5..R$ROW(-1):C5 )"}% |
"$CHAR(number)" |
"$IF(condition, value if true, value if 0)" | Returns one value if a condition is met, and another value if not. The condition can be a number (where 0 means condition not met), or two numbers with a comparison operator < (less than), <= (less than or equal), == (equal), != (not equal), >= (greater than or equal), > (greater than). Example: %CALC{"$IF( $T(R1:C5) > 1000, Over Budget, OK )"}% returns Over Budget if value in R1:C5 is over 1000, OK if not | Returns one value if a condition is met, and another value if not. The condition can be a number (where 0 means condition not met), or two numbers with a comparison operator < (less than), <= (less than or equal), == (equal), != (not equal), >= (greater than or equal), > (greater than). Examples:%BR% %CALC{"$IF( $T(R1:C5) > 1000, Over Budget, OK )"}% returns Over Budget if value in R1:C5 is over 1000, OK if not%BR% %CALC{"$IF( $EXACT($T(R1:C2),), empty, $T(R1:C2) )"}% returns the content of R1:C2 or empty if empty%BR% %CALC{"$SET(val, $IF( $T(R1:C2) == 0, zero, $T(R1:C2) ))"}% sets a variable conditionally (notice that you cannot set a variable conditionally inside an $IF() since formulae in the "value if true" and "value if 0" are both evaluated before the condition is applied) |
"$INT(formula)" |
"$MEDIAN(list)" | The median of a list or range of cells. Example: %CALC{"$MEDIAN( 3, 9, 4, 5 )"}% returns 4.5 | The median of a list or range of cells. Example: %CALC{"$MEDIAN(3, 9, 4, 5)"}% returns 4.5 |
"$MIN(list)" | The smallest value of a list or range of cells. Example: %CALC{"$MIN( 15, 3, 28 )"}% returns 3 | The smallest value of a list or range of cells. Example: %CALC{"$MIN(15, 3, 28)"}% returns 3 |
"$MULT(list)" | The product of a list or range of cells. Example: to calculate the product of the cells to the left of the current one use %CALC{"$MULT($LEFT())"}% | "$MOD(num, divisor)" | The reminder after dividing num by divisor . Example: %CALC{"$MOD(7, 3)"}% returns 1 |
"$NOP(text)" |
A no-operation. Allows one to defy the order of Plugin execution. For example, it will allow preprossing to be done before %SEARCH{}% is evaluated. Use $per to escape '%'. |
"$NOT(num)" | The reverse logic of a number. Returns 0 if num is not zero, 1 if zero. Example: %CALC{"$NOT(0)"}% returns 1 |
"$OR(list)" | The logcial OR of a list. Example: %CALC{"$OR(1, 0, 1)"}% returns 1 |
"$PRODUCT(list)" | The product of a list or range of cells. Example: to calculate the product of the cells to the left of the current one use %CALC{"$PRODUCT($LEFT())"}% |
"$PROPER(text)" | Capitalizes letters in text that follow any character other than a letter. Converts all other letters to lowercase letters. Examples:%BR% %CALC{"PROPER(a small STEP)"}% returns A Small Step %BR% %CALC{"PROPER(f1 (formula-1))"}% returns F1 (Formula 1) |
"$PROPERSPACE(text)" | Properly spaces out [[TWiki/WikiWords]] preceeded by white space, parenthesis, or ][ . Words listed in the DONTSPACE [[TWiki/TWikiPreferences]] variable or DONTSPACE Plugins setting are excluded. Example, assuming DONTSPACE contains McIntosh: %CALC{"PROPERSPACE(McIntosh likes WikiWord links like WebHome and [[WebHome][WebHome]])"}% returns McIntosh likes Wiki Word links like Web Home and [[Main/WebHome]] |
"$RAND(max)" | Random number, evenly distributed between 0 and max , or 0 and 1 if max is not specified. |
"$REPEAT(text)" | Repeat text a number of times. Example: %CALC{"$REPEAT(Hi! , 3)"}% returns Hi! Hi! Hi! |
"$REPLACE(text, start_num, num_chars, new_text)" |
Replaces part of text string text , based on the starting position start_num , and the number of characters to replace num_chars . The characters are replaced with new_text . Starting position is 1; use a negative start_num to count from the end of the text. See also $SUBSTITUTE() , $TRANSLATE() . Example: %BR% %CALC{"$REPLACE(abcdefghijk,6,5,*)"}% returns abcde*k |
"$ROUND(formula)" | Evaluates a simple formula and rounds the result up or down to the nearest integer. Example: %CALC{"$INT( 10 / 6 )"}% returns 2 | "$ROUND(formula, digits)" | 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. Examples: %BR% %CALC{"$ROUND(3.15, 1)"}% returns 3.2 %BR% %CALC{"$ROUND(3.149, 1)"}% returns 3.1 %BR% %CALC{"$ROUND(-2.475, 2)"}% returns -2.48 %BR% %CALC{"$ROUND(34.9, -1)"}% returns 30 |
"$ROW(offset)" |
"$SIGN(num)" | The sign of a number. Returns -1 if num is negative, 0 if zero, or 1 if positive. Example: %CALC{"$SIGN(-12.5)"}% returns -1 |
"$SUBSTITUTE(text, old, new, instance, option)" |
Substitutes new text for old text in a text string. instance specifies which occurance of old you want to replace. If you specify instance , only that instance is replaced. Otherwise, every occurance is changed to the new text. A literal search is performed by default; a [[TWiki/RegularExpression]] search if the option is set to r . See also $REPLACE() , $TRANSLATE() . Examples: %BR% %CALC{"$SUBSTITUTE(Good morning, morning, day)"}% returns Good day %BR% %CALC{"$SUBSTITUTE(Q2-2002,2,3)"}% returns Q3-3003 %BR% %CALC{"$SUBSTITUTE(Q2-2002,2,3,3)"}% returns Q2-2003 %BR% %CALC{"$SUBSTITUTE(abc123def,[0-9],9,,r)"}% returns abc999def |
The upper case string of a text. Example: %CALC{"$UPPER( $T(R1:C5) )"}% returns the upper case string of the text in cell R1:C5 |
"$VALUE(text)" | Extracts a number from text . Returns 0 if not found. Examples:%BR% %CALC{"$VALUE(US$1,200)"}% returns 1200 %BR% %CALC{"$VALUE(PrjNotebook1234)"}% returns 1234 %BR% %CALC{"$VALUE(Total: -12.5)"}% returns 12.5 |