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(-100)=|11111111111111111111111110011100|
$DEC2BIN()=|0|

Actual

$DEC2BIN(9)=|1001|
$DEC2BIN(9, 6)=|001001|
$DEC2BIN(-100)=|1111111111111111111111111111111111111111111111111111111110011100|
$DEC2BIN()=|0|

Function DEC2HEX

Expected

$DEC2HEX(165)=|A5|
$DEC2HEX(100, 4)=|0064|
$DEC2HEX(-54)=|FFFFFFCA|
$DEC2HEX()=|0|

Actual

$DEC2HEX(165)=|A5|
$DEC2HEX(100, 4)=|0064|
$DEC2HEX(-54)=|FFFFFFFFFFFFFFCA|
$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 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, 27)=||
$HASHREVERSE(age)=||
$HASH2LIST(age, $key: $value)=|26: Jane, 27: Tim|
$HASHREVERSE(height)=||
$HASH2LIST(height)=||
$HASHREVERSE()=||

Actual

$SETHASH()=||
$LIST2HASH(age, Jane, 26, Tim, 27, Sam, 27)=||
$HASHREVERSE(age)=||
$HASH2LIST(age, $key: $value)=|26: Jane, 27: Tim|
$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, Jane, 27, Zoltan)=||
$GETHASH(age, Jane)=|27|
$GETHASH(age, Tim)=|27|
$GETHASH(age)=|Anna, Jane, Tim|

Actual

$SETHASH()=||
$LIST2HASH(age, Jane, 26, Tim, 27)=||
$GETHASH(age, Jane)=|26|
$GETHASH(age, Tim)=|27|
$LIST2HASH(age, Anna, 25, Jane, 27, Zoltan)=||
$GETHASH(age, Jane)=|27|
$GETHASH(age, Tim)=|27|
$GETHASH(age)=|Anna, 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|

Actual

$LISTEACH($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)=|Apple, Apple, Orange, Kiwi|

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 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|

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)=|5.73262961106117, 4.65650084691323, 4.34609917539788|
Manual test: $RAND(), $RAND(), $RAND()=|0.141051753031658, 0.0176576213854034, 0.635710857314567|

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(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(abc,def,3,3)=|c,d|
$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(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))=|2025-04-25|

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-25 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 - 2013-03-20

Revision: r3 - 2014-01-23 - 13:24:34 - TWikiContributor

Copyright © 1999-2025 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Daya Bay? Send feedback
Note: Please contribute updates to this topic on TWiki.org at TWiki:TWiki.SpreadSheetPluginTestCases.