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)}=|%CALCULATE{$REPLACE(1234, 2, 1, X)}%|
  • CALCULATE{$REPLACE(1234, 2, 1, X)}=|%CALCULATE{$REPLACE(1234, 2, 1, X)}%|

NOTE: All functions that follow are tested via:

  • CALC{} if reference is done to table cells
  • CALCULATE{} otherwise

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)=|%CALCULATE{$ABS(-2)}%|
  • $ABS(-0.5)=|%CALCULATE{$ABS(-0.5)}%|
  • $ABS(0)=|%CALCULATE{$ABS(0)}%|
  • $ABS(0.5)=|%CALCULATE{$ABS(0.5)}%|
  • $ABS(2)=|%CALCULATE{$ABS(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()=|%CALCULATE{$AND(0)}%|
  • $AND(0)=|%CALCULATE{$AND(0)}%|
  • $AND(1)=|%CALCULATE{$AND(1)}%|
  • $AND(0, 0)=|%CALCULATE{$AND(0, 0)}%|
  • $AND(0, 1)=|%CALCULATE{$AND(0, 1)}%|
  • $AND(1, 0)=|%CALCULATE{$AND(1, 0)}%|
  • $AND(1, 1)=|%CALCULATE{$AND(1, 1)}%|
  • $AND(0, 1, 2, 3)=|%CALCULATE{$AND(0, 1, 2, 3)}%|
  • $AND(1, 2, 3, 4)=|%CALCULATE{$AND(1, 2, 3, 4)}%|

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()=|%CALCULATE{$AVERAGE()}%|
  • $AVERAGE(x)=|%CALCULATE{$AVERAGE(x)}%|
  • $AVERAGE(0)=|%CALCULATE{$AVERAGE(0)}%|
  • $AVERAGE(0, 1)=|%CALCULATE{$AVERAGE(0, 1)}%|
  • $AVERAGE(0, 1, 2)=|%CALCULATE{$AVERAGE(0, 1, 2)}%|
  • $AVERAGE(1.5, 2, 2.5)=|%CALCULATE{$AVERAGE(1.5, 2, 2.5)}%|
  • $AVERAGE(-1.5, 2, 2.5)=|%CALCULATE{$AVERAGE(-1.5, 2, 2.5)}%|

Function BITXOR

Expected

  • $BITXOR()=||
  • $BITXOR(A123)=||
  • $BITXOR($BITXOR(anything))=|anything|

Actual

  • $BITXOR()=|%CALCULATE{$BITXOR()}%|
  • $BITXOR(A123)=|%CALCULATE{$BITXOR(A123)}%|
  • $BITXOR($BITXOR(anything))=|%CALCULATE{$BITXOR($BITXOR(anything))}%|

Function CEILING

Expected

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

Actual

  • $CEILING(5.4)=|%CALCULATE{$CEILING(5.4)}%|
  • $CEILING(-5.4)=|%CALCULATE{$CEILING(-5.4)}%|

Function CHAR

Expected

  • $CHAR(97)=|a|

Actual

  • $CHAR(97)=|%CALCULATE{$CHAR(97)}%|

Function CODE

Expected

  • $CODE(abc)=|97|

Actual

  • $CODE(abc)=|%CALCULATE{$CODE(abc)}%|

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)=|%CALCULATE{$COUNTITEMS(One)}%|
  • $COUNTITEMS(One, Two)=|%CALCULATE{$COUNTITEMS(One, Two)}%|
  • $COUNTITEMS(One, Two, One)=|%CALCULATE{$COUNTITEMS(One, Two, One)}%|

Function COUNTSTR

Expected

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

Actual

  • $COUNTSTR(Done, , Done, OK, )=|%CALCULATE{$COUNTSTR(Done, , Done, OK, )}%|
  • $COUNTSTR(Done, , Done, OK, Done)=|%CALCULATE{$COUNTSTR(Done, , Done, OK, Done)}%|

Function DEF

Expected

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

Actual

  • $DEF(One, Two, Three)=|%CALCULATE{$DEF(One, Two, Three)}%|
  • $DEF(, Two, Three)=|%CALCULATE{$DEF(, Two, Three)}%|
  • $DEF(, , Three)=|%CALCULATE{$DEF(, , Three)}%|
  • $DEF(, , )=|%CALCULATE{$DEF(, , )}%|

Function EMPTY

Expected

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

Actual

  • $EMPTY()=|%CALCULATE{$EMPTY()}%|
  • $EMPTY( )=|%CALCULATE{$EMPTY( )}%|
  • $EMPTY(foo)=|%CALCULATE{$EMPTY(foo)}%|

Function EVAL

Expected

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

Actual

  • $EVAL( (5 * 3) / 2 + 1.1 )=|%CALCULATE{$EVAL( (5 * 3) / 2 + 1.1 )}%|

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()=|%CALCULATE{$EVEN()}%|
  • $EVEN(-1)=|%CALCULATE{$EVEN(-1)}%|
  • $EVEN(0)=|%CALCULATE{$EVEN(0)}%|
  • $EVEN(1)=|%CALCULATE{$EVEN(1)}%|
  • $EVEN(2)=|%CALCULATE{$EVEN(2)}%|
  • $EVEN(3.4)=|%CALCULATE{$EVEN(3.4)}%|
  • $EVEN(4.4)=|%CALCULATE{$EVEN(4.4)}%|
  • $EVEN(4.6)=|%CALCULATE{$EVEN(4.6)}%|

Function EXACT

Expected

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

Actual

  • $EXACT(foo, Foo)=|%CALCULATE{$EXACT(foo, Foo)}%|
  • $EXACT(foo, $LOWER(Foo))=|%CALCULATE{$EXACT(foo, $LOWER(Foo))}%|

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)))=|%CALCULATE{$SET(msg, $NOEXEC(Hi $GET(name)))}%|
  • $EXEC($SET(name, Tom) $EXEC($GET(msg)))=|%CALCULATE{$EXEC($SET(name, Tom) $EXEC($GET(msg)))}%|
  • $EXEC($SET(name, Jerry) $EXEC($GET(msg)))=|%CALCULATE{$EXEC($SET(name, Jerry) $EXEC($GET(msg)))}%|

