GNHLUG> TWiki Web>SpreadSheetPluginTestCases (2017-10-19, TWikiContributor) EditAttach

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:

  • 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)=|2|
  • $ABS(-0.5)=|0.5|
  • $ABS(0)=|0|
  • $ABS(0.5)=|0.5|
  • $ABS(2)=|2|

Function ADDLIST

Expected

  • $SETLIST(test)=||
  • $ADDLIST(test, 1, 2)=||
  • $GETLIST(test)=|1, 2|
  • $ADDLIST(test, 3, 4)=||
  • $GETLIST(test)=|1, 2, 3, 4|
  • $ADDLIST(novalue)=||
  • $GETLIST(novalue)=||
  • $ADDLIST()=||

Actual

  • $SETLIST(test)=||
  • $ADDLIST(test, 1, 2)=||
  • $GETLIST(test)=|1, 2|
  • $ADDLIST(test, 3, 4)=||
  • $GETLIST(test)=|1, 2, 3, 4|
  • $ADDLIST(novalue)=||
  • $GETLIST(novalue)=||
  • $ADDLIST()=||

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 BIN2DEC

Expected

  • $BIN2DEC(1100100)=|100|
  • $BIN2DEC(1010101010101010101)=|349525|
  • $BIN2DEC()=|0|

Actual

  • $BIN2DEC(1100100)=|100|
  • $BIN2DEC(1010101010101010101)=|349525|
  • $BIN2DEC()=|0|

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 DEC2BIN

Expected

  • $DEC2BIN(9)=|1001|
  • $DEC2BIN(9, 6)=|001001|
  • $DEC2BIN()=|0|

Actual

  • $DEC2BIN(9)=|1001|
  • $DEC2BIN(9, 6)=|001001|
  • $DEC2BIN()=|0|

Function DEC2HEX

Expected

  • $DEC2HEX(165)=|A5|
  • $DEC2HEX(100, 4)=|0064|
  • $DEC2HEX()=|0|

Actual

  • $DEC2HEX(165)=|A5|
  • $DEC2HEX(100, 4)=|0064|
  • $DEC2HEX()=|0|

Function DEC2OCT

Expected

  • $DEC2OCT(58)=|72|
  • $DEC2OCT(58, 3)=|072|
  • $DEC2OCT()=|0|

Actual

  • $DEC2OCT(58)=|72|
  • $DEC2OCT(58, 3)=|072|
  • $DEC2OCT()=|0|

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 EQUAL

Expected

  • $EQUAL(foo, foo)=|1|
  • $EQUAL(foo, Foo)=|1|
  • $EQUAL(foo, bar)=|0|

Actual

  • $EQUAL(foo, foo)=|1|
  • $EQUAL(foo, Foo)=|1|
  • $EQUAL(foo, bar)=|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 GETHASH

Expected

  • $SETHASH()=||
  • $SETHASH(age, Jane, 26)=||
  • $SETHASH(age, Tim, 27)=||
  • $SETHASH(sex, Jane, F)=||
  • $SETHASH(sex, Tim, M)=||
  • $GETHASH(age, Jane)=|26|
  • $SETHASH(age, Jane)=||
  • $GETHASH(age, Jane)=||
  • $GETHASH(sex)=|Jane, Tim|
  • $GETHASH(foo, bar)=||
  • $GETHASH(foo)=||
  • $GETHASH()=|age, sex|

Actual

  • $SETHASH()=||
  • $SETHASH(age, Jane, 26)=||
  • $SETHASH(age, Tim, 27)=||
  • $SETHASH(sex, Jane, F)=||
  • $SETHASH(sex, Tim, M)=||
  • $GETHASH(age, Jane)=|26|
  • $SETHASH(age, Jane)=||
  • $GETHASH(age, Jane)=||
  • $GETHASH(sex)=|Jane, Tim|
  • $GETHASH(foo, bar)=||
  • $GETHASH(foo)=||
  • $GETHASH()=|age, sex|

Function GETLIST

Expected

  • $SETLIST(test, 1, 2, 3, 4)=||
  • $GETLIST(test)=|1, 2, 3, 4|
  • $GETLIST()=||

