OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help

office-formula message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

Subject: Re: [office-formula] Conversion to Logical from Text

On Tuesday, 2007-03-20 16:28:17 -0500, David A. Wheeler wrote:

> Eike:
> > | Text, level 3 and higher applications examine the text; if the text
> > | matches the text "TRUE" ignoring case, then return TRUE, if the text
> > | matches the text "FALSE" ignoring case, then return FALSE, otherwise
> > | return Error. Note that case is ignored no matter what the value of
> > | case-sensitive is.  It is implementation-defined what happens if the
> > | text does not match TRUE or FALSE in a case-sensitive manner- it may
> > | return a logical value or an Error.
> > 
> > I think that's wrong and even worse than trying to interpret text as
> > numbers because it will definitely not work in different locales.
> > Instead, we should define that similar to "Conversion to Number", e.g.
> > 
> > Text: An application may return FALSE, an error value, or the results of
> > its attempt to convert the text value to a logical (and fall back to
> > FALSE or error if it fails to do so). Note that portable spreadsheet
> > files cannot depend on any particular conversion, and shall avoid
> > implicit conversions from text to logical.
> Okay, but please capture in a NOTE that some applications implement the algorithm above.  Some implementors may want to implement that algorithm for backwards-combatibility (sic) with really nasty spreadsheets.

Actually the problem is even worse. Assuming A1 has text "false", A2
"true", A3 "blah":

For AND(), Excel and OOo convert inline text to logical, but ignore (!)
textual cell content, =AND(A1) is error because of no logical value at
all, =AND(A1;1) gives TRUE. Gnumeric converts any text to logical TRUE,
both =AND(A1) and =AND(A1;1) give TRUE, as do =AND("blah") and
=AND("false") and =AND(A2;1) and =AND(A3;1). Kspread generates an error
for everything.

For IF(), Excel and Gnumeric convert inline text and textual cell
content to logical, so =IF(A1;TRUE();FALSE()) gives TRUE,
=IF(A2;TRUE();FALSE()) gives FALSE and =IF(A3;TRUE();FALSE()) gives
#VALUE!  OOo gives FALSE for every textual cell content, but inline text
is converted to logical if it is "true" or "false", other inline text
generates an error. Kspread generates an error for everything.

I think the latter is the usual behavior for all conversions except for
arguments to AND and OR (others?).

Gee, I'm really sick of all this conversion mess. So, how do we define
it? I think my previous proposal would be the cleanest definition
according to how applications currently behave, and adding the behavior
to the Notes may give some insight to how they do it. However,
personally I think that inline text and textual cell content should
generate an error as the recommended behavior, we just can't demand it,
I think.

Shall we define the exceptional behavior of ignoring textual cell
content for AND and OR and maybe XOR as required? I think so.


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

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]