38
Import / export / sharing issues in Spreadsheets
Form fields
Combo boxes, list boxes and checkboxes with links to spreadsheet cells all import and export
properly, although due to font handling the number of rows displayed in list boxes may be
slightly different. List boxes in Calc can either have multi-selection or not, whereas Excel has
two multi-selection modes: multi and extend. In Excel a multi-selection listbox’s link to a
cell is ignored (at least in Excel2000; the author hasn’t checked Excel XP). In Calc the link
works, but if more than one item is selected, the cell’s value is set to #N/A.
Option buttons import into Calc, but the link with cells works differently. In Calc each option
button has its own link to a cell, setting its value to true or false depending on whether the
option is selected. In Excel the cell link returns the number of the selected option button. On
exporting option buttons to Excel, the option buttons lose their radio operation – in other
words, they are not grouped so that selecting one “unselects” the others.
Scroll bars and spinners are not implemented in Calc.
Array formulas
Calc supports array formulas (formulas entered using Control+Shift+Enter) but there are two
things which Excel can do that Calc can’t do.
In Excel it is possible to have an array formula with two ranges in the formula and Excel
matches each cell in the ranges to get a result. For example, {=SUM(IF(A2:A5=1,B2:B5))}
would work like SUMIF and sum those cells in column B where in the same row column A
contained a 1. Calc returns “Err504”. Often this can be worked around by using SUMIF,
COUNTIF, or the database functions.
Array constants are not available in OpenOffice.org. The workaround is to have the constant
values in cells on a sheet and refer to them.
Optional parameters in formulas
Some formulas have more than one optional parameter at the end of the parameter list. In
Excel it is possible to leave an optional parameter blank and specify a later parameter, but
this is not possible in Calc. Formulas with optional parameters are not correctly imported into
Calc.
Functions
Statistical, engineering and financial functions
The statistical functions return slightly different results. They are the same up to at least 5
decimal places, but after that there is a discrepancy. Gnumeric (another open source
spreadsheet package) returns exactly the same results as Excel (based on the author’s simple
tests). The author hasn’t compared the results returned by a statistical package such as R or
SAS for the statistical functions.
Sharing Files with Microsoft Office Users
13