Actual

  • $SETLIST(test, 1, 2, 3, 4)=||
  • $GETLIST(test)=|1, 2, 3, 4|
  • $GETLIST()=||

Function HASH2LIST

Expected

  • $SETHASH()=||
  • $LIST2HASH(age, Jane, 26, Tim, 27)=||
  • $HASH2LIST(age)=|Jane, 26, Tim, 27|
  • $HASH2LIST(age, $key is $value)=|Jane is 26, Tim is 27|
  • $HASH2LIST(age, $key)=|Jane, Tim|
  • $HASH2LIST(age, $value)=|26, 27|

Actual

  • $SETHASH()=||
  • $LIST2HASH(age, Jane, 26, Tim, 27)=||
  • $HASH2LIST(age)=|Jane, 26, Tim, 27|
  • $HASH2LIST(age, $key is $value)=|Jane is 26, Tim is 27|
  • $HASH2LIST(age, $key)=|Jane, Tim|
  • $HASH2LIST(age, $value)=|26, 27|

Function HASHCOPY

Expected

  • $SETHASH()=||
  • $LIST2HASH(age, Jane, 26, Tim, 27, Sam, 27)=||
  • $HASHCOPY(age, new)=||
  • $SETHASH(age, Old, 95)=||
  • $SETHASH(new, New, 1)=||
  • $HASH2LIST(age, $key: $value)=|Jane: 26, Old: 95, Sam: 27, Tim: 27|
  • $HASH2LIST(new, $key: $value)=|Jane: 26, New: 1, Sam: 27, Tim: 27|
  • $HASHCOPY(age)=||
  • $HASHCOPY()=||

Actual

  • $SETHASH()=||
  • $LIST2HASH(age, Jane, 26, Tim, 27, Sam, 27)=||
  • $HASHCOPY(age, new)=||
  • $SETHASH(age, Old, 95)=||
  • $SETHASH(new, New, 1)=||
  • $HASH2LIST(age, $key: $value)=|Jane: 26, Old: 95, Sam: 27, Tim: 27|
  • $HASH2LIST(new, $key: $value)=|Jane: 26, New: 1, Sam: 27, Tim: 27|
  • $HASHCOPY(age)=||
  • $HASHCOPY()=||

Function HASHEACH

Expected

  • $SETHASH()=||
  • $LIST2HASH(age, Jane, 26, Tim, 27)=||
  • $HASHEACH($key is $INT($value * 2 + $index), age)=||
  • $HASH2LIST(age, $key: $value)=|Jane: Jane is 53, Tim: Tim is 56|

Actual

  • $SETHASH()=||
  • $LIST2HASH(age, Jane, 26, Tim, 27)=||
  • $HASHEACH($key is $INT($value * 2 + $index), age)=||
  • $HASH2LIST(age, $key: $value)=|Jane: Jane is 53, Tim: Tim is 56|

Function HASHEXISTS

Expected

  • $SETHASH()=||
  • $LIST2HASH(age, Jane, 26, Tim, 27)=||
  • $HASHEXISTS(age)=|1|
  • $HASHEXISTS(age, Jane)=|1|
  • $HASHEXISTS(age, Blake)=|0|
  • $HASHEXISTS(height)=|0|
  • $HASHEXISTS()=|0|

Actual

  • $SETHASH()=||
  • $LIST2HASH(age, Jane, 26, Tim, 27)=||
  • $HASHEXISTS(age)=|1|
  • $HASHEXISTS(age, Jane)=|1|
  • $HASHEXISTS(age, Blake)=|0|
  • $HASHEXISTS(height)=|0|
  • $HASHEXISTS()=|0|

Function HASHREVERSE

Expected

  • $SETHASH()=||
  • $LIST2HASH(age, Jane, 26, Tim, 27, Sam, 28)=||
  • $HASHREVERSE(age)=||
  • $HASH2LIST(age, $key: $value)=|26: Jane, 27: Tim, 28: Sam|
  • $HASHREVERSE(height)=||
  • $HASH2LIST(height)=||
  • $HASHREVERSE()=||

Actual

  • $SETHASH()=||
  • $LIST2HASH(age, Jane, 26, Tim, 27, Sam, 28)=||
  • $HASHREVERSE(age)=||
  • $HASH2LIST(age, $key: $value)=|26: Jane, 27: Tim, 28: Sam|
  • $HASHREVERSE(height)=||
  • $HASH2LIST(height)=||
  • $HASHREVERSE()=||

