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

*Subject*: **Re: [office-formula] Our next adventure: Types and conversions**

*From*:**Eike Rathke <erack@sun.com>***To*: office-formula@lists.oasis-open.org*Date*: Fri, 02 Jun 2006 00:38:07 +0200

Hi David, On Sun, May 21, 2006 at 22:02:11 -0400, David A. Wheeler wrote: > A. Logical is REQUIRED to be distinct from Number. > Inconsistent with OOo, Lotus 1-2-3, & many others. > B. Logical is REQUIRED to be the same as Number: > Inconsistent with Excel, Gnumeric. > C. "Logical" used as a notional type (so that we can easily > identify functions that take/return logicals), but we > explicitly permit EITHER of the above. > I recommend "C". If implementations can co-exist > with this variance, I think we can too. The problem is, applications can often coexist, but sometimes can't if Logical is contained in a referred range that produces a list of numeric values, e.g. =SUM(A1:A3), where Excel and Gnumeric do not sum a Logical value contained in A2. They also don't sum logical values of result arrays. They do sum however a multiplication of such array by one. Example: A1: 1 A2: =1=1 A3: 1 Formula Result =SUM(A1:A3) 2 {=SUM(A1:A3=1)} 0 {=SUM((A1:A3=1)*1)} 2 In OOoCalc all three formulas produce a result of 3. We could define Logical distinct from Number, but explicitly define implicit conversions, so that functions produce corresponding results: A Logical always implicitly converts to a Number, a Number does not implicitly convert to a Logical. Which results in ISLOGICAL(Logical)==TRUE and ISNUMBER(Logical)==TRUE, ISLOGICAL(Number)==FALSE and ISNUMBER(Number)==TRUE. With this, it would also be possible for a user to distinguish between the two different approaches if necessary, since for the Excel/Gnumeric behavior ISNUMBER(Logical)==FALSE. > Next up: how do they interact? In particular: if a function > expects a Number but gets a Text value, then what? > A. Text auto-converted to Number. Excel & Gnumeric > do this. There is a BIG PROBLEM here involving > locale... a conversion can work in one locale, & fail > in another. Furthermore there is the already mentioned difference between =SUM(A1:A2) and =A1+A2. > A1. Conversions usually work using the > "current locale" this is a problem -- the SAME spreadsheet > would work in one place and fail in another. Yes, many > implementations (e.g., Excel) really do this. > A2. An alternative would be to save the > locale of a spreadsheet on creation, and ALWAYS use that > "sheet locale" on an automatic conversion. But note that > nobody does that, so we have the risk of invention > without experience. This is not really viable. Despite from yet unforseen obstacles there may be cases where the creation of a document was done in a locale that is unknown to the application loading the document. > B. Text converted to 0. Lotus 1-2-3 does this. > C. Text converted to 0 if via reference, and auto-converted > to number if in-line. OOo 2.0 does this. > D. Text converted to error. Eike thinks this would be safest, > since it would force spreadsheet authors to notice. > I know of no one who does this. Me neither, but still it would be safest.. > E. Allow some set of the above. Spreadsheet users could > add VALUE() calls where they wanted conversions. > One problem: VALUE() doesn't have a fixed locale, it > uses the current locale (usually). We could modify the > definition of VALUE, or create VALUEL(v;locale). > (Creating a new function as a standards invention > seems less evil to me, though it's still not ideal.) We should not modify the definition of VALUE() for this. If at all, a VALUEL() function would be a clean approach, but then again, if a user would use that, why wouldn't he correct the text to a number at first hand? Which of course does not speak against the availability of VALUEL() function for general purposes. > I recommend (E) with "all of the above except A2". > I'm not sure we want to create VALUEL(), though > it'd be nice to have. You mean, to include the case of A1? How would that fit together? Doing so would keep the uncertainty of how a spreadsheet would calculate in another application, whereas a clean definition of "text doesn't calculate" allows export to any application. Eike

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