OpenDocument-formula-20090501.odt Details

Document Details     TC Member Document View
Title OpenDocument Formula Specification 2009-05-01 (ODT)
Name * OpenDocument Formula Specification 2009-05-01 (ODT) (993K)
Description OpenFormula is an open format for exchanging recalculated formulas between office application implementations, particularly for spreadsheets. OpenFormula defines the types, syntax, and semantics for calculated formulas, including many predefined functions and operations, so that formulas can be exchanged between applications and produce substantively equal outputs when recalculated with equal inputs. Both closed and open source software can implement OpenFormula.

OpenFormula is intended to be a supporting document to the Open Document Format for Office Applications (OpenDocument) format, particularly for defining its attributes table:formula and text:formula. It can be used in other circumstances where a simple, easy-to-read infix notation is desired for exchanging recalculated formulas.
Group OpenDocument - Formula SC
Folder Standards
Submitter Mr. Eike Rathke
Date Submitted Friday, 01 May 2009 06:22am
Document State Draft (A preliminary unapproved sketch, outline, or version.)
Access This document is visible to OpenDocument - Formula SC and shared with:
  • OASIS Open (General Membership)
  • General Public
  • OASIS Open Document Format for Office Applications (OpenDocument) TC

Document Revisions
Name # State Submitter Date Action
98
Draft
Michael Brauer
2010-07-26
97
Draft
Michael Brauer
2010-05-31
96
Draft
Michael Brauer
2010-05-27
95
Draft
Michael Brauer
2010-05-12
94
Draft
Mr. Eike Rathke
2010-05-11
93
Draft
Mr. Eike Rathke
2010-05-11
92
Draft
Mr. Eike Rathke
2010-05-06
91
Draft
Patrick Durusau
2010-05-06
90
Draft
Patrick Durusau
2010-05-03
89
Draft
Mr. Eike Rathke
2010-04-29
88
Draft
Mr. Eike Rathke
2010-04-29
87
Draft
Mr. Michael Brauer
2010-04-15
86
Draft
Mr. Eike Rathke
2010-04-15
85
Draft
Patrick Durusau
2010-04-11
84
Draft
Mr. Eike Rathke
2010-03-18
83
Draft
Patrick Durusau
2010-03-15
82
Draft
Patrick Durusau
2010-02-18
81
Draft
Mr. Eike Rathke
2010-02-16
80
Draft
Mr. Eike Rathke
2010-02-12
79
Draft
Patrick Durusau
2010-02-08
78
Draft
Mr. Eike Rathke
2010-02-03
77
Draft
Mr. Eike Rathke
2010-01-20
76
Draft
Mr. Eike Rathke
2009-12-22
75
Draft
Mr. Eike Rathke
2009-12-04
74
Draft
Mr. Eike Rathke
2009-10-26
73
Draft
David Wheeler
2009-05-08
72
Draft
Mr. Eike Rathke
2009-05-01
This doc
71
Draft
Mr. Eike Rathke
2008-12-21
70
Draft
Mr. Eike Rathke
2008-10-10
69
Draft
Mr. Eike Rathke
2008-06-18
68
Draft
David Wheeler
2008-06-14
67
Draft
David Wheeler
2008-06-13
66
Draft
David Wheeler
2008-06-04
65
Draft
David Wheeler
2008-06-03
64
Draft
David Wheeler
2008-06-02
63
Draft
David Wheeler
2008-05-16
62
Draft
David Wheeler
2008-05-15
61
Draft
Mr. Eike Rathke
2008-05-09
60
Draft
Mr. Eike Rathke
2007-12-28
59
Draft
David Wheeler
2007-11-25
58
Draft
Mr. Eike Rathke
2007-07-20
57
Draft
Mr. David Wheeler
2007-06-20
56
Draft
Mr. David Wheeler
2007-06-19
55
Draft
Mr. Eike Rathke
2007-06-19
54
Draft
Mr. David Wheeler
2007-06-18
53
Draft
Mr. David Wheeler
2007-06-07
52
Draft
Mr. Eike Rathke
2007-04-13
51
Draft
Mr. Eike Rathke
2007-04-13
50
Draft
Mr. Eike Rathke
2007-03-29
49
Draft
Mr. David Wheeler
2007-03-23
48
Draft
Mr. David Wheeler
2007-03-22
47
Draft
Mr. David Wheeler
2007-03-17
46
Draft
Mr. David Wheeler
2007-03-09
45
Draft
Mr. David Wheeler
2007-03-02
44
Draft
Mr. David Wheeler
2007-02-22
43
Draft
Mr. David Wheeler
2007-02-14
42
Draft
Mr. David Wheeler
2007-02-13
41
Draft
Mr. David Wheeler
2007-02-09
40
Draft
Mr. David Wheeler
2007-02-08
39
Draft
Mr. David Wheeler
2007-02-01
38
Draft
Mr. David Wheeler
2007-02-01
37
Draft
Mr. David Wheeler
2007-01-25
36
Draft
Mr. David Wheeler
2007-01-21
35
Draft
Mr. David Wheeler
2007-01-17
34
Draft
Mr. David Wheeler
2006-12-22
33
Draft
Mr. David Wheeler
2006-12-20
32
Draft
Mr. David Wheeler
2006-12-08
31
Draft
Mr. David Wheeler
2006-11-29
30
Draft
Mr. David Wheeler
2006-11-22
29
Draft
Mr. David Wheeler
2006-11-01
28
Draft
Mr. David Wheeler
2006-10-25
27
Draft
Mr. David Wheeler
2006-10-18
26
Draft
Mr. David Wheeler
2006-10-11
25
Draft
Mr. David Wheeler
2006-09-29
24
Draft
Mr. David Wheeler
2006-09-23
23
Draft
Mr. David Wheeler
2006-09-20
22
Draft
Mr. David Wheeler
2006-09-14
21
Draft
Mr. David Wheeler
2006-09-13
20
Draft
Mr. David Wheeler
2006-09-10
19
Draft
Mr. David Wheeler
2006-09-06
18
Draft
Mr. David Wheeler
2006-08-30
17
Draft
Mr. David Wheeler
2006-08-23
16
Draft
Mr. David Wheeler
2006-08-16
15
Draft
Mr. David Wheeler
2006-08-16
14
Draft
Mr. David Wheeler
2006-08-13
13
Draft
Mr. David Wheeler
2006-08-11
12
Draft
Mr. David Wheeler
2006-08-09
11
Draft
Mr. David Wheeler
2006-08-04
10
Draft
Mr. David Wheeler
2006-08-02
9
Draft
Mr. David Wheeler
2006-07-31
8
Draft
Mr. David Wheeler
2006-07-24
7
Draft
Mr. David Wheeler
2006-07-20
6
Draft
Mr. David Wheeler
2006-07-19
5
Draft
Mr. David Wheeler
2006-07-18
4
Draft
Mr. David Wheeler
2006-07-16
3
Draft
Mr. David Wheeler
2006-07-14
2
Draft
Mr. David Wheeler
2006-07-13
1
Draft
Mr. David Wheeler
2006-07-11
0
Draft
Mr. David Wheeler
2006-02-21

