Hi Leonard, On Thursday, 2008-08-07 21:12:29 +0300, Leonard Mada wrote:
>> A1: '-3 >> A2: 4 >> >> =ABS(A1) => 3 >> =SUM(A1:A2) => 4 >> =SUM(A1,A2) => 4 >> =A1+A2 => 1 > > The 2 sensible options are: > - raise an error Which is what I favor ans want to implement. > BUT then implement a mechanism to detect these string-cells and > easily convert them to numbers And here we come back to Cor Nouws again. Cor, you wrote an extension to convert such numbers, http://extensions.services.openoffice.org/project/CT2N I didn't try the latest version with bells and whistles for different locales yet, but I assume that your extension is almost _exactly_ what we need. When Calc will generate an error for such cells when used in arithmetic operations, it would even be possible to enhance the extension to make use of the detective to track down the source of the error and point the user to original problem. I would like if we bundled your extension with the released product at some point in future, but unfortunately you released it under GPLv2 that is incompatible with LGPLv3. Any plans to change that? > - 2nd option: less adequate, but sometimes needed: > interpret everything as number, in every function No, not an option, only a source of further problems due to locale differences. > The bigger problem is however different: > *Why* do people write numbers as strings? > > This question is relatively easy to answer. By a large margin, strings > get used because the spreadsheet reformats their input in a way they do > NOT want it. Auto-detection algorithms (especially for date) play here a > role, We will have to fix some of those date recognitions, I agree, but we already discussed that on several occasions. > but also sometimes a trivial formatting may trigger the user to > use a string instead of a number. Instead of using the number formatter ... > Fixing these issues would largely > prevent the use of strings as numbers (of course not in the competitor's > applications, so one should still support the 2nd option and the first > one). Also, the first option is only feasible IF this bigger problem is > fixed, because otherwise the user can't fix his number problem. I see it different. Unless we don't introduce the first option people will claim that results are different from Excel, I consider having correct results more important than some number formatting problem. Others reasons for textual numeric input are: clipboard pastes that did not result in numeric content, e.g. because of separators not matching the current input locale without getting noticed by the user. Many occurrences also originate from 3rd party documents, several applications generating database extracts in Excel binary format preferred to write string cell records instead of numeric, maybe because they didn't know better, or it was easier because the binary file format for numeric cell records may be quite awkward. Eike -- OOo/SO Calc core developer. Number formatter stricken i18n transpositionizer. SunSign 0x87F8D412 : 2F58 5236 DB02 F335 8304 7D6C 65C9 F9B5 87F8 D412 OpenOffice.org Engineering at Sun: http://blogs.sun.com/GullFOSS Please don't send personal mail to the [EMAIL PROTECTED] account, which I use for mailing lists only and don't read from outside Sun. Use [EMAIL PROTECTED] Thanks.
pgprueMPYhwyl.pgp
Description: PGP signature