Date: prev next · Thread: first prev next last
2011 Archives by date, by thread · List index


Hello Regina

I have ** inlaid my answers, its good to see such a different and more apt perspective, one I am going to have to get used to.

thanks
---------------------------------------

On 13/07/2011 14:14, Regina Henschel wrote:
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.

** Yes I fully understand this, its just when I are used to Lotus 123, then I can understand why you think the way you do; and yes I do think (wish) that LO should emulate 123. In 123, in the main, does not need to do any cell tweaking, it does it all for you.

** Who is / are OASIS?


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
** I can see why you would need all the" IS xyz" functions in LO and Excel, although Excel is not something I would hold up as a good example. **even in LO "" still means empty cell, for the purposes of the sheets default mathematical calculation mechanism (not for other purposes - agreed).

**try in cells * E9* =IF(D9="","","full") and then in* F9* =IF(E9="","empty","full") then type in a number / letter into *D9* and then delete* D9 *(toggle)* *you will see what I mean .

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.

** This will not work where you have an amount showing (the false Positive) formatting it to No zero's will only help when a zero appears (how annoying)
** a new command is needed  ISFALSEPOSITIVE then ignore!


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.
** This is only unsuitable in LO (and your stated industry standards)- please accept that again in 123 it is all taken care for you. I have used 123 for nearly 20years! ** I can see I am going to have to think how the LO sheet works before:- how I want it to work.


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".

** LO / Excel "implementation-defined"(whatever that means?) is such hard work!


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.

** There is a fundamental difference between the two method of operation - which I think is the crux of the matter ** In cell say in A3 you have the formula A1+ B1 and the purpose is to add numbers (which is 99% of the time the purpose in spread sheets)
**In LO    1 + a = #value!  (error) > work needs to be done!
**In 123   1 + a = 1   (correct) > with no messing about
**Hence >/ It is the old problem........ //and the same old rule "Never calculate with strings"/ - never applied to me, as the original "old" industry standard spreadsheet took care of it.

**This is great (and good), because you can "simply" type in text in the middle (say adding up a column in D11 =Sum(d1:d10) then in D6, now if you enter Nil or Not in Stock or FOC etc, 123 simply ignored it. Try that with LO? you get the/* #value!*/ in the answer box (cell). and maybe:- Who is going to fix it? - that's why I think its a bug - not because of industry standard software, but because mathematical manipulation is a spreadsheets prime (or majority) function and therefore should ignore text until "you" tell it to do so.




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.

**In LO, I am beginning to realise that there are further consequences to this and its NOT just that "to set up the cell accordingly" needs so much extra work, its any other cells that looks at it as well.

** My (plus 10's of thousands) problem is that IBM have ditched Lotus 123 in favor of their new IBM Symphony (OOo), so I really want/need to learn LO., but at this time it feels like swapping a right hand for a left hand drive - and I have to peddle and use hand signals as well!


kind regards
Regina

**Regards   John B

--
Unsubscribe instructions: E-mail to users+help@global.libreoffice.org
In case of problems unsubscribing, write to postmaster@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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.