Function EXISTS

Expected

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

Actual

  • $EXISTS(WebHome)=|%CALCULATE{$EXISTS(WebHome)}%|
  • $EXISTS(ThisDoesNotExist)=|%CALCULATE{$EXISTS(ThisDoesNotExist)}%|

Function EXP

Expected

  • $EXP(1)=|2.71828182845905|

Actual

  • $EXP(1)=|%CALCULATE{$EXP(1)}%|

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)=|%CALCULATE{$FILTER(f, fluffy)}%|
  • $FILTER(an Franc, San Francisco)=|%CALCULATE{$FILTER(an Franc, San Francisco)}%|
  • $FILTER($sp, Cat and Mouse)=|%CALCULATE{$FILTER($sp, Cat and Mouse)}%|
  • $FILTER([^0-9], Project-ID-1234)=|%CALCULATE{$FILTER([^0-9], Project-ID-1234)}%|
  • $FILTER([^a-zA-Z0-9 ], Stupid mistake*%@^! Fixed)=|%CALCULATE{$FILTER([^a-zA-Z0-9 ], Stupid mistake*%@^! Fixed)}%|

Function FIND

Expected

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

Actual

  • $FIND(f, fluffy)=|%CALCULATE{$FIND(f, fluffy)}%|
  • $FIND(f, fluffy, 2)=|%CALCULATE{$FIND(f, fluffy, 2)}%|
  • $FIND(x, fluffy, 1)=|%CALCULATE{$FIND(x, fluffy, 1)}%|

Function FLOOR

Expected

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

Actual

  • $FLOOR(5.4)=|%CALCULATE{$FLOOR(5.4)}%|
  • $FLOOR(-5.4)=|%CALCULATE{$FLOOR(-5.4)}%|

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)=|%CALCULATE{$FORMAT(COMMA, 2, 12345.6789)}%|
  • $FORMAT(DOLLAR, 2, 12345.6789)=|%CALCULATE{$FORMAT(DOLLAR, 2, 12345.6789)}%|
  • $FORMAT(KB, 2, 1234567)=|%CALCULATE{$FORMAT(KB, 2, 1234567)}%|
  • $FORMAT(MB, 2, 1234567)=|%CALCULATE{$FORMAT(MB, 2, 1234567)}%|
  • $FORMAT(KBMB, 2, 1234567)=|%CALCULATE{$FORMAT(KBMB, 2, 1234567)}%|
  • $FORMAT(KBMB, 2, 1234567890)=|%CALCULATE{$FORMAT(KBMB, 2, 1234567890)}%|
  • $FORMAT(NUMBER, 1, 12345.67)=|%CALCULATE{$FORMAT(NUMBER, 1, 12345.67)}%|
  • $FORMAT(PERCENT, 1, 0.1234567)=|%CALCULATE{$FORMAT(PERCENT, 1, 0.1234567)}%|

Function FORMATGMTIME

Expected

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

Actual

  • $FORMATGMTIME(1041379200, $day $mon $year)=|%CALCULATE{$FORMATGMTIME(1041379200, $day $mon $year)}%|

Function FORMATTIME

Expected

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

