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
- Re: [libreoffice-users] Error in Calc logical functions (continued)
- (message not available)
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.