Home | Gifts

SpreadSheetPlugin Testing

Use this topic to verify proper operation of the SpreadSheetPlugin in your environment.

For developers: This topic is included by TestCaseAutoSpreadSheetPlugin for automated integration testing. This requires the TWiki:Plugins.TestFixturePlugin and a repository checkout.

CALC{} and CALCULATE{}

Expected

  • CALC{$REPLACE(1234, 2, 1, X)}=|1X34|
  • CALCULATE{$REPLACE(1234, 2, 1, X)}=|1X34|

Actual

  • CALC{$REPLACE(1234, 2, 1, X)}=|1X34|
  • CALCULATE{$REPLACE(1234, 2, 1, X)}=|1X34|

NOTE: All functions that follow are tested via:

Function ABOVE

Expected

A1 B1
A2 B2
$ABOVE()=R0:C1..R2:C1 $ABOVE()=R0:C2..R2:C2

Actual

A1 B1
A2 B2
$ABOVE()=R0:C1..R2:C1 $ABOVE()=R0:C2..R2:C2

Function ABS

Expected

  • $ABS(-2)=|2|
  • $ABS(-0.5)=|0.5|
  • $ABS(0)=|0|
  • $ABS(0.5)=|0.5|
  • $ABS(2)=|2|

Actual

  • $ABS(-2)=|2|
  • $ABS(-0.5)=|0.5|
  • $ABS(0)=|0|
  • $ABS(0.5)=|0.5|
  • $ABS(2)=|2|

Function AND

Expected

  • $AND()=|0|
  • $AND(0)=|0|
  • $AND(1)=|1|
  • $AND(0, 0)=|0|
  • $AND(0, 1)=|0|
  • $AND(1, 0)=|0|
  • $AND(1, 1)=|1|
  • $AND(0, 1, 2, 3)=|0|
  • $AND(1, 2, 3, 4)=|1|

Actual

  • $AND()=|0|
  • $AND(0)=|0|
  • $AND(1)=|1|
  • $AND(0, 0)=|0|
  • $AND(0, 1)=|0|
  • $AND(1, 0)=|0|
  • $AND(1, 1)=|1|
  • $AND(0, 1, 2, 3)=|0|
  • $AND(1, 2, 3, 4)=|1|

Function AVERAGE

Expected

  • $AVERAGE()=|0|
  • $AVERAGE(x)=|0|
  • $AVERAGE(0)=|0|
  • $AVERAGE(0, 1)=|0.5|
  • $AVERAGE(0, 1, 2)=|1|
  • $AVERAGE(1.5, 2, 2.5)=|2|
  • $AVERAGE(-1.5, 2, 2.5)=|1|

Actual

  • $AVERAGE()=|0|
  • $AVERAGE(x)=|0|
  • $AVERAGE(0)=|0|
  • $AVERAGE(0, 1)=|0.5|
  • $AVERAGE(0, 1, 2)=|1|
  • $AVERAGE(1.5, 2, 2.5)=|2|
  • $AVERAGE(-1.5, 2, 2.5)=|1|

Function BITXOR

Expected

  • $BITXOR()=||
  • $BITXOR(A123)=|¾ÎÍÌ|
  • $BITXOR($BITXOR(anything))=|anything|

Actual

  • $BITXOR()=||
  • $BITXOR(A123)=|¾ÎÍÌ|
  • $BITXOR($BITXOR(anything))=|anything|

Function CEILING

Expected

  • $CEILING(5.4)=|6|
  • $CEILING(-5.4)=|-5|

Actual

  • $CEILING(5.4)=|6|
  • $CEILING(-5.4)=|-5|

Function CHAR

Expected

  • $CHAR(97)=|a|

Actual

  • $CHAR(97)=|a|

Function CODE

Expected

  • $CODE(abc)=|97|

Actual

  • $CODE(abc)=|97|

Function COLUMN

Expected

$COLUMN()=1 $COLUMN()=2
$COLUMN()=1 $COLUMN()=2
$COLUMN()=1 $COLUMN()=2

Actual

$COLUMN()=1 $COLUMN()=2
$COLUMN()=1 $COLUMN()=2
$COLUMN()=1 $COLUMN()=2

Function COUNTITEMS

Expected

  • $COUNTITEMS(One)=|One: 1|
  • $COUNTITEMS(One, Two)=|One: 1
    Two: 1|
  • $COUNTITEMS(One, Two, One)=|One: 2
    Two: 1|

Actual

  • $COUNTITEMS(One)=|One: 1|
  • $COUNTITEMS(One, Two)=|One: 1
    Two: 1|
  • $COUNTITEMS(One, Two, One)=|One: 2
    Two: 1|

Function COUNTSTR

Expected

  • $COUNTSTR(Done, , Done, OK, )=|3|
  • $COUNTSTR(Done, , Done, OK, Done)=|2|

Actual

  • $COUNTSTR(Done, , Done, OK, )=|3|
  • $COUNTSTR(Done, , Done, OK, Done)=|2|

Function DEF

Expected

  • $DEF(One, Two, Three)=|One|
  • $DEF(, Two, Three)=|Two|
  • $DEF(, , Three)=|Three|
  • $DEF(, , )=||

Actual

  • $DEF(One, Two, Three)=|One|
  • $DEF(, Two, Three)=|Two|
  • $DEF(, , Three)=|Three|
  • $DEF(, , )=||

Function EMPTY

Expected

  • $EMPTY()=|1|
  • $EMPTY( )=|0|
  • $EMPTY(foo)=|0|

Actual

  • $EMPTY()=|1|
  • $EMPTY( )=|0|
  • $EMPTY(foo)=|0|

Function EVAL

Expected

  • $EVAL( (5 * 3) / 2 + 1.1 )=|8.6|

Actual

  • $EVAL( (5 * 3) / 2 + 1.1 )=|8.6|

Function EVEN

Expected

  • $EVEN()=|1|
  • $EVEN(-1)=|0|
  • $EVEN(0)=|1|
  • $EVEN(1)=|0|
  • $EVEN(2)=|1|
  • $EVEN(3.4)=|0|
  • $EVEN(4.4)=|1|
  • $EVEN(4.6)=|1|