Comments  
Subject & Text Submitter Date Action
Initial comment by submitter
LOOKUP()
- If the query is Text and the value found is Number,
#N/A is returned.

HLOOKUP(), VLOOKUP()
- In sorted range mode, if the query is Text and the
value found is Number, #N/A is returned.

MATCH()
- In ascending sorted range mode, if the query is Text
and the value found is Number, #N/A is returned.
- In descending sorted range mode, if the query is
Number and the value found is Text, #N/A is returned.

REPLACE()
- start > length => start = length, no error
- count > length - start => count = length - start, no
error

FDIST()
- If the numerator degrees of freedom (r1) is 1, then
the density function has a pole at x=0. The draft
spec does not define the return value in that case.
For x<0 it defines that the return value is 0. For
x>=0 it defines it with a term which has a subterm
x^(r1/2-1). And x^(-1/2) is not defined for x=0.
Mention that an error is to be returned in that case.
- Change ugly looking hard to read formula to better
one using {x}over{y} instead of x/y and use left( ...
right) braces.

LEGACY.FDIST()
- Change ugly looking hard to read formula to better
one using {x}over{y} instead of x/y and use left( ...
right) braces.

Test cases
- Remove all Level columns and remaining references to
Level in text.

DATEDIF()
- Test cases missing results.
Gnumeric and Excel confirmed:
=DATEDIF(DATE(1990;2;15); DATE(1993;9;15); "y") => 3
=DATEDIF(DATE(1990;2;15); DATE(1993;9;15); "d") => 1308
=DATEDIF(DATE(1990;2;15); DATE(1993;9;15); "yd") => 212

DATEDIF()
- Don't talk about "between", use the difference instead.

TIMEVALUE()
- Change test case
=TIMEVALUE("1999-11-22 06:05:07")=TIME(6;5;7)
to
=ROUND(TIMEVALUE("1999-11-22 06:05:07");10)=ROUND(TIME(6;5;7);10)

TIME()
- Test case wrong: =TIME(11;-125;-144)*60*60*24 should
not return -31956 but 31956 instead.

DateParam conversion in test cases
- Replace all implicit "YYYY-MM-DD" conversions with
DATE(YYYY;MM;DD).
- Test conversion only for DATEVALUE() test cases.

EOMONTH()
- test case
- the expression: =EOMONTH("2000-04-30";-2)=DATE(2006;2;29) should be:
=EOMONTH("2000-04-30";-2)=DATE(2000;2;29)
=EOMONTH(DATE(2000;04;30);-2)=DATE(2000;2;29)
- the expression: =EOMONTH("2006-01-05";5)=DATE(2002;6;30 should be:
=EOMONTH(DATE(2006;01;05);5)=DATE(2006;6;30)

EDATE()
- test cases
- the expression: =EDATE("2000-04-30";-2)=DATE(2006;2;29) should be:
=EDATE(DATE(2000;04;30);-2)=DATE(2000;2;29)
- the expression: =EDATE("2000-04-05";24)=DATE(2002;4;12) should be:
=EDATE(DATE(2000;04;05);24)=DATE(2002;4;5)

DAY()
- Test case, the expression, =DAY("2006-12-15") should
return 15 not 12 as indicated.
- Also, replace with =DAY(DATE(2006;12;15))

EUROCONVERT()
- Add Slovak Koruna SKK/EUR 30.1260 with 2 decimals.

PERCENTRANK()
- X_a < X < X_b: make that Y < X < Z with Z=Y+1
- Clarify that Y is the largest number smaller than
X and Z the smallest number larger than X.
- Remove "Despite its name, PERCENTRANK returns ..."

PERMUT()
- Remove P(n,k) from formula.

PERMUTATIONA()
- Add constraint of integer parameters.
Mr. Eike Rathke
2009-05-01
---