important difference between Excel and Calc regarding functions is
that the values supplied to a function (which are called arguments
in Excel and parameters in Calc) have different separators. Calc
parameters in a function.
Excel uses either commas or semicolons, depending on the system
(for example commas on English systems, semicolons on German
systems). Calc will generate a “#NAME?” error if you use a
comma in place of a semi-colon.
parameters in functions
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. When using Calc functions, parameters marked as
"optional" can be left out (or left empty) only when no
example, to calculate an annuity by using the Present value function,
PV(Rate; NPER; PMT; FV; Type), without the Future Value variable
Excel the formula =PV(0.05,10,100,,1)
Err:511 (Variable missing).
you enter the PV function in Calc with the FV variable empty, a
message box is displayed offering to change the incorrect input to
too, is incorrect as it will produce a value of -772.79.
work-around is to put values in for the missing parameters. For the
above example, putting a zero in for the blank parameter returns the
correct result for the annuity, -$810.78.
values in financial functions
Excel some of the financial functions accept string parameters for
dates; in Calc financial functions require
a serial number, although you can simply type in the dates in date
format without quotes.
an Excel Analysis ToolPak function has the same name as a Calc
function, there is an equivalent in the Calc Analysis AddIn function
but with a trailing _ADD. This ensures compatibility with Excel but
without changing the behavior of Calcs built-in functions.
example, the functions EFFECT and CONVERT provided by the Excel
Analysis ToolPak are named EFFECT_ADD and CONVERT_ADD in Calc and
return the same results.
functions whose names end with _ADD return the same results as the
corresponding Microsoft Excel functions. Use the functions without
_ADD to get results based on international standards. For example,
the WEEKNUM function calculates the week number of a given date based
on international standard ISO 6801, while WEEKNUM_ADD returns the
same week number as Microsoft Excel.
in function results
and Excel handle the missing Reference
parameter with the Cell function, CELL(Info_type;
is missing, Calc uses the position of the cell in which this formula
is located. Excel uses the reference of the cell in which the cursor
not implemented or imported
functions which are not imported or implemented in Calc include INFO
Excel has no equivalent for Calc’s ARABIC function which calculates
the value of a Roman number.
are issues when importing both the Ceiling function, CEILING
(Number; Significance; Mode), and the Floor
Significance; Mode). If both parameters Number
are negative and the Mode value is equal to zero or is not given, the
results in OpenOffice.org and Excel will differ after the import has
been completed. If you are exporting a Calc spreadsheet to Excel, use
to see the same results in Excel as in Calc.