Actual

  • $FORMATTIME(0, $year/$month/$day GMT)=|%CALCULATE{$FORMATTIME(0, $year/$month/$day 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)=|%CALCULATE{$FORMATTIMEDIFF(min, 1, 200)}%|
  • $FORMATTIMEDIFF(min, 2, 200)=|%CALCULATE{$FORMATTIMEDIFF(min, 2, 200)}%|
  • $FORMATTIMEDIFF(min, 1, 1640)=|%CALCULATE{$FORMATTIMEDIFF(min, 1, 1640)}%|
  • $FORMATTIMEDIFF(min, 2, 1640)=|%CALCULATE{$FORMATTIMEDIFF(min, 2, 1640)}%|
  • $FORMATTIMEDIFF(min, 3, 1640)=|%CALCULATE{$FORMATTIMEDIFF(min, 3, 1640)}%|

Function GET

Expected

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

Actual

  • $SET(test, 1234)=|%CALCULATE{$SET(test, 1234)}%|
  • $GET(test)=|%CALCULATE{$GET(test)}%|
  • $GET()=|%CALCULATE{$GET()}%|

Function HEXDECODE

Expected

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

Actual

  • $HEXDECODE(687474703A2F2F7477696B692E6F72672F)=|%CALCULATE{$HEXDECODE(687474703A2F2F7477696B692E6F72672F)}%|

Function HEXENCODE

Expected

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

Actual

  • $HEXENCODE(http://twiki.org/)=|%CALCULATE{$HEXENCODE(http://twiki.org/)}%|

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)=|%CALCULATE{$SET(test_number, 123)}%|
  • $IF($GET(test_number)>100, greater)=|%CALCULATE{$IF($GET(test_number)>100, greater)}%|
  • $SET(test_string, San Francisco)=|%CALCULATE{$SET(test_string, San Francisco)}%|
  • $IF($EXACT($GET(test_string), Cupertino), equal, not equal)=|%CALCULATE{$IF($EXACT($GET(test_string), Cupertino), equal, not equal)}%|
  • $SET(result, $IF($GET(test_number)==0, zero, $GET(test_number)))=|%CALCULATE{$SET(result, $IF($GET(test_number)==0, zero, $GET(test_number)))}%|
  • $GET(result)=|%CALCULATE{$GET(result)}%|

Function INSERTSTRING

Expected

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

Actual

  • $INSERTSTRING(abcdefg, 2, XYZ)=|%CALCULATE{$INSERTSTRING(abcdefg, 2, XYZ)}%|
  • $INSERTSTRING(abcdefg, -2, XYZ)=|%CALCULATE{$INSERTSTRING(abcdefg, -2, XYZ)}%|

Function INT

Expected

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

Actual

  • $INT(10 / 4)=|%CALCULATE{$INT(10 / 4)}%|
  • $INT($VALUE(09))=|%CALCULATE{$INT($VALUE(09))}%|

Function ISDIGIT

Expected

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

Actual

  • $ISDIGIT(123)=|%CALCULATE{$ISDIGIT(123)}%|
  • $ISDIGIT(-7)=|%CALCULATE{$ISDIGIT(-7)}%|
  • $ISDIGIT(abc123)=|%CALCULATE{$ISDIGIT(abc123)}%|
  • $ISDIGIT()=|%CALCULATE{$ISDIGIT()}%|

Function ISLOWER

Expected

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

Actual

  • $ISLOWER(apple)=|%CALCULATE{$ISLOWER(apple)}%|
  • $ISLOWER(apple tree)=|%CALCULATE{$ISLOWER(apple tree)}%|
  • $ISLOWER(ORANGE)=|%CALCULATE{$ISLOWER(ORANGE)}%|

Function ISUPPER

Expected

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

Actual

  • $ISUPPER(apple)=|%CALCULATE{$ISUPPER(apple)}%|
  • $ISUPPER(ORANGE)=|%CALCULATE{$ISUPPER(ORANGE)}%|
  • $ISUPPER(ORANGE GARDEN)=|%CALCULATE{$ISUPPER(ORANGE GARDEN)}%|

Function ISWIKIWORD

Expected

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

Actual

  • $ISWIKIWORD(GoldenGate)=|%CALCULATE{$ISWIKIWORD(GoldenGate)}%|
  • $ISWIKIWORD(whiteRafting)=|%CALCULATE{$ISWIKIWORD(whiteRafting)}%|
  • $ISWIKIWORD()=|%CALCULATE{$ISWIKIWORD()}%|

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)=|%CALCULATE{$LEFTSTRING(abcdefg)}%|
  • $LEFTSTRING(abcdefg, 5)=|%CALCULATE{$LEFTSTRING(abcdefg, 5)}%|
  • $LEFTSTRING()=|%CALCULATE{$LEFTSTRING()}%|

Function LENGTH

Expected

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

