Re: [office-formula] Treatment of argument separators

[Eike Rathke <erack@sun.com>]

Hi Eric,

On Thursday, 2010-06-03 01:06:14 +0000, Eric Patterson wrote:

> Current drafts of part 2 don't seem to specify how to handle optional
> parameters when the preceding argument separator is used or not.

For a few functions where it matters we explicitly defined the syntax to
allow empty parameters (two consecutive ;; semicolons) and describe them
in semantics, for example INDEX() where the Row or Column parameters may
be empty:

INDEX( ReferenceList|Array DataSource ; [ Integer Row ] [ ; [ Integer Column ] ] [ ; Integer AreaNumber = 1 ] )

For the usual syntax (optional parameters listed as [;param])
a separator may not be present if the parameter is not.


> For example, using VLOOKUP with its optional 4th parameter, Excel
> produces the same result when an argument separator is used prior to
> an optional argument as when it is not.  Calc produces different
> results.
> 
> In Excel
> Formula				Result
> =VLOOKUP(2,{1,"a";2,"b";3,"c"},2,)	b
> =VLOOKUP(2,{1,"a";2,"b";3,"c"},2)	b
> 
> In Open Office Calc
> VLOOKUP(2;{1;"a"|2;"b"|3;"c"};2;)	Err:511
> VLOOKUP(2;{1;"a"|2;"b"|3;"c"};2)	b
> 
> Should we call this out as implementation dependent?

No. Excel, when storing to OpenFormula, would have to either write the
missing optional parameter (here TRUE() in this example) or omit the
separator.

  Eike

-- 
Automatic string conversions considered dangerous. They are the GOTO statements
of spreadsheets.  --Robert Weir on the OpenDocument formula subcommittee's list.

PGP signature