Function HEX2DEC

Expected

  • $HEX2DEC(A5)=|165|
  • $HEX2DEC(3DA408B9)=|1034160313|
  • $HEX2DEC()=|0|

Actual

  • $HEX2DEC(A5)=|165|
  • $HEX2DEC(3DA408B9)=|1034160313|
  • $HEX2DEC()=|0|

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 LIST2HASH

Expected

  • $SETHASH()=||
  • $LIST2HASH(age, Jane, 26, Tim, 27)=||
  • $GETHASH(age, Jane)=|26|
  • $GETHASH(age, Tim)=|27|
  • $LIST2HASH(age, Anna, 25, Empty, , Jane, 27, Zoltan)=||
  • $GETHASH(age, Jane)=|27|
  • $GETHASH(age, Empty)=||
  • $GETHASH(age, Tim)=|27|
  • $GETHASH(age)=|Anna, Empty, Jane, Tim|

Actual

  • $SETHASH()=||
  • $LIST2HASH(age, Jane, 26, Tim, 27)=||
  • $GETHASH(age, Jane)=|26|
  • $GETHASH(age, Tim)=|27|
  • $LIST2HASH(age, Anna, 25, Empty, , Jane, 27, Zoltan)=||
  • $GETHASH(age, Jane)=|27|
  • $GETHASH(age, Empty)=||
  • $GETHASH(age, Tim)=|27|
  • $GETHASH(age)=|Anna, Empty, Jane, Tim|

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 LISTEACH

Expected

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

Actual

  • $LISTEACH($index: $EVAL(2 * $item), 3, 5, 7, 11)=|1: 6, 2: 10, 3: 14, 4: 22|
  • $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)=|Orange|

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 NOTE

Expected

  • $NOTE(some text)=||
  • $NOTE()=||

Actual

  • $NOTE(some text)=||
  • $NOTE()=||

Function OCT2DEC

Expected

  • $OCT2DEC(54)=|44|
  • $OCT2DEC(77777533)=|16777051|
  • $OCT2DEC()=|0|

Actual

  • $OCT2DEC(54)=|44|
  • $OCT2DEC(77777533)=|16777051|
  • $OCT2DEC()=|0|

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|
  • $MULT(1,2,3,4)=|24|

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|
  • $MULT(1,2,3,4)=|24|

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)=|2.07737404145043, 4.84246777400575, 2.72379522526322|
  • Manual test: $RAND(), $RAND(), $RAND()=|0.459538604438873, 0.0784400667472411, 0.27475188879254|

Function RANDSTRING

Expected

  • Manual test: $RANDSTRING(), $RANDSTRING(), $RANDSTRING()=|(three random strings of 8 alphanumeric/underscore characters)|
  • Manual test: $RANDSTRING(A..NP..Z1..9, xxxx-xxxx-xxxx-xxxx), $RANDSTRING(A..NP..Z1..9, xxxx-xxxx-xxxx-xxxx), $RANDSTRING(A..NP..Z1..9, xxxx-xxxx-xxxx-xxxx)=|(three random strings, each of format xxxx-xxxx-xxxx-xxxx, composed of uppercase letters and numbers excluding letter O and number 0)|

Actual

  • Manual test: $RANDSTRING(), $RANDSTRING(), $RANDSTRING()=|5O_HfmIU, JqdDWNNE, AtlYsbJt|
  • Manual test: $RANDSTRING(A..NP..Z1..9, xxxx-xxxx-xxxx-xxxx), $RANDSTRING(A..NP..Z1..9, xxxx-xxxx-xxxx-xxxx), $RANDSTRING(A..NP..Z1..9, xxxx-xxxx-xxxx-xxxx)=|JWJ5-9CU6-MLL8-RF2N, 8CPZ-A5W4-JIB4-8J5M, 8P94-RDTJ-L5EZ-WRAU|

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 SETHASH

Expected

  • $SETHASH()=||
  • $SETHASH(age, Jane, 26)=||
  • $GETHASH(age, Jane)=|26|
  • $GETHASH()=|age|