Actual

  • $LENGTH(abcd)=|%CALCULATE{$LENGTH(abcd)}%|
  • $LENGTH()=|%CALCULATE{$LENGTH()}%|

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)=|%CALCULATE{$LISTIF($item > 12, 14, 7, 25)}%|
  • $LISTIF($NOT($EXACT($item,)), A, B, , E)=|%CALCULATE{$LISTIF($NOT($EXACT($item,)), A, B, , E)}%|
  • $LISTIF($index > 2, A, B, C, D)=|%CALCULATE{$LISTIF($index > 2, A, B, 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)=|%CALCULATE{$LISTITEM(2, Apple, Orange, Apple, Kiwi)}%|
  • $LISTITEM(-1, Apple, Orange, Apple, Kiwi)=|%CALCULATE{$LISTITEM(-1, Apple, Orange, Apple, 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)=|%CALCULATE{$LISTJOIN(-, Apple, Orange, Apple, Kiwi)}%|
  • $LISTJOIN($empty, Apple, Orange, Apple, Kiwi)=|%CALCULATE{$LISTJOIN($empty, Apple, Orange, Apple, Kiwi)}%|
$LISTJOIN($n, Apple, Orange, Apple, Kiwi)=|%CALCULATE{$LISTJOIN($n, 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)=|%CALCULATE{$LISTMAP($index: $EVAL(2 * $item), 3, 5, 7, 11)}%|

Function LISTNONEMPTY

Expected

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

Actual

  • $LISTNONEMPTY(, Apple, Orange, , Kiwi)=|%CALCULATE{$LISTNONEMPTY(, 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)=|%CALCULATE{$LISTRAND(Apple, Apple, Apple)}%|
  • Manual test: $LISTRAND(Apple, Orange, Apple, Kiwi)=|%CALCULATE{$LISTRAND(Apple, Orange, Apple, Kiwi)}%|

Function LISTREVERSE

Expected

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

Actual

  • $LISTREVERSE(Apple, Orange, Apple, Kiwi)=|%CALCULATE{$LISTREVERSE(Apple, Orange, Apple, Kiwi)}%|

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)=|%CALCULATE{$LISTSHUFFLE(Apple, Apple, Apple)}%|
  • Manual test: $LISTSHUFFLE(Apple, Orange, Apple, Kiwi)=|%CALCULATE{$LISTSHUFFLE(Apple, Orange, Apple, Kiwi)}%|

Function LISTSIZE

Expected

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

Actual

  • $LISTSIZE(Apple, Orange, Apple, Kiwi)=|%CALCULATE{$LISTSIZE(Apple, Orange, Apple, Kiwi)}%|
  • $LISTSIZE()=|%CALCULATE{$LISTSIZE()}%|

Function LISTSORT

Expected

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

Actual

  • $LISTSORT(Apple, Orange, Apple, Kiwi)=|%CALCULATE{$LISTSORT(Apple, Orange, Apple, Kiwi)}%|

Function LISTTRUNCATE

Expected

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

Actual

  • $LISTTRUNCATE(2, Apple, Orange, Apple, Kiwi)=|%CALCULATE{$LISTTRUNCATE(2, Apple, Orange, Apple, Kiwi)}%|

Function LISTUNIQUE

Expected

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

Actual

  • $LISTUNIQUE(Apple, Orange, Apple, Kiwi)=|%CALCULATE{$LISTUNIQUE(Apple, Orange, Apple, Kiwi)}%|

Function LN

Expected

  • $LN(10)=|2.30258509299405|

Actual

  • $LN(10)=|%CALCULATE{$LN(10)}%|

Function LOG

Expected

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

Actual

  • $LOG(1000)=|%CALCULATE{$LOG(1000)}%|
  • $LOG(16, 2)=|%CALCULATE{$LOG(16, 2)}%|

Function LOWER

Expected

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

Actual

  • $LOWER(this BECOMES a lower cASE String)=|%CALCULATE{$LOWER(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)=|%CALCULATE{$MAX(7, 99, 2, 5)}%|
  • $MAX(A, 99, 2, 5)=|%CALCULATE{$MAX(A, 99, 2, 5)}%|
  • $MAX(A, B)=|%CALCULATE{$MAX(A, B)}%|
  • $MAX()=|%CALCULATE{$MAX()}%|

Function MEDIAN

Expected

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

Actual

  • $MEDIAN(3, 9, 4, 5)=|%CALCULATE{$MEDIAN(3, 9, 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)=|%CALCULATE{$MIN(7, 99, 2, 5)}%|
  • $MIN(A, 99, 2, 5)=|%CALCULATE{$MIN(A, 99, 2, 5)}%|
  • $MIN(A, B)=|%CALCULATE{$MIN(A, B)}%|
  • $MIN()=|%CALCULATE{$MIN()}%|

Function MOD

Expected

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

Actual

  • $MOD(7, 3)=|%CALCULATE{$MOD(7, 3)}%|
  • $MOD(7)=|%CALCULATE{$MOD(7)}%|
  • $MOD()=|%CALCULATE{$MOD()}%|

Function NOEXEC

Expected

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

Actual

  • $SET(msg, $NOEXEC(Hi $GET(name)))=|%CALCULATE{$SET(msg, $NOEXEC(Hi $GET(name)))}%|
  • $SET(name, Jane)$EXEC($GET(msg))=|%CALCULATE{$SET(name, Jane)$EXEC($GET(msg))}%|

Function NOP

Expected

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

Actual

  • $NOP(100$percnt $quotquoted$quot)=|%CALCULATE{$NOP(100$percnt $quotquoted$quot)}%|
  • $NOP()=|%CALCULATE{$NOP()}%|

Function NOT

Expected

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

Actual

  • $NOT(1)=|%CALCULATE{$NOT(1)}%|
  • $NOT(0)=|%CALCULATE{$NOT(0)}%|
  • $NOT(1234)=|%CALCULATE{$NOT(1234)}%|
  • $NOT()=|%CALCULATE{$NOT()}%|

Function ODD

Expected

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

Actual

  • $ODD(2)=|%CALCULATE{$ODD(2)}%|
  • $ODD(3)=|%CALCULATE{$ODD(3)}%|
  • $ODD(3.5)=|%CALCULATE{$ODD(3.5)}%|
  • $ODD(-4)=|%CALCULATE{$ODD(-4)}%|
  • $ODD()=|%CALCULATE{$ODD()}%|

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()=|%CALCULATE{$OR()}%|
  • $OR(0)=|%CALCULATE{$OR(0)}%|
  • $OR(1)=|%CALCULATE{$OR(1)}%|
  • $OR(0, 0)=|%CALCULATE{$OR(0, 0)}%|
  • $OR(0, 1)=|%CALCULATE{$OR(0, 1)}%|
  • $OR(1, 0)=|%CALCULATE{$OR(1, 0)}%|
  • $OR(1, 1)=|%CALCULATE{$OR(1, 1)}%|
  • $OR(0, 1, 2, 3)=|%CALCULATE{$OR(0, 1, 2, 3)}%|
  • $OR(1, 2, 3, 4)=|%CALCULATE{$OR(1, 2, 3, 4)}%|

Function PERCENTILE

Expected

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

Actual

  • $PERCENTILE(75, 400, 200, 500, 100, 300)=|%CALCULATE{$PERCENTILE(75, 400, 200, 500, 100, 300)}%|
  • $PERCENTILE(60)=|%CALCULATE{$PERCENTILE(60)}%|
  • $PERCENTILE()=|%CALCULATE{$PERCENTILE()}%|

Function PI

Expected

  • $PI()=|3.14159265358979|

Actual

  • $PI()=|%CALCULATE{$PI()}%|

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)=|%CALCULATE{$PRODUCT(0,4)}%|
  • $PRODUCT(1,4)=|%CALCULATE{$PRODUCT(1,4)}%|
  • $PRODUCT(2,4)=|%CALCULATE{$PRODUCT(2,4)}%|
  • $PRODUCT(1,2,3,4)=|%CALCULATE{$PRODUCT(1,2,3,4)}%|
  • $PRODUCT(1)=|%CALCULATE{$PRODUCT(1)}%|
  • $PRODUCT(0)=|%CALCULATE{$PRODUCT(0)}%|
  • $PRODUCT()=|%CALCULATE{$PRODUCT()}%|

Function PROPER

Expected

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

Actual

  • $PROPER(a small STEP)=|%CALCULATE{$PROPER(a small STEP)}%|
  • $PROPER(f1 (formula-1))=|%CALCULATE{$PROPER(f1 (formula-1))}%|
  • $PROPER()=|%CALCULATE{$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)=|%CALCULATE{$PROPERSPACE(Old MacDonald had a ServerFarm, EeEyeEeEyeOh)}%|
  • $PROPERSPACE()=|%CALCULATE{$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)=|%CALCULATE{$IF($RAND()<=1, OK, not OK)}%|
  • Manual test: $RAND(10), $RAND(10), $RAND(10)=|%CALCULATE{$RAND(10), $RAND(10), $RAND(10)}%|
  • Manual test: $RAND(), $RAND(), $RAND()=|%CALCULATE{$RAND(), $RAND(), $RAND()}%|

Function REPEAT

Expected

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

Actual

  • $REPEAT(/\, 10)=|%CALCULATE{$REPEAT(/\, 10)}%|
  • $REPEAT(x)=|%CALCULATE{$REPEAT(x)}%|
  • $REPEAT()=|%CALCULATE{$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)=|%CALCULATE{$REPLACE(abcd, 2, 1, X)}%|
  • $REPLACE(1023, 2, 1, X)=|%CALCULATE{$REPLACE(1023, 2, 1, X)}%|
  • $REPLACE(z_1023, 4, 1, X)=|%CALCULATE{$REPLACE(z_1023, 4, 1, X)}%|
  • $REPLACE(abcd, 2, 1)=|%CALCULATE{$REPLACE(abcd, 2, 1)}%|
  • $REPLACE(abcd, 2, 0)=|%CALCULATE{$REPLACE(abcd, 2, 0)}%|
  • $REPLACE(abcd, 1, 3)=|%CALCULATE{$REPLACE(abcd, 1, 3)}%|
  • $REPLACE(abcd, 1, 4)=|%CALCULATE{$REPLACE(abcd, 1, 4)}%|
  • $REPLACE(abcd, 1, 4, YYYY)=|%CALCULATE{$REPLACE(abcd, 1, 4, YYYY)}%|
  • $REPLACE(abcd, 2, 4, YYYY)=|%CALCULATE{$REPLACE(abcd, 2, 4, YYYY)}%|
  • $REPLACE(abcdefghijk,6,5,*)=|%CALCULATE{$REPLACE(abcdefghijk,6,5,*)}%|
  • $REPLACE(abcd)=|%CALCULATE{$REPLACE(abcd)}%|
  • $REPLACE()=|%CALCULATE{$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)=|%CALCULATE{$RIGHTSTRING(abcdefg)}%|
  • $RIGHTSTRING(abcdefg, 0)=|%CALCULATE{$RIGHTSTRING(abcdefg, 0)}%|
  • $RIGHTSTRING(abcdefg, 1)=|%CALCULATE{$RIGHTSTRING(abcdefg, 1)}%|
  • $RIGHTSTRING(abcdefg, 2)=|%CALCULATE{$RIGHTSTRING(abcdefg, 2)}%|
  • $RIGHTSTRING()=|%CALCULATE{$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)=|%CALCULATE{$ROUND(3.15, 1)}%|
  • $ROUND(3.149, 1)=|%CALCULATE{$ROUND(3.149, 1)}%|
  • $ROUND(-2.475, 2)=|%CALCULATE{$ROUND(-2.475, 2)}%|
  • $ROUND(34.9, -1)=|%CALCULATE{$ROUND(34.9, -1)}%|
  • $ROUND(12.34)=|%CALCULATE{$ROUND(12.34)}%|
  • $ROUND(12.51)=|%CALCULATE{$ROUND(12.51)}%|
  • $ROUND()=|%CALCULATE{$ROUND()}%|

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)=|%CALCULATE{$SEARCH([uy], fluffy)}%|
  • $SEARCH([uy], fluffy, 4)=|%CALCULATE{$SEARCH([uy], fluffy, 4)}%|
  • $SEARCH([abc], fluffy,)=|%CALCULATE{$SEARCH([abc], fluffy,)}%|
  • $SEARCH(abc)=|%CALCULATE{$SEARCH(abc)}%|
  • $SEARCH()=|%CALCULATE{$SEARCH()}%|

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)=|%CALCULATE{$SET(test, 1234)}%|
  • $GET(test)=|%CALCULATE{$GET(test)}%|
  • $SET(sum, $SUM(1, 2, 3, 4))=|%CALCULATE{$SET(sum, $SUM(1, 2, 3, 4))}%|
  • $GET(sum)=|%CALCULATE{$GET(sum)}%|
  • $SET(novalue)=|%CALCULATE{$SET(novalue)}%|
  • $GET(novalue)=|%CALCULATE{$GET(novalue)}%|
  • $SET()=|%CALCULATE{$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)=|%CALCULATE{$SET(test, 1234)}%|
  • $SETIFEMPTY(test, 1)=|%CALCULATE{$SETIFEMPTY(test, 1)}%|
  • $GET(test)=|%CALCULATE{$GET(test)}%|
  • $SET(test, 0)=|%CALCULATE{$SET(test, 0)}%|
  • $SETIFEMPTY(test, 2)=|%CALCULATE{$SETIFEMPTY(test, 2)}%|
  • $GET(test)=|%CALCULATE{$GET(test)}%|
  • $SET(test,)=|%CALCULATE{$SET(test,)}%|
  • $SETIFEMPTY(test, 3)=|%CALCULATE{$SETIFEMPTY(test, 3)}%|
  • $GET(test)=|%CALCULATE{$GET(test)}%|

Function SETM

Expected

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

Actual

  • $SET(total, 10)=|%CALCULATE{$SET(total, 10)}%|
  • $SETM(total, +5)=|%CALCULATE{$SETM(total, +5)}%|
  • $SETM(total)=|%CALCULATE{$SETM(total)}%|
  • $GET(total)=|%CALCULATE{$GET(total)}%|
  • $SETM()=|%CALCULATE{$SETM()}%|

Function SIGN

Expected

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

Actual

  • $SIGN(12.34)=|%CALCULATE{$SIGN(12.34)}%|
  • $SIGN(2)=|%CALCULATE{$SIGN(2)}%|
  • $SIGN(0)=|%CALCULATE{$SIGN(0)}%|
  • $SIGN()=|%CALCULATE{$SIGN()}%|
  • $SIGN(-2)=|%CALCULATE{$SIGN(-2)}%|

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)=|%CALCULATE{$SPLIT(, Apple Orange Kiwi)}%|
  • $SPLIT(-, Apple-Orange-Kiwi)=|%CALCULATE{$SPLIT(-, Apple-Orange-Kiwi)}%|
  • $SPLIT([-:]$sp*, Apple-Orange: Kiwi)=|%CALCULATE{$SPLIT([-:]$sp*, Apple-Orange: Kiwi)}%|
  • $SPLIT($empty, Apple)=|%CALCULATE{$SPLIT($empty, Apple)}%|
  • $SPLIT(x)=|%CALCULATE{$SPLIT()}%|
  • $SPLIT()=|%CALCULATE{$SPLIT()}%|

Function SQRT

Expected

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

Actual

  • $SQRT(16)=|%CALCULATE{$SQRT(16)}%|
  • $SQRT(1)=|%CALCULATE{$SQRT(1)}%|
  • $SQRT()=|%CALCULATE{$SQRT()}%|

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)=||
  • $STDEV(2, 5, 3, 12)=||
  • $STDEV(2, 5, 3, xyz, 12)=||
  • $STDEV(3.50, 5.00, 7.23, 2.99)=||
  • $STDEV()=||

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)=||
  • $STDEVP(2, 5, 3, xyz, 12)=||
  • $STDEVP(3.50, 5.00, 7.23, 2.99)=||
  • $STDEVP()=||

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)=|%CALCULATE{$SUBSTITUTE(Good morning, morning, day)}%|
  • $SUBSTITUTE(Q2-2012, 2, 3)=|%CALCULATE{$SUBSTITUTE(Q2-2012, 2, 3)}%|
  • $SUBSTITUTE(Q2-2012,2, 3, 3)=|%CALCULATE{$SUBSTITUTE(Q2-2012,2, 3, 3)}%|
  • $SUBSTITUTE(abc123def, [0-9], 9, , r)=|%CALCULATE{$SUBSTITUTE(abc123def, [0-9], 9, , r)}%|
  • $SUBSTITUTE(abcd)=|%CALCULATE{$SUBSTITUTE(abcd)}%|
  • $SUBSTITUTE()=|%CALCULATE{$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)=|%CALCULATE{$SUBSTRING(abcdef,3,5)}%|
  • $SUBSTRING(abcdefgh,3,5)=|%CALCULATE{$SUBSTRING(abcdefgh,3,5)}%|
  • $SUBSTRING(abcdefg,-2,2)=|%CALCULATE{$SUBSTRING(abcdefg,-2,2)}%|
  • $SUBSTRING(abcdefg,-1,2)=|%CALCULATE{$SUBSTRING(abcdefg,-1,2)}%|
  • $SUBSTRING(abcdefg,0,2)=|%CALCULATE{$SUBSTRING(abcdefg,0,2)}%|
  • $SUBSTRING(abcdefg,1,2)=|%CALCULATE{$SUBSTRING(abcdefg,1,2)}%|
  • $SUBSTRING(abcdefg,2,2)=|%CALCULATE{$SUBSTRING(abcdefg,2,2)}%|
  • $SUBSTRING(abcdefg,2,-1)=|%CALCULATE{$SUBSTRING(abcdefg,2,-1)}%|
  • $SUBSTRING(abcdefg,-2,-1)=|%CALCULATE{$SUBSTRING(abcdefg,-2,-1)}%|
  • $SUBSTRING(abcdefg)=|%CALCULATE{$SUBSTRING(abcdefg)}%|
  • $SUBSTRING()=|%CALCULATE{$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)=|%CALCULATE{$SUM(1, 2, 3, 4, 5)}%|
  • $SUM(1, x, 3, , 5)=|%CALCULATE{$SUM(1, x, 3, , 5)}%|
  • $SUM(1)=|%CALCULATE{$SUM(1)}%|
  • $SUM()=|%CALCULATE{$SUM()}%|

