Tags:
create new tag
, view all tags

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 AND

Expected

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

Actual

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

Function AVERAGE

Expected

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

Actual

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

Function BITXOR

Expected

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

Actual

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

Function CEILING

Expected

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

Actual

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

Function CHAR

Expected

  • $CHAR(97)=|a|

Actual

  • $CHAR(97)=|a|

Function CODE

Expected

  • $CODE(abc)=|97|

Actual

  • $CODE(abc)=|97|

Function COLUMN

Expected

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

Actual

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

Function COUNTITEMS

Expected

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

Actual

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

Function COUNTSTR

Expected

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

Actual

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

Function DEF

Expected

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

Actual

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

Function EMPTY

Expected

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

Actual

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

Function EVAL

Expected

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

Actual

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

Function EVEN

Expected

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

Actual

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

Function EXACT

Expected

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

Actual

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

Function EXEC

Expected

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

Actual

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

Function EXISTS

Expected

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

Actual

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

Function EXP

Expected

  • $EXP(1)=|2.71828182845905|

Actual

  • $EXP(1)=|2.71828182845905|

Function FILTER

Expected

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

Actual

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

Function FIND

Expected

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

Actual

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

Function FLOOR

Expected

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

Actual

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

Function FORMAT

Expected

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

Actual

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

Function FORMATGMTIME

Expected

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

Actual

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

Function FORMATTIME

Expected

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

Actual

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

Function FORMATTIMEDIFF

Expected

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

Actual

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

Function GET

Expected

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

Actual

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

Function HEXDECODE

Expected

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

Actual

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

Function HEXENCODE

Expected

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

Actual

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

Function IF

Expected

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

Actual

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

Function INSERTSTRING

Expected

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

Actual

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

Function INT

Expected

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

Actual

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

Function ISDIGIT

Expected

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

Actual

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

Function ISLOWER

Expected

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

Actual

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

Function ISUPPER

Expected

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

Actual

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

Function ISWIKIWORD

Expected

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

Actual

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

Function LEFT

Expected

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

Actual

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

Function LEFTSTRING

Expected

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

Actual

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

Function LENGTH

Expected

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

Actual

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

Function LIST

Expected

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

Actual

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

Function LISTIF

Expected

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

Actual

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

Function LISTITEM

Expected

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

Actual

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

Function LISTJOIN

Expected

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

Actual

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

Function LISTMAP

Expected

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

Actual

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

Function LISTNONEMPTY

Expected

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

Actual

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

Function LISTRAND

Expected

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

Actual

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

Function LISTREVERSE

Expected

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

Actual

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

Function LISTSHUFFLE

Expected

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

Actual

  • $LISTSHUFFLE(Apple, Apple, Apple)=|Apple, Apple, Apple|
  • Manual test: $LISTSHUFFLE(Apple, Orange, Apple, Kiwi)=|Orange, Apple, Apple, 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 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)=|8.38313390108393, 9.17667922185608, 6.23129916814666|
  • Manual test: $RAND(), $RAND(), $RAND()=|0.289994871359369, 0.749258067290111, 0.757258694692617|

Function REPEAT

Expected

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

Actual

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

Function REPLACE

Expected

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

Actual

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

Function RIGHT

Expected

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

Actual

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

Function RIGHTSTRING

Expected

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

Actual

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

Function ROUND

Expected

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

Actual

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

Function ROW

Expected

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

Actual

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

Function SEARCH

Expected

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

Actual

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

Function SET

Expected

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

Actual

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

Function SETIFEMPTY

Expected

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

Actual

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

Function SETM

Expected

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

Actual

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

Function SIGN

Expected

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

Actual

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

Function SPLIT

Expected

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

Actual

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

Function SQRT

Expected

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

Actual

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

Function SUBSTITUTE

Expected

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

Actual

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

Function SUBSTRING

Expected

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

Actual

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

Function SUM

Expected

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

Actual

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

Function SUMDAYS

Expected

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

Actual

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

Function SUMPRODUCT

Expected

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

Actual

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

Function T

Expected

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

Actual

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

Function TIME

Expected

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

Actual

  • $TIME(2012-12-31 GMT)=|1356912000|
  • Manual test: $TIME($FORMATTIME($TIME(), $year-$mo-$day))=|2016-07-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))=|2016-07-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 WHILE

Expected

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

Actual

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

Function WORKINGDAYS

Expected

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

Actual

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

Function XOR

Expected

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

Actual

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

Related Topics: SpreadSheetPlugin, VarCALC, VarCALCULATE

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

Topic revision: r1 - 2012-11-03 - PeterThoeny
 
  • Learn about TWiki  
  • Download TWiki
This site is powered by the TWiki collaboration platform Powered by Perl Hosted by OICcam.com Ideas, requests, problems regarding TWiki? Send feedback. Ask community in the support forum.
Copyright © 1999-2016 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.