Actual

  • $SETHASH()=||
  • $SETHASH(age, Jane, 26)=||
  • $GETHASH(age, Jane)=|26|
  • $GETHASH()=|age|

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 SETLIST

Expected

  • $SETLIST(test, 1, '''2, 2.a, 2.b''', 3, 4)=||
  • $GETLIST(test)=|1, 2, 2.a, 2.b, 3, 4|
  • $LISTJOIN(; , $GETLIST(test))=|1; 2, 2.a, 2.b; 3; 4|
  • $SETLIST(novalue)=||
  • $GETLIST(novalue)=||
  • $SETLIST()=||

Actual

  • $SETLIST(test, 1, '''2, 2.a, 2.b''', 3, 4)=||
  • $GETLIST(test)=|1, 2, 2.a, 2.b, 3, 4|
  • $LISTJOIN(; , $GETLIST(test))=|1; 2, 2.a, 2.b; 3; 4|
  • $SETLIST(novalue)=||
  • $GETLIST(novalue)=||
  • $SETLIST()=||

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 SETMHASH

Expected

  • $SETHASH(count)=||
  • $LISTJOIN(, $LISTEACH($SETMHASH(count, $item, +1), Anna, Jane, Berta, Charlie, Jane, Tom, Anna, Jane))=||
  • $HASH2LIST(count, $key: $value)=|Anna: 2, Berta: 1, Charlie: 1, Jane: 3, Tom: 1|
  • $SETMHASH(count, Jane, +1)=||
  • $SETMHASH(count, Jane)=||
  • $HASH2LIST(count, $key: $value)=|Anna: 2, Berta: 1, Charlie: 1, Jane: 4, Tom: 1|

Actual

  • $SETHASH(count)=||
  • $LISTJOIN(, $LISTEACH($SETMHASH(count, $item, +1), Anna, Jane, Berta, Charlie, Jane, Tom, Anna, Jane))=||
  • $HASH2LIST(count, $key: $value)=|Anna: 2, Berta: 1, Charlie: 1, Jane: 3, Tom: 1|
  • $SETMHASH(count, Jane, +1)=||
  • $SETMHASH(count, Jane)=||
  • $HASH2LIST(count, $key: $value)=|Anna: 2, Berta: 1, Charlie: 1, Jane: 4, Tom: 1|

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('''Good, early morning''', morning, '''day''')=|Good, early 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('''Good, early morning''', morning, '''day''')=|Good, early 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(abcdefgh,8,5)=|h|
  • $SUBSTRING(abcdefgh,9,5)=||
  • $SUBSTRING(abcdefg,-2,2)=|fg|
  • $SUBSTRING(abcdefg,-1,2)=|g|
  • $SUBSTRING(abcdefg,-7,2)=|ab|
  • $SUBSTRING(abcdefg,-8,2)=||
  • $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(abc,def,3,3)=|c,d|
  • $SUBSTRING(abcdefg)=||
  • $SUBSTRING()=||

Actual

  • $SUBSTRING(abcdef,3,5)=|cdef|
  • $SUBSTRING(abcdefgh,3,5)=|cdefg|
  • $SUBSTRING(abcdefgh,8,5)=|h|
  • $SUBSTRING(abcdefgh,9,5)=||
  • $SUBSTRING(abcdefg,-2,2)=|fg|
  • $SUBSTRING(abcdefg,-1,2)=|g|
  • $SUBSTRING(abcdefg,-7,2)=|ab|
  • $SUBSTRING(abcdefg,-8,2)=||
  • $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(abc,def,3,3)=|c,d|
  • $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))=|2024-12-27|

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))=|2024-12-27 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|

  • Set EDITMETHOD = raw

Related Topics: SpreadSheetPlugin, VarCALC, VarCALCULATE

-- TWiki:Main.PeterThoeny - 2014-10-24

Edit | Attach | Watch | Print version | History: r5 < r4 < r3 < r2 < r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r5 - 2017-10-19 - TWikiContributor
 

Copyright © 1999-2024 by the contributing authors. All material from TWiki is the property of the contributing authors.
Questions, comments, or concerns? Contact GNHLUG.
All use of this site subject to our Legal Notice (includes Terms of Service).