Issue 3879

Summary: ODFF: Empty parameters in formulas
Product: Calc Reporter: arkey <romeo.koeppel>
Component: codeAssignee: AOO issues mailing list <issues>
Status: ACCEPTED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: daniel.rentz, issues, jody, pagalmes.lists, rb.henschel, rene, vdvo
Version: 641Keywords: ms_interoperability
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: ENHANCEMENT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
Table with default parameters for ALL Excel functions
none
Same list with updated LOG function none

Description arkey 2002-04-05 05:11:58 UTC
To generate legal email adresses i created folliwing formula with excel:

in Column B : Firstname
in Column C : Lastname
in Column D : Forumla

=CONCATENATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(B3;"ä";"ae");"ü";"ue");"ö";"oe");"é";"e");" ";"");".";SUBSTITUTE(SUBSTITUTE
(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(C3;"ä";"ae");"ü";"ue");"ö";"oe");"é";"e");" ";"");;"@wincor-nixdorf.ch")

In Excel, this formula works, but with OpenOffice i got the Err:511

Sincerely
Romeo Köppel
Comment 1 peter.junge 2002-04-05 09:33:50 UTC
I'll have a look, Peter
Comment 2 peter.junge 2002-04-08 16:12:13 UTC
Hi Oliver,
please check this for the excel import. Created in OOo 641d the
formula doesn't throw any error message. Returning Err:511 indicates
that a parameter is missing. Maybe import is incomplete?!
... and I see another issue. Due to this function is case sensitive it
does only convert small characters. Please check what is happening in
Excel and advice me if I should file an issue for that.
Best regards, Peter 
Comment 3 arkey 2002-04-09 06:35:54 UTC
With the hint Err:511 means parameter missing, i found out that excel 
can handle =CONCATENATE( "1";;"3") with empty second parameter. 
OpenOffice gives here the Err:511.

Excel is case sensitive. the formula works only with small 
characters. ( lower(B3) fix this )

The Err:511 means nothing. A additional text like "Err:511, missing 
parameter" would help, to find out, what's wrong.

Best regards, Romeo
Comment 4 daniel.rentz 2002-04-09 08:40:15 UTC
Hi Romeo,
> A additional text ... would help, to find out, what's wrong.
There is 
one: Select the cell and take a look to the status bar.
Daniel
Comment 5 peter.junge 2002-04-09 08:56:04 UTC
Hi Romeo,
we'll check your last comment and I guess you might be right becuse I
didn't found '...);;"@wincor...' yesterday. That maybe the reason why
import fails with an Errr:511. Oliver will check this when he can
spare some time. Unfortunally this has to fail recently because
OOoCalc formulas are not yet designed for empty parameters somewhere
in the middle. Only the last parameters can be left away.
Best regards, Peter
Comment 6 oc 2002-04-15 09:32:39 UTC
Hi Falko,
the problem here is, that OpenOffice can't handle formulas with 
missing parameters inside the parameterlist. (Excel could handle it) 
=> Enhancement
Comment 7 falko.tesch 2003-09-30 15:05:59 UTC
FT->AMA: This is an filter issues. Please evaluate. Thx.
Comment 8 andreas.martens 2003-10-01 16:03:57 UTC
Issues in Calc filter, so it's Kai's decision how to proceed.
Comment 9 ooo 2003-10-13 13:31:07 UTC
approved
Comment 10 daniel.rentz 2003-11-10 12:02:43 UTC
DR->ER: it's your area
Comment 11 ooo 2003-11-10 12:29:20 UTC
We need a definition list of how missing parameters are to be
substituted. Most times it will be numerical 0 or textual empty
string, but this is not guaranteed to be the correct substitution for
every occurence. For example, the 4th parameter of VLOOKUP() is
defaulted to TRUE, or the 3rd parameter of MATCH() is assumed to be 1.
Additionally to optional parameters, Excel even allows required
parameters to be defaulted, as long as they are explicitly specified
empty. Creating such a list is a bunch of work, one has to try out
what happens for every single parameter of each function. I would be
pleased if someone could do that, I wouldn't like to do it myself ...
=> adding needhelp keyword.
Comment 12 ooo 2003-12-12 13:37:16 UTC
*** Issue 577 has been marked as a duplicate of this issue. ***
Comment 13 maison.godard 2003-12-15 13:21:18 UTC
same problem with a starbasic user defined function using optional arguments