Function SUMDAYS

Expected

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

Actual

  • $SUMDAYS(2w, 1, 2d, 4h)=|%CALCULATE{$SUMDAYS(2w, 1, 2d, 4h)}%|
  • $SUMDAYS(1w, x)=|%CALCULATE{$SUMDAYS(1w, x)}%|
  • $SUMDAYS()=|%CALCULATE{$SUMDAYS()}%|

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)=|%CALCULATE{$TIME(2012-12-31 GMT)}%|
  • Manual test: $TIME($FORMATTIME($TIME(), $year-$mo-$day))=|%CALCULATE{$FORMATTIME($TIME(), $year-$mo-$day)}%|

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)=|%CALCULATE{$FORMATTIME($TIMEADD($TIME(2012-12-31), 2, day), $year-$mo-$day)}%|
  • $FORMATTIME($TIMEADD($TIME(2012-12-31), 2, year), $year-$mo-$day)=|%CALCULATE{$FORMATTIME($TIMEADD($TIME(2012-12-31), 2, year), $year-$mo-$day)}%|
  • $TIMEADD($TIME(2012-12-31 GMT), 10)=|%CALCULATE{$TIMEADD($TIME(2012-12-31 GMT), 10)}%|
  • $TIMEADD($TIME(2012-12-31 GMT), 10, sec)=|%CALCULATE{$TIMEADD($TIME(2012-12-31 GMT), 10, sec)}%|
  • $TIMEADD($TIME(2012-12-31 GMT), 2, min)=|%CALCULATE{$TIMEADD($TIME(2012-12-31 GMT), 2, min)}%|
  • $TIMEADD()=|%CALCULATE{$TIMEADD()}%|

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)=|%CALCULATE{$TIMEDIFF($TIME(), $EVAL($TIME()+90), minute)}%|
  • $TIMEDIFF($ROUND($TIMEDIFF($TIME(2012-12-06),$TIME(2012-12-13), day)))=|%CALCULATE{$ROUND($TIMEDIFF($TIME(2012-12-06),$TIME(2012-12-13), day))}%|
  • $TIMEDIFF()=|%CALCULATE{$TIMEDIFF()}%|

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))=|%CALCULATE{$FORMATTIME($TODAY(), $year-$mo-$day $hour:$min:$sec GMT)}%|

