+1 -----Original Message----- From: Regina Henschel [mailto:rb.hensc...@t-online.de] Sent: Wednesday, July 13, 2011 06:14 To: users@global.libreoffice.org Subject: Re: [libreoffice-users] Error in Calc logical functions
Hi John, John B schrieb: > Dear Regina > > The way LO explains (you mention), its not a bug, but that does not mean > that it is correct either and can be classed as a false positive. > > It is not until you have practical use. LO follows the standard ODF and acts the same as main competitors. There are some aspects, (1) You can argue, that the standard should be changed to specify the behavior directly and do not leave it to the implementations. If you think so, you should write an request to OASIS. (2) You think, that LO should behave different to Excel, OOo, and Gnumeric. Then you need very good rationals. (3) You want to achieve something, but might have used a wrong way. Then you should first explain, what you want to achieve. It is likely, that you need different formula. > > For example if a box ="" (say in B1 which means empty) "box" is "cell"? If you write ="" into a cell, the cell is not empty. Test with ISBLANK() will result in FALSE and tests with ISFORMULA() and ISTEXT() will result in TRUE. It is a common error to consider an empty string as empty cell. then box stays blank > but if box A1 = a number, then a set amount appears in B1 (in business > most people do not like a page full of zero's and unused amounts > appearing for no good reason) Then they should format the cell not to show a zero. > > What you _don't_ want to happen is as per LO, if you place any Letter in > A1 then the amount shows > > eg (a very common formula) > > in B1 =IF(A1=0,"",4.5) - which happens if you put any non number (a > space) in A1 even by accident, B1 will show 4.5 (a false positive). So the formula is unsuitable, use =IF(N(A1)=0;"";4.5) for example. But as mentioned above, not showing a zero is a matter of formatting and an empty string is still a string and not a value for to use in number calculations. > > Also if you now add up the row B with false positives, that would give > the wrong answer as well. > > As mentioned before in Lotus 123 this does not happen. That is the crux with "implementation-defined". > > However, I suppose it depends on your point of view and the software you > are used too. But it does mean that in the case Alejo showed, it is a > false positive, which has to be manually formula-ed out. It is the old problem with hidden, automatic conversions and the same old rule "Never calculate with strings", but do explicit conversions before. > > I cannot see any case where the reverse would be of practical use, if a > box is expecting a number and you enter in a letter then "nothing" > should be the result - In which case its a bug - unless - you know better? If you want a cell to only expect a number, you have to set up the cell accordingly before entering something, otherwise a cell expects all input. kind regards Regina -- Unsubscribe instructions: E-mail to users+h...@global.libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- Unsubscribe instructions: E-mail to users+h...@global.libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
