| | TWiki Spreadsheet Plugin
This Plugin adds speadsheet capabilities to TWiki topics. Formulas like %CALC{"$INT(7/3)"}% are evaluated at page view time. They can be placed in table cells and outside of tables. |
| |
"$INT(formula)" |
Evaluates a simple formula and rounds the result down to the nearest integer. Example: %CALC{"$INT( 10 / 4 )"}% returns 2 |
"$LEFT()" |
The address range of cells to the left of the current cell |
"$LENGTH(text)" |
The length in bytes of text. Example: %CALC{"$LENGTH(abcd)"}% returns 4 |
|
|
>
> |
"$LIST(range)" |
Converts the content of a range of cells into a flat list, delimited by comma. Example: %CALC{"$LIST( $LEFT() )"}% returns Apples, Lemons, Oranges, Kiwis assuming the cells to the left contain | Apples | Lemons, Oranges | Kiwis | |
|
| |
"$LOWER(text)" |
The lower case string of a text. Example: %CALC{"$LOWER( $T(R1:C5) )"}% returns the lower case string of the text in cell R1:C5 |
"$MAX(list)" |
The biggest value of a list or range of cells. Example: To find the biggest number to the left of the current cell, write: %CALC{"$MAX( $LEFT() )"}% |
"$MEDIAN(list)" |
The median of a list or range of cells. Example: %CALC{"$MEDIAN(3, 9, 4, 5)"}% returns 4.5 |
|
| |
"$PROPER(text)" |
Capitalizes letters in text that follow any character other than a letter. Converts all other letters to lowercase letters. Examples:
%CALC{"PROPER(a small STEP)"}% returns A Small Step
%CALC{"PROPER(f1 (formula-1))"}% returns F1 (Formula 1) |
"$PROPERSPACE(text)" |
Properly spaces out WikiWords preceeded by white space, parenthesis, or ][. Words listed in the DONTSPACE 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 Web Home |
"$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! |
|
>
> |
"$REPEAT(text)" |
Repeat text a number of times. Example: %CALC{"$REPEAT(/\, 5)"}% returns /\/\/\/\/\ |
|
| |
"$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:
%CALC{"$REPLACE(abcdefghijk,6,5,*)"}% returns abcde*k |
"$RIGHT()" |
The address range of cells to the right of the current cell |
"$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:
%CALC{"$ROUND(3.15, 1)"}% returns 3.2
%CALC{"$ROUND(3.149, 1)"}% returns 3.1
%CALC{"$ROUND(-2.475, 2)"}% returns -2.48
%CALC{"$ROUND(34.9, -1)"}% returns 30 |
|
| |
"$TODAY()" |
Get the serialized date of today at midnight GMT. The related $TIME() returns the serialized date of today at the current time, e.g. it includes the number of seconds since midnight GMT. See also $FORMATTIME(), $FORMATGMTIME(), $TIMEDIFF(). Example: %CALC{"$TODAY()"}% returns the number of seconds since Epoch |
"$TRIM(text)" |
Removes all spaces from text except for single spaces between words. Example: %CALC{"$TRIM( eat spaces )"}% returns eat spaces. |
"$UPPER(text)" |
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:
%CALC{"$VALUE(US$1,200)"}% returns 1200
%CALC{"$VALUE(PrjNotebook1234)"}% returns 1234
%CALC{"$VALUE(Total: -12.5)"}% returns 12.5 |
|
>
> |
"$VALUE(text)" |
Extracts a number from text. Returns 0 if not found. Examples:
%CALC{"$VALUE(US$1,200)"}% returns 1200
%CALC{"$VALUE(PrjNotebook1234)"}% returns 1234
%CALC{"$VALUE(Total: -12.5)"}% returns -12.5 |
|
| |
Bug Tracking Example |
| | Plugin Info
|
|
<
< |
| Plugin Version: |
06 Mar 2004 |
|
>
> |
| Plugin Version: |
08 Mar 2004 |
|
| |
| Change History: |
<-- specify latest version first --> |
|
|
>
> |
| 08 Mar 2004: |
Added $LIST() |
|
| |
| 06 Mar 2004: |
Added $AND(), $MOD(), $NOT(), $OR(), $PRODUCT(), $PROPER(), $PROPERSPACE(), $RAND(), $REPEAT(), $SIGN(), $VALUE(); added digits parameter to $ROUND(); renamed $MULT() to $PRODUCT(); $MULT() is deprecated and undocumented |
| 27 Feb 2004: |
Added $COUNTUNIQUE() |
| 24 Oct 2003: |
Added $SET(), $GET(), $MEDIAN(); added $SUMPRODUCT(), inspired by TWiki:Main/RobertWithrow; added $SUMDAYS(), contributed by TWiki:Main/SvenDowideit |
|
| |
Related Topics: TWikiPreferences, TWikiPlugins |
|
<
< | -- TWiki:Main/PeterThoeny - 06 Mar 2004 |
>
> | -- TWiki:Main/PeterThoeny - 08 Mar 2004 |
Copyright � by the contributing authors. All material on this collaboration platform is the property of the contributing authors.