| 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 |
|
55 |
|
|
| Excel 2003 Display (with f prefix) |
OpenOffice.org 2 Display (after f=) |
OOo2 OpenDocument format |
Excel Result (as Value) |
OOo2 Result (as Value) |
| 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='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. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| ADD MORE TESTS HERE, NOT ABOVE: |
|
|
|
|