Function TRANSLATE

Expected

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

Actual

  • $TRANSLATE(boom,bm,cl)=|%CALCULATE{$TRANSLATE(boom,bm,cl)}%|
  • $TRANSLATE(one, two,$comma,;)=|%CALCULATE{$TRANSLATE(one, two,$comma,;)}%|
  • $TRANSLATE()=|%CALCULATE{$TRANSLATE()}%|

Function TRIM

Expected

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

Actual

  • $TRIM( eat spaces )=|%CALCULATE{$TRIM( eat spaces )}%|
  • $TRIM()=|%CALCULATE{$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)=|%CALCULATE{$UPPER(this beCOMES an UPPER cASE String)}%|
  • $UPPER()=|%CALCULATE{$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)=|%CALCULATE{$VALUE(US$1,200)}%|
  • $VALUE(PrjNotebook1234)=|%CALCULATE{$VALUE(PrjNotebook1234)}%|
  • $VALUE(Total: -12.5)=|%CALCULATE{$VALUE(Total: -12.5)}%|
  • $VALUE()=|%CALCULATE{$VALUE()}%|

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)=|%CALCULATE{$VAR(1, 2, 3, 4, 5, 6)}%|
  • $VAR(2, 5, 3, 12)=|%CALCULATE{$VAR(2, 5, 3, 12)}%|
  • $VAR(2, 5, 3, xyz, 12)=|%CALCULATE{$VAR(2, 5, 3, xyz, 12)}%|
  • $VAR(3.50, 5.00, 7.23, 2.99)=|%CALCULATE{$VAR(3.50, 5.00, 7.23, 2.99)}%|
  • $VAR()=|%CALCULATE{$VAR()}%|

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)=|%CALCULATE{$VARP(1, 2, 3, 4, 5, 6)}%|
  • $VARP(2, 5, 3, 12)=|%CALCULATE{$VARP(2, 5, 3, 12)}%|
  • $VARP(2, 5, 3, xyz, 12)=|%CALCULATE{$VARP(2, 5, 3, xyz, 12)}%|
  • $VARP(3.50, 5.00, 7.23, 2.99)=|%CALCULATE{$VARP(3.50, 5.00, 7.23, 2.99)}%|
  • $VARP()=|%CALCULATE{$VARP()}%|

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 )=|%CALCULATE{$WHILE($counter<=10, $counter )}%|
  • $SET(i, 0)$WHILE($GET(i)<10, $SETM(i, +1) $EVAL($GET(i) * $GET(i)), )=|%CALCULATE{$SET(i, 0)$WHILE($GET(i)<10, $SETM(i, +1) $EVAL($GET(i) * $GET(i)), )}%|
  • $WHILE()=|%CALCULATE{$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))=|%CALCULATE{$WORKINGDAYS($TIME(2012-07-15 GMT), $TIME(2012-08-03 GMT))}%|
  • $WORKINGDAYS()=|%CALCULATE{$WORKINGDAYS()}%|

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)=|%CALCULATE{$XOR(0)}%|
  • $XOR(1)=|%CALCULATE{$XOR(1)}%|
  • $XOR(0, 0)=|%CALCULATE{$XOR(0, 0)}%|
  • $XOR(0, 1)=|%CALCULATE{$XOR(0, 1)}%|
  • $XOR(1, 0)=|%CALCULATE{$XOR(1, 0)}%|
  • $XOR(1, 1)=|%CALCULATE{$XOR(1, 1)}%|
  • $XOR(0, 1, 2, 3)=|%CALCULATE{$XOR(0, 1, 2, 3)}%|
  • $XOR(1, 2, 3, 4)=|%CALCULATE{$XOR(1, 2, 3, 4)}%|
  • $XOR()=|%CALCULATE{$XOR()}%|

Related Topics: SpreadSheetPlugin, VarCALC, VarCALCULATE

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


This topic: TWiki > SpreadSheetPluginTestCases
Topic revision: r1 - 2012-11-06 - TWikiContributor
 
Copyright © 1999-2019 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback
Note: Please contribute updates to this topic on TWiki.org at TWiki:TWiki.SpreadSheetPluginTestCases.