Actual

  • $EVEN()=|1|
  • $EVEN(-1)=|0|
  • $EVEN(0)=|1|
  • $EVEN(1)=|0|
  • $EVEN(2)=|1|
  • $EVEN(3.4)=|0|
  • $EVEN(4.4)=|1|
  • $EVEN(4.6)=|1|

Function EXACT

Expected

  • $EXACT(foo, Foo)=|0|
  • $EXACT(foo, $LOWER(Foo))=|1|

Actual

  • $EXACT(foo, Foo)=|0|
  • $EXACT(foo, $LOWER(Foo))=|1|

Function EXEC

Expected

  • $SET(msg, $NOEXEC(Hi $GET(name)))=||
  • $EXEC($SET(name, Tom) $EXEC($GET(msg)))=| Hi Tom|
  • $EXEC($SET(name, Jerry) $EXEC($GET(msg)))=| Hi Jerry|

Actual

  • $SET(msg, $NOEXEC(Hi $GET(name)))=||
  • $EXEC($SET(name, Tom) $EXEC($GET(msg)))=| Hi Tom|
  • $EXEC($SET(name, Jerry) $EXEC($GET(msg)))=| Hi Jerry|

Function EXISTS

Expected

  • $EXISTS(WebHome)=|1|
  • $EXISTS(ThisDoesNotExist)=|0|

Actual

  • $EXISTS(WebHome)=|1|
  • $EXISTS(ThisDoesNotExist)=|0|

Function EXP

Expected

  • $EXP(1)=|2.71828182845905|

Actual

  • $EXP(1)=|2.71828182845905|

Function FILTER

Expected

  • $FILTER(f, fluffy)=|luy|
  • $FILTER(an Franc, San Francisco)=|Sisco|
  • $FILTER($sp, Cat and Mouse)=|CatandMouse|
  • $FILTER([^0-9], Project-ID-1234)=|1234|
  • $FILTER([^a-zA-Z0-9 ], Stupid mistake*%@^! Fixed)=|Stupid mistake Fixed|

Actual

  • $FILTER(f, fluffy)=|luy|
  • $FILTER(an Franc, San Francisco)=|Sisco|
  • $FILTER($sp, Cat and Mouse)=|CatandMouse|
  • $FILTER([^0-9], Project-ID-1234)=|1234|
  • $FILTER([^a-zA-Z0-9 ], Stupid mistake*%@^! Fixed)=|Stupid mistake Fixed|

Function FIND

Expected

  • $FIND(f, fluffy)=|1|
  • $FIND(f, fluffy, 2)=|4|
  • $FIND(x, fluffy, 1)=|0|

Actual

  • $FIND(f, fluffy)=|1|
  • $FIND(f, fluffy, 2)=|4|
  • $FIND(x, fluffy, 1)=|0|

Function FLOOR

Expected

  • $FLOOR(5.4)=|5|
  • $FLOOR(-5.4)=|-6|

Actual

  • $FLOOR(5.4)=|5|
  • $FLOOR(-5.4)=|-6|

Function FORMAT

Expected

  • $FORMAT(COMMA, 2, 12345.6789)=|12,345.68|
  • $FORMAT(DOLLAR, 2, 12345.6789)=|$12,345.68|
  • $FORMAT(KB, 2, 1234567)=|1205.63 KB|
  • $FORMAT(MB, 2, 1234567)=|1.18 MB|
  • $FORMAT(KBMB, 2, 1234567)=|1.18 MB|
  • $FORMAT(KBMB, 2, 1234567890)=|1.15 GB|
  • $FORMAT(NUMBER, 1, 12345.67)=|12345.7|
  • $FORMAT(PERCENT, 1, 0.1234567)=|12.3%|

Actual

  • $FORMAT(COMMA, 2, 12345.6789)=|12,345.68|
  • $FORMAT(DOLLAR, 2, 12345.6789)=|$12,345.68|
  • $FORMAT(KB, 2, 1234567)=|1205.63 KB|
  • $FORMAT(MB, 2, 1234567)=|1.18 MB|
  • $FORMAT(KBMB, 2, 1234567)=|1.18 MB|
  • $FORMAT(KBMB, 2, 1234567890)=|1.15 GB|
  • $FORMAT(NUMBER, 1, 12345.67)=|12345.7|
  • $FORMAT(PERCENT, 1, 0.1234567)=|12.3%|

Function FORMATGMTIME

Expected

  • $FORMATGMTIME(1041379200, $day $mon $year)=|01 Jan 2003|

Actual

  • $FORMATGMTIME(1041379200, $day $mon $year)=|01 Jan 2003|

Function FORMATTIME

Expected

  • $FORMATTIME(0, $year/$month/$day GMT)=|1970/01/01 GMT|

Actual

  • $FORMATTIME(0, $year/$month/$day GMT)=|1970/01/01 GMT|

Function FORMATTIMEDIFF

Expected

  • $FORMATTIMEDIFF(min, 1, 200)=|3 hours|
  • $FORMATTIMEDIFF(min, 2, 200)=|3 hours and 20 minutes|
  • $FORMATTIMEDIFF(min, 1, 1640)=|1 day|
  • $FORMATTIMEDIFF(min, 2, 1640)=|1 day and 3 hours|
  • $FORMATTIMEDIFF(min, 3, 1640)=|1 day, 3 hours and 20 minutes|

Actual

  • $FORMATTIMEDIFF(min, 1, 200)=|3 hours|
  • $FORMATTIMEDIFF(min, 2, 200)=|3 hours and 20 minutes|
  • $FORMATTIMEDIFF(min, 1, 1640)=|1 day|
  • $FORMATTIMEDIFF(min, 2, 1640)=|1 day and 3 hours|
  • $FORMATTIMEDIFF(min, 3, 1640)=|1 day, 3 hours and 20 minutes|

Function GET

Expected

  • $SET(test, 1234)=||
  • $GET(test)=|1234|
  • $GET()=||

Actual

  • $SET(test, 1234)=||
  • $GET(test)=|1234|
  • $GET()=||

