 |
 |
 |
      |
Optional parameters in functions 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. When using Calc functions, parameters marked as "optional" can be left out (or left empty) only when no parameter follows. For example, to calculate an annuity by using the Present value function, PV(Rate; NPER; PMT; FV; Type), without the Future Value variable (FV):
When you enter the PV function in Calc with the FV variable empty, a message box is displayed offering to change the incorrect input to “=PV(0.05;10;100;1)”:
This, too, is incorrect as it will produce a value of -772.79. The 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. Date values in financial functions In 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. Analysis Addin functions Whenever 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. For 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. The 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. Differences in function results Calc and Excel handle the missing Reference parameter with the Cell function, CELL(Info_type; Reference), differently. If Reference 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 is positioned. Functions not implemented or imported Excel functions which are not imported or implemented in Calc include INFO and GETPIVOTDATA. Excel has no equivalent for Calc’s ARABIC function which calculates the value of a Roman number. There are issues when importing both the Ceiling function, CEILING (Number; Significance; Mode), and the Floor function, FLOOR(Number; Significance; Mode). If both parameters Number and Significance 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 Mode=1 to see the same results in Excel as in Calc.
|
||||||||||||||||||||||||
