Conditional Array Calculation and Specific Array Handling

OpenOffice.org 2.0

Document - ID	Specification Owner	Last Change	Status
	Eike Rathke	2003-12-18	Final
Conforms to	
Applies to	Calc
Task ID(s)	20494,4070,4485
Category	Feature

Abstract

Conditional array calculations and some specific array/matrix handling within formulas written in other spreadsheet applications do not translate to OpenOffice.org/StarOffice because they are not supported or perform differently in OOo/SO, so calculation is lost. The features described here will eliminate the differences.

i-Team Members (The specification Owner is part of the i-Team)

	Name	E-mail Address
User Experience	Falko Tesch (ft)	ft@openoffice.org
Development	Eike Rathke (er)	er@openoffice.org
Quality Assurance	Oliver Crämer (oc)	oc@openoffice.org
Documentation	Frank Peters (fpe)	fpe@openoffice.org

Approved for Implementation

	Approved by	Date
User Experience	Falko Tesch	2003-12-18
Development	Eike Rathke	2003-12-18
Quality Assurance	Oliver Crämer	2003-12-17
Documentation	Frank Peters	2003-12-17
String Review	n/a	

Document Change History

Rev. Level	Change	Initials	Date
1.0	Initial version	er	2003-11-07
1.1	Added section 6.2, File Format Issues	er	2003-12-17
1.2	Draft -> Final	er	2003-12-18

Contents

Glossary	1

1	Motivation	1

2	User Scenarios	1

3	Goals	1

4	Requirements and Dependencies	1

4.1	Requirements	1

4.2	Technical Dependencies	1

5	Competitive Analyses	1

6	Detailed Specification	2

6.1	Error Conditions	2

6.2	File Format Issues	2

7	Future Tasks	3

8	Notes	3

9	References	3

Glossary

Term	Description
array/matrix formula	A formula completed with Shift‑Control‑Enter instead of Enter, mostly entered into a preselected rectangular area. An array/matrix formula is displayed using enclosing curly braces, {=...}
conditional array calculation	A formula using the IF() or CHOOSE() function within an array/matrix formula, where the condition argument is an area reference or a matrix result, e.g. {=IF(A1:A2<>0;"yes";"no")}

Motivation

Increase compatibility and interoperability with other spreadsheet applications.

User Scenarios

When Excel documents that use conditional array calculation are loaded in OOo1.1/SO7, the formula cells will show error values (Err:504). With some specific matrix functions a #VALUE! error is displayed instead of a result. Conditional array calculation is often used, for example, to sum values depending on other related cell content.

Goals

Eliminate differences in array/matrix formula calculation between MS-Excel and OOo/SO Calc.

Requirements and Dependencies

Requirements

<State the requirement(s) of this feature here.>

Technical Dependencies

Import/export filters will have to support the feature.

Competitive Analyses

Microsoft Excel is the reference in this case.

Detailed Specification

Enable the use of formulas such as
{=IF(A1:A3>0;"good";"bad")}
as an array formula, for example, in B1:B3, where for each cell in the range A1:A3 the test >0 is carried out, and the corresponding value ("good" or "bad") is displayed as the formula result in each cell of B1:B3 for each test:

	A	B (formula)	B (result)
1	1	{=IF(A1:A3>0;"good";"bad")}	good
2	0	{=IF(A1:A3>0;"good";"bad")}	bad
3	1	{=IF(A1:A3>0;"good";"bad")}	good

Further more detailed examples can be found in the test case documents listed under the references section.

A second aspect of the implementation is the forced array handling of some functions, which when called with area references inside their arguments automatically behave as if an array/matrix formula was entered. This because MS-Excel does it that way. Normally, a function or operator expecting a single value, when called with an area reference as argument, gets a position dependent reference of the same row or column where the formula resides. Other positions generate a #VALUE! error. Functions with forced array handling propagate array usage to all functions or operators used within their parameters, regardless of the formula's position:

	A	B (formula)	B (result)	C (forced array formula)	C (result)
1	1	=A1:A2+1	2	=SUMPRODUCT(A1:A2+1)	5
2	2	=A1:A2+1	3	=SUMPRODUCT(A1:A2+1)	5
3		=A1:A2+1	#VALUE!	=SUMPRODUCT(A1:A2+1)	5

In OOo1.1/SO7 the results in column C are the error values C1:Err504, C2:Err504, C3:#VALUE!

A comprehensive listing of all functions and their parameter classification is available from the references section.

Error Conditions

Usual error conditions of formula calculation.

File Format Issues

No changes are necessary regarding the OOo XML file format. A conditional array formula is stored as a usual array/matrix formula. Versions prior to OOo2.0 will of course not be able to interpret the formula correctly and will show error conditions, but are able to store it so that it can be properly interpreted by an OOo2.0 version again.

If a document (XML file format) that was created with an OOo1.x version is loaded and functions with a forced array handling are used, the results may differ from the results that were dsplayed in an OOo1.x version, depending on the context of and the data used within the formula, as there are no position dependent single cell references generated anymore. Again, see the parameter classification of the references section further down for a list of affected functions. However, mostly such formulas would have displayed error condition results in OOo1.x versions and were imported from other spreadsheet applications, and now when imported to OOo2.0 calculate correctly as intended.

The binary SO5.x and earlier file format versions will transport the array formula itself (the textual representation may be retrieved), but some attributes, for example the forced array handling of certain functions, are lost. Reloading such a document in an OOo2.0 version may result in erroneus calculations.

Future Tasks

The MS-Excel export filter currently doesn't support all aspects of array calculation, which will be addressed in future workspaces on the road to OOo2.0. Exported documents won't properly work when opened in MS-Excel and formulas have to be re-edited to solve it.

Notes

<Questions will arise during the writing of the specification. State these in here.>

References

OOo issues:
http://www.openoffice.org/issues/show_bug.cgi?id=4070
http://www.openoffice.org/issues/show_bug.cgi?id=4485

Q-PCD task: http://www.openoffice.org/issues/show_bug.cgi?id=20494

Developer's test case documents:
http://specs.openoffice.org/calc/compatibility/cac/conditional_array_calculation_test_cases.xls
http://specs.openoffice.org/calc/compatibility/cac/i4070_i16989_example.xls

Parameter classification of all functions and operators:
http://specs.openoffice.org/calc/compatibility/cac/parameter_classification.txt

Required - Give references to related documentation, such as engineering designs, technical specs, and other sources for related units. Also include links to documents that other sections in this spec need to refer to, such as user opinions usability test reports.

Conditional Array Calculation and Specific Array Handling

Page 3

http://specs.openoffice.org/calc/compatibility/cac/conditional_array_calculation.sxw

Conditional Array Calculation and Specific Array Handling

Eike Rathke

2003-11-07T18:00:17