Function HEXDECODE

Expected

  • $HEXDECODE(687474703A2F2F7477696B692E6F72672F)=|http://twiki.org/|

Actual

  • $HEXDECODE(687474703A2F2F7477696B692E6F72672F)=|http://twiki.org/|

Function HEXENCODE

Expected

  • $HEXENCODE(http://twiki.org/)=|687474703A2F2F7477696B692E6F72672F|

Actual

  • $HEXENCODE(http://twiki.org/)=|687474703A2F2F7477696B692E6F72672F|

Function IF

Expected

  • $SET(test_number, 123)=||
  • $IF($GET(test_number)>100, greater)=|greater|
  • $SET(test_string, San Francisco)=||
  • $IF($EXACT($GET(test_string), Cupertino), equal, not equal)=|not equal|
  • $SET(result, $IF($GET(test_number)==0, zero, $GET(test_number)))=||
  • $GET(result)=|123|

Actual

  • $SET(test_number, 123)=||
  • $IF($GET(test_number)>100, greater)=|greater|
  • $SET(test_string, San Francisco)=||
  • $IF($EXACT($GET(test_string), Cupertino), equal, not equal)=|not equal|
  • $SET(result, $IF($GET(test_number)==0, zero, $GET(test_number)))=||
  • $GET(result)=|123|

Function INSERTSTRING

Expected

  • $INSERTSTRING(abcdefg, 2, XYZ)=|abXYZcdefg|
  • $INSERTSTRING(abcdefg, -2, XYZ)=|abcdeXYZfg|

Actual

  • $INSERTSTRING(abcdefg, 2, XYZ)=|abXYZcdefg|
  • $INSERTSTRING(abcdefg, -2, XYZ)=|abcdeXYZfg|

Function INT

Expected

  • $INT(10 / 4)=|2|
  • $INT($VALUE(09))=|9|

Actual

  • $INT(10 / 4)=|2|
  • $INT($VALUE(09))=|9|

Function ISDIGIT

Expected

  • $ISDIGIT(123)=|1|
  • $ISDIGIT(-7)=|0|
  • $ISDIGIT(abc123)=|0|
  • $ISDIGIT()=|0|

Actual

  • $ISDIGIT(123)=|1|
  • $ISDIGIT(-7)=|0|
  • $ISDIGIT(abc123)=|0|
  • $ISDIGIT()=|0|

Function ISLOWER

Expected

  • $ISLOWER(apple)=|1|
  • $ISLOWER(apple tree)=|0|
  • $ISLOWER(ORANGE)=|0|

Actual

  • $ISLOWER(apple)=|1|
  • $ISLOWER(apple tree)=|0|
  • $ISLOWER(ORANGE)=|0|

Function ISUPPER

Expected

  • $ISUPPER(apple)=|0|
  • $ISUPPER(ORANGE)=|1|
  • $ISUPPER(ORANGE GARDEN)=|0|

Actual

  • $ISUPPER(apple)=|0|
  • $ISUPPER(ORANGE)=|1|
  • $ISUPPER(ORANGE GARDEN)=|0|

Function ISWIKIWORD

Expected

  • $ISWIKIWORD(GoldenGate)=|1|
  • $ISWIKIWORD(whiteRafting)=|0|
  • $ISWIKIWORD()=|0|

Actual

  • $ISWIKIWORD(GoldenGate)=|1|
  • $ISWIKIWORD(whiteRafting)=|0|
  • $ISWIKIWORD()=|0|

Function LEFT

Expected

1 2 $SUM($LEFT())=|3|
3 4 $SUM($LEFT())=|7|

Actual

1 2 $SUM($LEFT())=|3|
3 4 $SUM($LEFT())=|7|

Function LEFTSTRING

Expected

  • $LEFTSTRING(abcdefg)=|a|
  • $LEFTSTRING(abcdefg, 5)=|abcde|
  • $LEFTSTRING()=||

Actual

  • $LEFTSTRING(abcdefg)=|a|
  • $LEFTSTRING(abcdefg, 5)=|abcde|
  • $LEFTSTRING()=||

Function LENGTH

Expected

  • $LENGTH(abcd)=|4|
  • $LENGTH()=|0|

Actual

  • $LENGTH(abcd)=|4|
  • $LENGTH()=|0|

Function LIST

Expected

Apple Banana Citrus $LIST($LEFT())=|Apple, Banana, Citrus|

Actual

Apple Banana Citrus $LIST($LEFT())=|Apple, Banana, Citrus|

Function LISTIF

Expected

  • $LISTIF($item > 12, 14, 7, 25)=|14, 25|
  • $LISTIF($NOT($EXACT($item,)), A, B, , E)=|A, B, E|
  • $LISTIF($index > 2, A, B, C, D)=|C, D|

Actual

  • $LISTIF($item > 12, 14, 7, 25)=|14, 25|
  • $LISTIF($NOT($EXACT($item,)), A, B, , E)=|A, B, E|
  • $LISTIF($index > 2, A, B, C, D)=|C, D|

Function LISTITEM

Expected

  • $LISTITEM(2, Apple, Orange, Apple, Kiwi)=|Orange|
  • $LISTITEM(-1, Apple, Orange, Apple, Kiwi)=|Kiwi|

Actual

  • $LISTITEM(2, Apple, Orange, Apple, Kiwi)=|Orange|
  • $LISTITEM(-1, Apple, Orange, Apple, Kiwi)=|Kiwi|

Function LISTJOIN

Expected

  • $LISTJOIN(-, Apple, Orange, Apple, Kiwi)=|Apple-Orange-Apple-Kiwi|
  • $LISTJOIN($empty, Apple, Orange, Apple, Kiwi)=|AppleOrangeAppleKiwi|
$LISTJOIN($n, Apple, Orange, Apple, Kiwi)=|Apple Orange Apple Kiwi|

Actual

  • $LISTJOIN(-, Apple, Orange, Apple, Kiwi)=|Apple-Orange-Apple-Kiwi|
  • $LISTJOIN($empty, Apple, Orange, Apple, Kiwi)=|AppleOrangeAppleKiwi|
$LISTJOIN($n, Apple, Orange, Apple, Kiwi)=|Apple Orange Apple Kiwi|

Function LISTMAP

Expected

  • $LISTMAP($index: $EVAL(2 * $item), 3, 5, 7, 11)=|1: 6, 2: 10, 3: 14, 4: 22|

Actual

  • $LISTMAP($index: $EVAL(2 * $item), 3, 5, 7, 11)=|1: 6, 2: 10, 3: 14, 4: 22|

Function LISTNONEMPTY

Expected

  • $LISTNONEMPTY(, Apple, Orange, , Kiwi)=|Apple, Orange, Kiwi|

Actual

  • $LISTNONEMPTY(, Apple, Orange, , Kiwi)=|Apple, Orange, Kiwi|

Function LISTRAND

Expected

  • $LISTRAND(Apple, Apple, Apple)=|Apple|
  • Manual test: $LISTRAND(Apple, Orange, Apple, Kiwi)=|(one of the four)|

Actual

  • $LISTRAND(Apple, Apple, Apple)=|Apple|
  • Manual test: $LISTRAND(Apple, Orange, Apple, Kiwi)=|Apple|

Function LISTREVERSE

Expected

  • $LISTREVERSE(Apple, Orange, Apple, Kiwi)=|Kiwi, Apple, Orange, Apple|

Actual

  • $LISTREVERSE(Apple, Orange, Apple, Kiwi)=|Kiwi, Apple, Orange, Apple|

Function LISTSHUFFLE

Expected

  • $LISTSHUFFLE(Apple, Apple, Apple)=|Apple, Apple, Apple|
  • Manual test: $LISTSHUFFLE(Apple, Orange, Apple, Kiwi)=|(4 shuffled items)|

Actual

  • $LISTSHUFFLE(Apple, Apple, Apple)=|Apple, Apple, Apple|
  • Manual test: $LISTSHUFFLE(Apple, Orange, Apple, Kiwi)=|Kiwi, Apple, Orange, Apple|

Function LISTSIZE

Expected

  • $LISTSIZE(Apple, Orange, Apple, Kiwi)=|4|
  • $LISTSIZE()=|0|

Actual

  • $LISTSIZE(Apple, Orange, Apple, Kiwi)=|4|
  • $LISTSIZE()=|0|

Function LISTSORT

Expected

  • $LISTSORT(Apple, Orange, Apple, Kiwi)=|Apple, Apple, Kiwi, Orange|

Actual

  • $LISTSORT(Apple, Orange, Apple, Kiwi)=|Apple, Apple, Kiwi, Orange|

Function LISTTRUNCATE

Expected

  • $LISTTRUNCATE(2, Apple, Orange, Apple, Kiwi)=|Apple, Orange|

Actual

  • $LISTTRUNCATE(2, Apple, Orange, Apple, Kiwi)=|Apple, Orange|

Function LISTUNIQUE

Expected

  • $LISTUNIQUE(Apple, Orange, Apple, Kiwi)=|Apple, Orange, Kiwi|

Actual

  • $LISTUNIQUE(Apple, Orange, Apple, Kiwi)=|Apple, Orange, Kiwi|

Function LN

Expected

  • $LN(10)=|2.30258509299405|

Actual

  • $LN(10)=|2.30258509299405|

Function LOG

Expected

  • $LOG(1000)=|3|
  • $LOG(16, 2)=|4|

Actual

  • $LOG(1000)=|3|
  • $LOG(16, 2)=|4|

Function LOWER

Expected

  • $LOWER(this BECOMES a lower cASE String)=|this becomes a lower case string|

Actual

  • $LOWER(this BECOMES a lower cASE String)=|this becomes a lower case string|

Function MAX

Expected

  • $MAX(7, 99, 2, 5)=|99|
  • $MAX(A, 99, 2, 5)=|99|
  • $MAX(A, B)=||
  • $MAX()=||

Actual

  • $MAX(7, 99, 2, 5)=|99|
  • $MAX(A, 99, 2, 5)=|99|
  • $MAX(A, B)=||
  • $MAX()=||

Function MEDIAN

Expected

  • $MEDIAN(3, 9, 4, 5)=|4.5|

Actual

  • $MEDIAN(3, 9, 4, 5)=|4.5|

Function MIN

Expected

  • $MIN(7, 99, 2, 5)=|2|
  • $MIN(A, 99, 2, 5)=|2|
  • $MIN(A, B)=||
  • $MIN()=||

Actual

  • $MIN(7, 99, 2, 5)=|2|
  • $MIN(A, 99, 2, 5)=|2|
  • $MIN(A, B)=||
  • $MIN()=||

Function MOD

Expected

  • $MOD(7, 3)=|1|
  • $MOD(7)=|0|
  • $MOD()=|0|

Actual

  • $MOD(7, 3)=|1|
  • $MOD(7)=|0|
  • $MOD()=|0|

Function NOEXEC

Expected

  • $SET(msg, $NOEXEC(Hi $GET(name)))=||
  • $SET(name, Jane)$EXEC($GET(msg))=|Hi Jane|

Actual

  • $SET(msg, $NOEXEC(Hi $GET(name)))=||
  • $SET(name, Jane)$EXEC($GET(msg))=|Hi Jane|

Function NOP

Expected

  • $NOP(100$percnt $quotquoted$quot)=|100% "quoted"|
  • $NOP()=||

Actual

  • $NOP(100$percnt $quotquoted$quot)=|100% "quoted"|
  • $NOP()=||

Function NOT

Expected

  • $NOT(1)=|0|
  • $NOT(0)=|1|
  • $NOT(1234)=|0|
  • $NOT()=|1|

Actual

  • $NOT(1)=|0|
  • $NOT(0)=|1|
  • $NOT(1234)=|0|
  • $NOT()=|1|

Function ODD

Expected

  • $ODD(2)=|0|
  • $ODD(3)=|1|
  • $ODD(3.5)=|1|
  • $ODD(-4)=|0|
  • $ODD()=|0|

Actual

  • $ODD(2)=|0|
  • $ODD(3)=|1|
  • $ODD(3.5)=|1|
  • $ODD(-4)=|0|
  • $ODD()=|0|

Function OR

Expected

  • $OR()=|0|
  • $OR(0)=|0|
  • $OR(1)=|1|
  • $OR(0, 0)=|0|
  • $OR(0, 1)=|1|
  • $OR(1, 0)=|1|
  • $OR(1, 1)=|1|
  • $OR(0, 1, 2, 3)=|1|
  • $OR(1, 2, 3, 4)=|1|

Actual

  • $OR()=|0|
  • $OR(0)=|0|
  • $OR(1)=|1|
  • $OR(0, 0)=|0|
  • $OR(0, 1)=|1|
  • $OR(1, 0)=|1|
  • $OR(1, 1)=|1|
  • $OR(0, 1, 2, 3)=|1|
  • $OR(1, 2, 3, 4)=|1|

Function PERCENTILE

Expected

  • $PERCENTILE(75, 400, 200, 500, 100, 300)=|450|
  • $PERCENTILE(60)=|0|
  • $PERCENTILE()=|0|

Actual

  • $PERCENTILE(75, 400, 200, 500, 100, 300)=|450|
  • $PERCENTILE(60)=|0|
  • $PERCENTILE()=|0|

Function PI

Expected

  • $PI()=|3.14159265358979|

Actual

  • $PI()=|3.14159265358979|

Function PRODUCT

Expected

  • $PRODUCT(0,4)=|0|
  • $PRODUCT(1,4)=|4|
  • $PRODUCT(2,4)=|8|
  • $PRODUCT(1,2,3,4)=|24|
  • $PRODUCT(1)=|1|
  • $PRODUCT(0)=|0|
  • $PRODUCT()=|1|

Actual

  • $PRODUCT(0,4)=|0|
  • $PRODUCT(1,4)=|4|
  • $PRODUCT(2,4)=|8|
  • $PRODUCT(1,2,3,4)=|24|
  • $PRODUCT(1)=|1|
  • $PRODUCT(0)=|0|
  • $PRODUCT()=|1|

Function PROPER

Expected

  • $PROPER(a small STEP)=|A Small Step|
  • $PROPER(f1 (formula-1))=|F1 (Formula-1)|
  • $PROPER()=||

Actual

  • $PROPER(a small STEP)=|A Small Step|
  • $PROPER(f1 (formula-1))=|F1 (Formula-1)|
  • $PROPER()=||

Function PROPERSPACE

Expected

  • $PROPERSPACE(Old MacDonald had a ServerFarm, EeEyeEeEyeOh)=|Old MacDonald had a Server Farm, Ee Eye Ee Eye Oh|
  • $PROPERSPACE()=||

Actual

  • $PROPERSPACE(Old MacDonald had a ServerFarm, EeEyeEeEyeOh)=|Old MacDonald had a Server Farm, Ee Eye Ee Eye Oh|
  • $PROPERSPACE()=||

Function RAND

Expected

  • $IF($RAND()<=1, OK, not OK)=|OK|
  • Manual test: $RAND(10), $RAND(10), $RAND(10)=|(three random numbers between 0 and 10)|
  • Manual test: $RAND(), $RAND(), $RAND()=|(three random numbers between 0 and 1)|

Actual

  • $IF($RAND()<=1, OK, not OK)=|OK|
  • Manual test: $RAND(10), $RAND(10), $RAND(10)=|3.86805429139525, 3.27065986571576, 9.21093680693037|
  • Manual test: $RAND(), $RAND(), $RAND()=|0.230805595487041, 0.711711660100715, 0.0480846633146186|

Function REPEAT

Expected

  • $REPEAT(/\, 10)=|/\/\/\/\/\/\/\/\/\/\|
  • $REPEAT(x)=||
  • $REPEAT()=||

Actual

  • $REPEAT(/\, 10)=|/\/\/\/\/\/\/\/\/\/\|
  • $REPEAT(x)=||
  • $REPEAT()=||

Function REPLACE

Expected

  • $REPLACE(abcd, 2, 1, X)=|aXcd|
  • $REPLACE(1023, 2, 1, X)=|1X23|
  • $REPLACE(z_1023, 4, 1, X)=|z_1X23|
  • $REPLACE(abcd, 2, 1)=|acd|
  • $REPLACE(abcd, 2, 0)=|abcd|
  • $REPLACE(abcd, 1, 3)=|d|
  • $REPLACE(abcd, 1, 4)=||
  • $REPLACE(abcd, 1, 4, YYYY)=|YYYY|
  • $REPLACE(abcd, 2, 4, YYYY)=|aYYYY|
  • $REPLACE(abcdefghijk,6,5,*)=|abcde*k|
  • $REPLACE(abcd)=|abcd|
  • $REPLACE()=||

Actual

  • $REPLACE(abcd, 2, 1, X)=|aXcd|
  • $REPLACE(1023, 2, 1, X)=|1X23|
  • $REPLACE(z_1023, 4, 1, X)=|z_1X23|
  • $REPLACE(abcd, 2, 1)=|acd|
  • $REPLACE(abcd, 2, 0)=|abcd|
  • $REPLACE(abcd, 1, 3)=|d|
  • $REPLACE(abcd, 1, 4)=||
  • $REPLACE(abcd, 1, 4, YYYY)=|YYYY|
  • $REPLACE(abcd, 2, 4, YYYY)=|aYYYY|
  • $REPLACE(abcdefghijk,6,5,*)=|abcde*k|
  • $REPLACE(abcd)=|abcd|
  • $REPLACE()=||

Function RIGHT

Expected

$SUM($RIGHT())=|3| 1 2
$SUM($RIGHT())=|7| 3 4

Actual

$SUM($RIGHT())=|3| 1 2
$SUM($RIGHT())=|7| 3 4

Function RIGHTSTRING

Expected

  • $RIGHTSTRING(abcdefg)=|g|
  • $RIGHTSTRING(abcdefg, 0)=|g|
  • $RIGHTSTRING(abcdefg, 1)=|g|
  • $RIGHTSTRING(abcdefg, 2)=|fg|
  • $RIGHTSTRING()=||

Actual

  • $RIGHTSTRING(abcdefg)=|g|
  • $RIGHTSTRING(abcdefg, 0)=|g|
  • $RIGHTSTRING(abcdefg, 1)=|g|
  • $RIGHTSTRING(abcdefg, 2)=|fg|
  • $RIGHTSTRING()=||

Function ROUND

Expected

  • $ROUND(3.15, 1)=|3.2|
  • $ROUND(3.149, 1)=|3.1|
  • $ROUND(-2.475, 2)=|-2.48|
  • $ROUND(34.9, -1)=|30|
  • $ROUND(12.34)=|12|
  • $ROUND(12.51)=|13|
  • $ROUND()=|0|

Actual

  • $ROUND(3.15, 1)=|3.2|
  • $ROUND(3.149, 1)=|3.1|
  • $ROUND(-2.475, 2)=|-2.48|
  • $ROUND(34.9, -1)=|30|
  • $ROUND(12.34)=|12|
  • $ROUND(12.51)=|13|
  • $ROUND()=|0|

Function ROW

Expected

$ROW()=1 $ROW()=1
$ROW()=2 $ROW(10)=12
$ROW()=3 $ROW(-10)=-7

Actual

$ROW()=1 $ROW()=1
$ROW()=2 $ROW(10)=12
$ROW()=3 $ROW(-10)=-7

Function SEARCH

Expected

  • $SEARCH([uy], fluffy)=|3|
  • $SEARCH([uy], fluffy, 4)=|6|
  • $SEARCH([abc], fluffy,)=|0|
  • $SEARCH(abc)=|0|
  • $SEARCH()=|0|

Actual

  • $SEARCH([uy], fluffy)=|3|
  • $SEARCH([uy], fluffy, 4)=|6|
  • $SEARCH([abc], fluffy,)=|0|
  • $SEARCH(abc)=|0|
  • $SEARCH()=|0|

Function SET

Expected

  • $SET(test, 1234)=||
  • $GET(test)=|1234|
  • $SET(sum, $SUM(1, 2, 3, 4))=||
  • $GET(sum)=|10|
  • $SET(novalue)=||
  • $GET(novalue)=||
  • $SET()=||

Actual

  • $SET(test, 1234)=||
  • $GET(test)=|1234|
  • $SET(sum, $SUM(1, 2, 3, 4))=||
  • $GET(sum)=|10|
  • $SET(novalue)=||
  • $GET(novalue)=||
  • $SET()=||

Function SETIFEMPTY

Expected

  • $SET(test, 1234)=||
  • $SETIFEMPTY(test, 1)=||
  • $GET(test)=|1234|
  • $SET(test, 0)=||
  • $SETIFEMPTY(test, 2)=||
  • $GET(test)=|2|
  • $SET(test,)=||
  • $SETIFEMPTY(test, 3)=||
  • $GET(test)=|3|

Actual

  • $SET(test, 1234)=||
  • $SETIFEMPTY(test, 1)=||
  • $GET(test)=|1234|
  • $SET(test, 0)=||
  • $SETIFEMPTY(test, 2)=||
  • $GET(test)=|2|
  • $SET(test,)=||
  • $SETIFEMPTY(test, 3)=||
  • $GET(test)=|3|

Function SETM

Expected

  • $SET(total, 10)=||
  • $SETM(total, +5)=||
  • $SETM(total)=||
  • $GET(total)=|15|
  • $SETM()=||

Actual

  • $SET(total, 10)=||
  • $SETM(total, +5)=||
  • $SETM(total)=||
  • $GET(total)=|15|
  • $SETM()=||

Function SIGN

Expected

  • $SIGN(12.34)=|1|
  • $SIGN(2)=|1|
  • $SIGN(0)=|0|
  • $SIGN()=|0|
  • $SIGN(-2)=|-1|

Actual

  • $SIGN(12.34)=|1|
  • $SIGN(2)=|1|
  • $SIGN(0)=|0|
  • $SIGN()=|0|
  • $SIGN(-2)=|-1|

Function SPLIT

Expected

  • $SPLIT(, Apple Orange Kiwi)=|Apple, Orange, Kiwi|
  • $SPLIT(-, Apple-Orange-Kiwi)=|Apple, Orange, Kiwi|
  • $SPLIT([-:]$sp*, Apple-Orange: Kiwi)=|Apple, Orange, Kiwi|
  • $SPLIT($empty, Apple)=|A, p, p, l, e|
  • $SPLIT(x)=||
  • $SPLIT()=||

Actual

  • $SPLIT(, Apple Orange Kiwi)=|Apple, Orange, Kiwi|
  • $SPLIT(-, Apple-Orange-Kiwi)=|Apple, Orange, Kiwi|
  • $SPLIT([-:]$sp*, Apple-Orange: Kiwi)=|Apple, Orange, Kiwi|
  • $SPLIT($empty, Apple)=|A, p, p, l, e|
  • $SPLIT(x)=||
  • $SPLIT()=||

Function SQRT

Expected

  • $SQRT(16)=|4|
  • $SQRT(1)=|1|
  • $SQRT()=|0|

Actual

  • $SQRT(16)=|4|
  • $SQRT(1)=|1|
  • $SQRT()=|0|

Function STDEV

Expected

  • $STDEV(2, 4, 4, 4, 5, 5, 7, 9)=|2.1380899352994|
  • $STDEV(2, 5, 3, 12)=|4.50924975282289|
  • $STDEV(2, 5, 3, xyz, 12)=|4.50924975282289|
  • $STDEV(3.50, 5.00, 7.23, 2.99)=|1.90205152401295|
  • $STDEV()=|0|

Actual

  • $STDEV(2, 4, 4, 4, 5, 5, 7, 9)=|2.1380899352994|
  • $STDEV(2, 5, 3, 12)=|4.50924975282289|
  • $STDEV(2, 5, 3, xyz, 12)=|4.50924975282289|
  • $STDEV(3.50, 5.00, 7.23, 2.99)=|1.90205152401295|
  • $STDEV()=|0|

Function STDEVP

Expected

  • $STDEVP(2, 5, 3, 12)=|3.90512483795333|
  • $STDEVP(2, 5, 3, xyz, 12)=|3.90512483795333|
  • $STDEVP(3.50, 5.00, 7.23, 2.99)=|1.64722493910213|
  • $STDEVP()=|0|

Actual

  • $STDEVP(2, 5, 3, 12)=|3.90512483795333|
  • $STDEVP(2, 5, 3, xyz, 12)=|3.90512483795333|
  • $STDEVP(3.50, 5.00, 7.23, 2.99)=|1.64722493910213|
  • $STDEVP()=|0|

Function SUBSTITUTE

Expected

  • $SUBSTITUTE(Good morning, morning, day)=|Good day|
  • $SUBSTITUTE(Q2-2012, 2, 3)=|Q3-3013|
  • $SUBSTITUTE(Q2-2012,2, 3, 3)=|Q2-2013|
  • $SUBSTITUTE(abc123def, [0-9], 9, , r)=|abc999def|
  • $SUBSTITUTE(abcd)=|abcd|
  • $SUBSTITUTE()=||

Actual

  • $SUBSTITUTE(Good morning, morning, day)=|Good day|
  • $SUBSTITUTE(Q2-2012, 2, 3)=|Q3-3013|
  • $SUBSTITUTE(Q2-2012,2, 3, 3)=|Q2-2013|
  • $SUBSTITUTE(abc123def, [0-9], 9, , r)=|abc999def|
  • $SUBSTITUTE(abcd)=|abcd|
  • $SUBSTITUTE()=||

Function SUBSTRING

Expected

  • $SUBSTRING(abcdef,3,5)=|cdef|
  • $SUBSTRING(abcdefgh,3,5)=|cdefg|
  • $SUBSTRING(abcdefg,-2,2)=|fg|
  • $SUBSTRING(abcdefg,-1,2)=|g|
  • $SUBSTRING(abcdefg,0,2)=||
  • $SUBSTRING(abcdefg,1,2)=|ab|
  • $SUBSTRING(abcdefg,2,2)=|bc|
  • $SUBSTRING(abcdefg,2,-1)=|bcdef|
  • $SUBSTRING(abcdefg,-2,-1)=|f|
  • $SUBSTRING(abcdefg)=||
  • $SUBSTRING()=||

Actual

  • $SUBSTRING(abcdef,3,5)=|cdef|
  • $SUBSTRING(abcdefgh,3,5)=|cdefg|
  • $SUBSTRING(abcdefg,-2,2)=|fg|
  • $SUBSTRING(abcdefg,-1,2)=|g|
  • $SUBSTRING(abcdefg,0,2)=||
  • $SUBSTRING(abcdefg,1,2)=|ab|
  • $SUBSTRING(abcdefg,2,2)=|bc|
  • $SUBSTRING(abcdefg,2,-1)=|bcdef|
  • $SUBSTRING(abcdefg,-2,-1)=|f|
  • $SUBSTRING(abcdefg)=||
  • $SUBSTRING()=||

Function SUM

Expected

  • $SUM(1, 2, 3, 4, 5)=|15|
  • $SUM(1, x, 3, , 5)=|9|
  • $SUM(1)=|1|
  • $SUM()=|0|

Actual

  • $SUM(1, 2, 3, 4, 5)=|15|
  • $SUM(1, x, 3, , 5)=|9|
  • $SUM(1)=|1|
  • $SUM()=|0|

Function SUMDAYS

Expected

  • $SUMDAYS(2w, 1, 2d, 4h)=|13.5|
  • $SUMDAYS(1w, x)=|5|
  • $SUMDAYS()=|0|

Actual

  • $SUMDAYS(2w, 1, 2d, 4h)=|13.5|
  • $SUMDAYS(1w, x)=|5|
  • $SUMDAYS()=|0|

Function SUMPRODUCT

Expected

1 2
3 4
$SUMPRODUCT(R1:C1..R2:C1, R1:C2..R2:C2)=|14| $SUMPRODUCT(R1:C1..R2:C1, $ABOVE())=|14|

Actual

1 2
3 4
$SUMPRODUCT(R1:C1..R2:C1, R1:C2..R2:C2)=|14| $SUMPRODUCT(R1:C1..R2:C1, $ABOVE())=|14|

Function T

Expected

1 2
3 4
$T(R2:C1)=|3| $T(R1:C2)=|2|

Actual

1 2
3 4
$T(R2:C1)=|3| $T(R1:C2)=|2|

Function TIME

Expected

  • $TIME(2012-12-31 GMT)=|1356912000|
  • Manual test: $TIME($FORMATTIME($TIME(), $year-$mo-$day))=|(today)|

Actual

  • $TIME(2012-12-31 GMT)=|1356912000|
  • Manual test: $TIME($FORMATTIME($TIME(), $year-$mo-$day))=|2025-04-01|

Function TIMEADD

Expected

  • $FORMATTIME($TIMEADD($TIME(2012-12-31), 2, day), $year-$mo-$day)=|2013-01-02|
  • $FORMATTIME($TIMEADD($TIME(2012-12-31), 2, year), $year-$mo-$day)=|2014-12-31|
  • $TIMEADD($TIME(2012-12-31 GMT), 10)=|1356912010|
  • $TIMEADD($TIME(2012-12-31 GMT), 10, sec)=|1356912010|
  • $TIMEADD($TIME(2012-12-31 GMT), 2, min)=|1356912120|
  • $TIMEADD()=|0|

Actual

  • $FORMATTIME($TIMEADD($TIME(2012-12-31), 2, day), $year-$mo-$day)=|2013-01-02|
  • $FORMATTIME($TIMEADD($TIME(2012-12-31), 2, year), $year-$mo-$day)=|2014-12-31|
  • $TIMEADD($TIME(2012-12-31 GMT), 10)=|1356912010|
  • $TIMEADD($TIME(2012-12-31 GMT), 10, sec)=|1356912010|
  • $TIMEADD($TIME(2012-12-31 GMT), 2, min)=|1356912120|
  • $TIMEADD()=|0|

Function TIMEDIFF

Expected

  • $TIMEDIFF($TIME(), $EVAL($TIME()+90), minute)=|1.5|
  • $TIMEDIFF($ROUND($TIMEDIFF($TIME(2012-12-06),$TIME(2012-12-13), day)))=|7|
  • $TIMEDIFF()=|0|

Actual

  • $TIMEDIFF($TIME(), $EVAL($TIME()+90), minute)=|1.5|
  • $TIMEDIFF($ROUND($TIMEDIFF($TIME(2012-12-06),$TIME(2012-12-13), day)))=|7|
  • $TIMEDIFF()=|0|

Function TODAY

Expected

  • (can't be tested automatically)
  • Manual test: $TIME($FORMATTIME($TODAY(), $year-$mo-$day $hour:$min:$sec GMT))=|(this morning midnight GMT)|

Actual

  • (can't be tested automatically)
  • Manual test: $TIME($FORMATTIME($TODAY(), $year-$mo-$day $hour:$min:$sec GMT))=|2025-04-02 00:00:00 GMT|

Function TRANSLATE

Expected

  • $TRANSLATE(boom,bm,cl)=|cool|
  • $TRANSLATE(one, two,$comma,;)=|one; two|
  • $TRANSLATE()=||

Actual

  • $TRANSLATE(boom,bm,cl)=|cool|
  • $TRANSLATE(one, two,$comma,;)=|one; two|
  • $TRANSLATE()=||

Function TRIM

Expected

  • $TRIM( eat spaces )=|eat spaces|
  • $TRIM()=||

Actual

  • $TRIM( eat spaces )=|eat spaces|
  • $TRIM()=||

Function UPPER

Expected

  • $UPPER(this beCOMES an UPPER cASE String)=|THIS BECOMES AN UPPER CASE STRING|
  • $UPPER()=||

Actual

  • $UPPER(this beCOMES an UPPER cASE String)=|THIS BECOMES AN UPPER CASE STRING|
  • $UPPER()=||

Function VALUE

Expected

  • $VALUE(US$1,200)=|1200|
  • $VALUE(PrjNotebook1234)=|1234|
  • $VALUE(Total: -12.5)=|-12.5|
  • $VALUE()=|0|

Actual

  • $VALUE(US$1,200)=|1200|
  • $VALUE(PrjNotebook1234)=|1234|
  • $VALUE(Total: -12.5)=|-12.5|
  • $VALUE()=|0|

Function VAR

Expected

  • $VAR(1, 2, 3, 4, 5, 6)=|3.5|
  • $VAR(2, 5, 3, 12)=|20.3333333333333|
  • $VAR(2, 5, 3, xyz, 12)=|20.3333333333333|
  • $VAR(3.50, 5.00, 7.23, 2.99)=|3.6178|
  • $VAR()=|0|

Actual

  • $VAR(1, 2, 3, 4, 5, 6)=|3.5|
  • $VAR(2, 5, 3, 12)=|20.3333333333333|
  • $VAR(2, 5, 3, xyz, 12)=|20.3333333333333|
  • $VAR(3.50, 5.00, 7.23, 2.99)=|3.6178|
  • $VAR()=|0|

Function VARP

Expected

  • $VARP(1, 2, 3, 4, 5, 6)=|2.91666666666667|
  • $VARP(2, 5, 3, 12)=|15.25|
  • $VARP(2, 5, 3, xyz, 12)=|15.25|
  • $VARP(3.50, 5.00, 7.23, 2.99)=|2.71335|
  • $VARP()=|0|

Actual

  • $VARP(1, 2, 3, 4, 5, 6)=|2.91666666666667|
  • $VARP(2, 5, 3, 12)=|15.25|
  • $VARP(2, 5, 3, xyz, 12)=|15.25|
  • $VARP(3.50, 5.00, 7.23, 2.99)=|2.71335|
  • $VARP()=|0|

Function WHILE

Expected

  • $WHILE($counter<=10, $counter )=|1 2 3 4 5 6 7 8 9 10 |
  • $SET(i, 0)$WHILE($GET(i)<10, $SETM(i, +1) $EVAL($GET(i) * $GET(i)), )=| 1, 4, 9, 16, 25, 36, 49, 64, 81, 100, |
  • $WHILE()=||

Actual

  • $WHILE($counter<=10, $counter )=|1 2 3 4 5 6 7 8 9 10 |
  • $SET(i, 0)$WHILE($GET(i)<10, $SETM(i, +1) $EVAL($GET(i) * $GET(i)), )=| 1, 4, 9, 16, 25, 36, 49, 64, 81, 100, |
  • $WHILE()=||

Function WORKINGDAYS

Expected

  • $WORKINGDAYS($TIME(2012-07-15 GMT), $TIME(2012-08-03 GMT))=|14|
  • $WORKINGDAYS()=|0|

Actual

  • $WORKINGDAYS($TIME(2012-07-15 GMT), $TIME(2012-08-03 GMT))=|14|
  • $WORKINGDAYS()=|0|

Function XOR

Expected

  • $XOR(0)=|0|
  • $XOR(1)=|0|
  • $XOR(0, 0)=|0|
  • $XOR(0, 1)=|1|
  • $XOR(1, 0)=|1|
  • $XOR(1, 1)=|0|
  • $XOR(0, 1, 2, 3)=|1|
  • $XOR(1, 2, 3, 4)=|0|
  • $XOR()=|0|

Actual

  • $XOR(0)=|0|
  • $XOR(1)=|0|
  • $XOR(0, 0)=|0|
  • $XOR(0, 1)=|1|
  • $XOR(1, 0)=|1|
  • $XOR(1, 1)=|0|
  • $XOR(0, 1, 2, 3)=|1|
  • $XOR(1, 2, 3, 4)=|0|
  • $XOR()=|0|

Related Topics: SpreadSheetPlugin, VarCALC, VarCALCULATE

-- TWiki:Main.PeterThoeny - 2012-11-01

Revision r1 - 2012-11-06 - 03:07:35 - TWikiContributor Edit