Formula-test2: Test Syntax and Semantics of Built-in operators
   Note: "formula-test2a" is a copy of this file, used to test inter-file operations

Excel 2003 Display	OpenOffice.org 2 Display	OOo2 OpenDocument format Excel Result  OOo2 Result

f=1 f=1 oooc:=1 1 1
f=2 f=2 oooc:=2 2 2
f="7" f="7" oooc:="7" 7 7
f="Hello" f="Hello" oooc:="Hello" Hello Hello
f=MyCellTest f=MyCellTest oooc:=MyCellTest 1 1
f='formula-test2a.xls'!MyCellTest oooc:=#NAME!MyCellTest 1 Err:508
f=2+3 f=2+3 oooc:=2+3 5 5
f="2"+"3" f="2"+"3" oooc:="2"+"3" 5 5
f="2"+3 f="2"+3 oooc:="2"+3 5 5
f=2+"3" f=2+"3" oooc:=2+"3" 5 5
f=2+C6 f=2+C6 oooc:=2+[.C6] 9 2
f=C6+2 f=C6+2 oooc:=[.C6]+2 9 2
f=2-3 f=2-3 oooc:=2-3 -1 -1
f=2 & 3 f=2 & 3 oooc:=2&3 23 23
f="a" & "b" f="a" & "b" oooc:="a"&"b" ab ab
f=2*3 f=2*3 oooc:=2*3 6 6
f=3/2 f=3/2 oooc:=3/2 1.5 1.5
f=2^3 f=2^3 oooc:=2^3 8 8
f=2>3 f=2>3 oooc:=2>3 FALSE 0
f=2<3 f=2<3 oooc:=2<3 TRUE 1
f=2<=3 f=2<=3 oooc:=2<=3 TRUE 1
f=2>=3 f=2>=3 oooc:=2>=3 FALSE 0
f=2=3 f=2=3 oooc:=2=3 FALSE 0
f=2<>3 f=2<>3 oooc:=2<>3 TRUE 1
f="2">"3" f="2">"3" oooc:="2">"3" FALSE 0
f="5">"21" f="5">"21" oooc:="5">"21" TRUE 1
f="5">21 f="5">21 oooc:="5">21 TRUE 1
f=5>"21" f=5>"21" oooc:=5>"21" FALSE 0
f="20"<3 f="20"<3 oooc:="20"<3 FALSE 0
f=20<"3" f=20<"3" oooc:=20<"3" TRUE 1
f=+C4 f=+C4 oooc:=+[.C4] 1 1
f=-C4 f=-C4 oooc:=-[.C4] -1 -1
f=+5 f=+5 oooc:=+5 5 5
f=-5 f=-5 oooc:=-5 -5 -5
f=ISTEXT("4") f=ISTEXT("4") oooc:=ISTEXT("4") TRUE 1
f=ISTEXT(4) f=ISTEXT(4) oooc:=ISTEXT(4) FALSE 0
f=ISTEXT(+"4") f=ISTEXT(+"4") oooc:=ISTEXT(+"4") TRUE 1
f=ISTEXT(-"4") f=ISTEXT(-"4") oooc:=ISTEXT(-"4") FALSE 0
f=1+2*3^4 f=1+2*3^4 oooc:=1+2*3^4 163 163
f=(1+2)*3^4 f=(1+2)*3^4 oooc:=(1+2)*3^4 243 243
f=PI() f=PI() oooc:=PI() 3.14 3.14
f=COS(0) f=COS(0) oooc:=COS(0) 1 1
f=SUM(C4,C5) f=SUM(C4;,C5) oooc:=SUM([.C4];[.C5]) 3 3
f=IF(1,5,6) f=IF(1;5;6) oooc:=IF(1;5;6) 5 5
f=SUM(C4,,C5)	oooc:=SUM([.C4];;[.C5]) 3 Err:511
f=C4 f=C4 oooc:=[.C4] 1 1
f=$C4 f=$C4 oooc:=[.$C4] 1 1
f=C$4 f=C$4 oooc:=[.C$4] 1 1
f=$C$4 f=$C$4 oooc:=[.$C$4] 1 1
f=Sheet1!C4 f=$Sheet1.C4 oooc:=[$Sheet1.C4] 1 1
f=ISREF(C4) f=ISREF(C4) oooc:=ISREF([.C4]) TRUE 1
f=ISBLANK(IF(1>2,"stuff")) f=ISBLANK(IF(1>2;"stuff")) oooc:=ISBLANK(IF(1>2;"stuff")) FALSE 0
f=ISBLANK(IF(1>2,"stuff",)) f=ISBLANK(IF(1>2;"stuff",)) oooc:=ISBLANK(IF(1>2;"stuff";0)) TRUE 0
f=Sheet1!C$4 f=$Sheet1.C$4 oooc:=[$Sheet1.C$4] 1 1
f='C:\dwheeler\misc\[formula-test2a.xls]Sheet1'!C4 f='[5]file:///C:/dwheeler/misc/formula-test2a.xls#$Sheet1.C4 oooc:=['file:///C:/dwheeler/misc/formula-test2a.xls'#$Sheet1.C4] 1 1
f=SUM(C4:C5) f=SUM(C4:C5) oooc:=SUM([.C4:.C5]) 3 3
f=8 f=8 oooc:=8 8 8
f=9 f=9 oooc:=9 9 9
f=10 f=10 oooc:=10 10 10
f=11 f=11 oooc:=11 11 11
f=SUM(C61:C62:C63) oooc:=SUM([.C61:.C62]:[.C63]) 27 Err:508
f=SUM((C61:C62):(C63:C64)) oooc:=SUM(([.C61:.C62]):([.C63:.C64])) 38 Err:508
f=SUM(C61:C62:(C63:C64)) oooc:=SUM([.C61:.C62]:([.C63:.C64])) 38 Err:508
f=SUM((C61:C62):C63:C64) oooc:=SUM(([.C61:.C62]):[.C63:.C64]) 38 Err:508
f=SUM((C4:C5,C4:C5)) oooc:=SUM(([.C4:.C5];[.C4:.C5])) 6 Err:508
f=SUM(C4:C5 C5) f=SUM(C4:C5!C5) oooc:=SUM([.C4:.C5]![.C5]) 2 2
f=SUM(A4:D4 C1:C6) f=SUM(A4:D4!C1:C6) oooc:=SUM([.A4:.D4]![.C1:.C6]) 1 1
f=SUM(A4:D4 C1:C6 C4) f=SUM(A4:D4!C1:C6!C4) oooc:=SUM([.A4:.D4]![.C1:.C6]![.C4]) 1 1
f=SUM(A4:D4 C1:C6 C5) f=SUM(A4:D4!C1:C6!C5) oooc:=SUM([.A4:.D4]![.C1:.C6]![.C5]) = #REF!
f=SUM(Sheet1:Sheet2!C4) f=SUM($Sheet1.C4:$Sheet2.C4) oooc:=SUM([$Sheet1.C4:$Sheet2.C4]) 51 51
f=SUM(Sheet1:Sheet2!C4:C5) f=SUM($Sheet1.C4:$Sheet2.C5) oooc:=SUM([$Sheet1.C4:$Sheet2.C5]) 70 70
f=AREAS((C4:C5,C4:C5,C4:C5)) f=AREAS((C4:C5;C4:C5;C4:C5)) oooc:=AREAS(([.C4:.C5];[.C4:.C5];[.C4:.C5])) 3 Err:508
f=AREAS((C4:C5 C4:C5 C4:C5)) f=AREAS((C4:C5!C4:C5!C4:C5)) oooc:=AREAS(([.C4:.C5]![.C4:.C5]![.C4:.C5])) 1 1
f=SUM(B:B) f=SUM(B$1:B$65536) oooc:=SUM([.B$1:.B$65536]) 0 0
f=SUM(2:2) f=SUM($A2:$IV2) oooc:=SUM([.$A2:.$IV2]) 55 55
f=SUM(A:B) f=SUM(A$1:B$65536) oooc:=SUM([.A$1:.B$65536]) 0 0
f=SUM((C5):(C5))
oooc:=SUM(([.C5]):([.C5])) 2 Err:508
f=SUM(Sheet1:Sheet2!C4) f=SUM($Sheet1.C4:$Sheet2.C4) oooc:=SUM([$Sheet1.C4:$Sheet2.C4]) 51 51
f=SUM('Sheet1:Space Name'!C4:C5) f=SUM($Sheet1.C4:$'Space Name'.C5) oooc:=SUM([$Sheet1.C4:$'Space Name'.C5]) 236 236
f=SUM(MyCellTest:C5) f=SUM(MyCellTest:C5) oooc:=SUM(MyCellTest:[.C5]) 3 Err:508
f=SUM(MyCellTest (C4:C6)) f=SUM(MyCellTest!(C4:C6)) oooc:=SUM(MyCellTest!([.C4:.C6])) 1 1
f=MyCellTest f=MyCellTest oooc:=MyCellTest 1 1
f=Sheet1!MyCellTest f=MyCellTest oooc:=MyCellTest 1 1
f='Sheet1 (2)'!MyCellTest f=MyCellTest_4 oooc:=MyCellTest_4 42 42
f=TRUE f=TRUE() oooc:=TRUE() TRUE 1
f=FALSE f=FALSE() oooc:=FALSE() FALSE 0
f=1/0 f=1/0 oooc:=1/0 = Err:503
f=5% f=5% oooc:=5% 0.05 0.05
f=6+50% f=6+50% oooc:=6+50% 6.5 6.5
f=NA() f=NA() oooc:=NA() #N/A #N/A
f=SUM({2,3}*{4,5})
oooc:=SUM(*) 23 #NAME?
f=SUM({2,3;4,5}*{3,4;5,6})
oooc:=SUM(*) 68 #NAME?
f={"A","B";"C","D"}&{"a","b";"c","d"}
oooc:=& Aa #NAME?
f={AVERAGE(IF(C4:C5<>1,C4:C5))} f={AVERAGE(IF(C4:C5<>1;C4:C5))} oooc:=AVERAGE(IF([.C4:.C5]<>1;[.C4:.C5])) 2 2
f=ISLOGICAL(1) f=ISLOGICAL(1) oooc:=ISLOGICAL(1) FALSE 0
f=ISNUMBER(1) f=ISNUMBER(1) oooc:=ISNUMBER(1) TRUE 1
f=ISLOGICAL(TRUE) f=ISLOGICAL(TRUE()) oooc:=ISLOGICAL(TRUE()) TRUE 1
f=ISNUMBER(TRUE) f=ISNUMBER(TRUE()) oooc:=ISNUMBER(TRUE()) FALSE 1
f=4+TRUE f=4+TRUE() oooc:=4+TRUE() 5 5
f=TRUE+4 f=TRUE()+4 oooc:=TRUE()+4 5 5
f=IF(1>2,"stuff",) f=IF(1>2;"stuff";0) oooc:=IF(1>2;"stuff";0) 0 0
f=IF(1>2,"stuff") f=IF(1>2;"stuff") oooc:=IF(1>2;"stuff") FALSE 0
f="b"*"c" f="b"*"c" oooc:="b"*"c" #N/A Err:502

NOTE: Volatile functions are ALWAYS recalculated. Walkenbach, Excel 2000, pg 108. They are AREAS, CELL, COLUMNS, INDEX, INDIRECT, NOW, OFFSET, ROWS, TODAY NOTE: Named ranges can be scoped to the sheet level, or workbook level (default:
workbook level). The same name can be used many times, once at workbook level and once per sheet. Inside a sheet, a sheet-level name takes precedence (if there is one), else use workbook level.