Laurent
Comment 14 vdvo 2004-11-18 06:30:08 UTC
The ms_interoperability keyword should be added.
Comment 15 ooo 2004-11-18 11:00:47 UTC
Added keywords ms_interoperability, easy2dev; targeted to PleaseHelp.

The first step, gather information about each and every parameter and how it
acts if missing, can be done by anyone owning an Excel software package, just
that it is a time consuming task and information is not always obvious. Note
that we need a complete and comprehensive list, partial specification is not
sufficient.

Eike
Comment 16 vdvo 2004-11-18 11:05:32 UTC
Eike: Is there any particular format that you would like to have the list in?

Vaclav
Comment 17 ooo 2004-11-18 11:37:58 UTC
Vaclav,

Either as simple text like CSV (comma separated values, just use semicolons
instead of commas to separate fields, so commas can be used in descriptions)
that can be easily imported into applications, or a OOoCalc spreadsheet ;-)
using columns for fields, listing in each row:

English function name; Param1 default value; Param2 default value; ...; comments
and descriptions

Eike
Comment 18 daniel.rentz 2005-04-04 16:26:31 UTC
*** Issue 45942 has been marked as a duplicate of this issue. ***
Comment 19 daniel.rentz 2005-04-12 09:15:40 UTC
*** Issue 47172 has been marked as a duplicate of this issue. ***
Comment 20 guzi 2005-04-12 10:17:40 UTC
We get some Excel files from other partners with the formula "=Offset(A1;;1)" -
the Error511 stops all migration plans because the ms_interoperability is not
given. 
I can not understand that this short change in the import filter (replace ";;"
by ";0;") is so difficult. 
Comment 21 ooo 2005-04-12 10:31:05 UTC
Guzi,

> I can not understand that this short change in the import filter (replace ";;"
> by ";0;") is so difficult.

Read the issue's comments, especially
------- Additional comments from er Mon Nov 10 05:29:20 -0700 2003 -------
and
------- Additional comments from er Thu Nov 18 04:00:47 -0700 2004 -------

Eike
Comment 22 guzi 2005-04-12 13:49:25 UTC
Eike,

I understand the need to have a parameter substitution table, but I assume
nobody has the time to check each formula in Excel to create a complete list.
But in this way it would never go forward with the ms_interoperability. 

Maybe everybody with an actual problem can report this one entry in the
suggested format. 

The issue 47172 would be solved with :

OFFSET (reference;rows default 0;columns default 0;height default 1;width default 1)

Guido
Comment 23 daniel.rentz 2005-05-02 17:18:19 UTC
*** Issue 48492 has been marked as a duplicate of this issue. ***
Comment 24 daniel.rentz 2005-05-09 17:27:43 UTC
Created attachment 25935 [details]
Table with default parameters for ALL Excel functions
Comment 25 daniel.rentz 2005-05-09 17:29:45 UTC
Feel free to play with the attached list of all functions and their default 
parameters.

By the way: OFFSET does not always use 1 as default for the 4th and 5th 
parameter...
Comment 26 ooo 2005-05-09 19:11:53 UTC
Thanks, Daniel!

This is f... far out GREAT!

Removing help keywords, retargeting from PleaseHelp for implementation.

  Eike
Comment 27 daniel.rentz 2005-05-12 10:05:26 UTC
Created attachment 26048 [details]
Same list with updated LOG function
Comment 28 rene 2005-05-20 10:23:07 UTC
*** Issue 49562 has been marked as a duplicate of this issue. ***
Comment 29 Joost Andrae 2006-08-07 13:25:29 UTC
*** Issue 68203 has been marked as a duplicate of this issue. ***
Comment 30 ooo 2007-11-14 21:57:49 UTC
ODFF relevant.
Comment 31 ooo 2008-06-19 18:11:42 UTC
Targeting this in its entirety to OOo3.0 was overoptimistic. Retargeting to 3.1.
Some general behavior and single functions in the meantime were changed, and
effort will go on. For example, CONCATENATE mentioned by the original submitter
and the issues that were closed as duplicates are fixed. However, it's good to
keep this issue and its attached document for complete reference.
Comment 32 Regina Henschel 2010-07-08 14:48:26 UTC
cc me
Comment 33 Marcus 2017-05-20 11:11:34 UTC
Reset assigne to the default "issues@openoffice.apache.org".