[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

b.  changed:

   What|Removed |Added

 CC||newbie...@gmx.de

--- Comment #19 from b.  ---
@Bill Babb ... and others: 'SUM' is sensitive to the order of operands,
'=SUM(1E16; -1E16; 1)' -> 1 , '=SUM(1; 1E16; -1E16)' -> 0, 
it's an effect of 'cancellation' and will truncate small operands in
subtraction to different degree (between 'nearly nothing' and 'total
extinction', 
calc and excel process ranges with different strategies, thus expect
deviations,

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

andis.lazd...@gmail.com changed:

   What|Removed |Added

 Status|NEW |RESOLVED
 Resolution|--- |WORKSFORME

--- Comment #18 from andis.lazd...@gmail.com ---
I tried the formula on the newer version of file, where I identified the
problem, with 5.1.5.2, Ubuntu 16.04, 32 bit. It looks like problem is gone or
some kind of forgotten workaround is implemented.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

--- Comment #17 from QA Administrators  ---
** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today,
LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed
bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this
bug report. During that time, it's possible that the bug has been fixed, or the
details of the problem have changed. We'd really appreciate your help in
getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present on a currently supported version of
LibreOffice 
(5.1.5 or 5.2.1  https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the version of
LibreOffice and 
your operating system, and any changes you see in the bug behavior

If the bug is NOT present, please set the bug's Status field to
RESOLVED-WORKSFORME and leave 
a short comment that includes your version of LibreOffice and Operating System

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular
meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a
REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your
bug pertains to a feature added after 3.3)

http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to "inherited from OOo";
4b. If the bug was not present in 3.3 - add "regression" to keyword


Feel free to come ask questions or to say hello in our QA chat:
http://webchat.freenode.net/?channels=libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug-20160920

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

--- Comment #16 from Bill Babb mobil...@gmail.com ---
After many tests I've found that the problem seems related to the position of
the SUM function in the expression. When I place SUM as the first term in the
expression the result of the calculation is wrong. When I remove it and
substitute the same terms that were operands of SUM with the appropriate plus
signs, the result is correct. I can't be certain about SUM being position
sensitive but it is definitely suspect.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

--- Comment #13 from Bill Babb mobil...@gmail.com ---
Comment on attachment 113375
  -- https://bugs.documentfoundation.org/attachment.cgi?id=113375
Example of calculation approach

What's wrong with this: =B3+B4+F3+F4+F7+F8+F9+F19, all fields are defined and
have simple numbers in them. Calc gives VALUE error. SUM function gives same
result.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

--- Comment #14 from Bill Babb mobil...@gmail.com ---
Comment on attachment 113401
  -- https://bugs.documentfoundation.org/attachment.cgi?id=113401
Updated version of example with repeatable error

Your problem confirms what I suspected: Calc is inconsistent on basic
operations. Mine failed after I added the SUM function and then removed it.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

--- Comment #15 from m.a.riosv miguelange...@libreoffice.org ---
Please Bill what is the issue in your attached files?

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

--- Comment #11 from andis.lazd...@gmail.com ---
Hello!

The problem has gone after hard recalc [Shif+Ctrl+F9]. However, after saving
and reopening I got the same result as before [Shif+Ctrl+F9]. Simple
recalculation of cell content [f9] has no impact on result.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

m.a.riosv mari...@miguelangel.mobi changed:

   What|Removed |Added

 Status|NEEDINFO|NEW

--- Comment #12 from m.a.riosv mari...@miguelangel.mobi ---
Hi @Andis, I see the issue of your last comment, it happens when Autocalculate
is activated.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

--- Comment #10 from LeMoyne Castle lemoyne.cas...@gmail.com ---
Just a note in support of this report.  If confirmed, this bug may deserve a
higher priority /or severity.  

It is possible that a linked cell's current value is being taken and used
without forcing an update through the link first. Or, that the value update is
called for, but the calculation is not properly serialized by blocking the
other parts of the calculation dependent on the pending update.  Either way,
results reliant on linked data would be inconsistent and unreliable.   

I have been testing spreadsheet formulas related to other bugs and have seen
results that *may* be a symptom of serialization/dependency issues in the
formula interpreter.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

--- Comment #9 from m.a.riosv mari...@miguelangel.mobi ---
Forgive me, I didn't mention.

Shortcut = [Shif+Ctrl+F9]

It forces to recalculate all cells.

https://help.libreoffice.org/Calc/Recalculate

http://markmail.org/thread/vggjlicfxgpvi2xy

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

k...@cox.net changed:

   What|Removed |Added

 Status|RESOLVED|REOPENED
 Resolution|DUPLICATE   |---
 Ever confirmed|0   |1

--- Comment #4 from k...@cox.net ---
Andis' example is an excellent example of a real use of spreadsheets that most
programmers don't understand and can't imagine. The SUM() bug is probably
related to the same cause as bug 50299 (improper handling of significant digits
in floating point calculations); however, if it is caused by something
specifically in the implementation of the SUM() function, then its correction
in the MOD() (and DATE()) functions will not correct it in the SUM() function.

You are fortunate; you noticed that the answers were incorrect. Since I noticed
the related problem with the MOD() and DATE() functions, I have returned to
using Excel and am warning people that they could get unpleasant surprises if
they use these functions in open source spreadsheets. As I commented on 50299,
engineers, doctors and accountants cannot use spreadsheets that give random
incorrect answers when used as they would normally be expected to be used.

Does the problem still exist if the links in B3:BJ16 to values calculated in
another document are replaced with links to values calculated in another
worksheet in the example document? If so, you would be able to provide an
example that shows the problem.

I would not mark this as resolved, but as more information required (an example
that shows the problem).

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

--- Comment #5 from andis.lazd...@gmail.com ---
Created attachment 113401
  -- https://bugs.documentfoundation.org/attachment.cgi?id=113401action=edit
Updated version of example with repeatable error

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

--- Comment #6 from andis.lazd...@gmail.com ---
Hello!

It's funny, but when I tried to reconstruct formulas in example file using
another worksheet instead of linked file, I was able to repeat the error.
Result of the experiments is attached to the bug report.

It is interesting, that some values are causing error (220, 945) and some
values are calculated as they should be (375). No problems found in version
4.3.6.2. So there is working example of the problem.

Regarding use of Libreoffice in complex calculations. I would say, that it is
much, much better than excel, when there is need to use lot of sumif(),
lookup(), hlookup() and other array functions. Statistical functions like
ttest() are not limited to equal number of values in compared ranges and so on.
I can't imagine returning to Excel now; the worst case would be returning to
openoffice.org, which is much better and faster with large data ranges, because
CPU use is another madness introduced somewhere with 3.5.x version.

Some time ago there was something wrong with calculations in pivot tables and
since that I'm always checking results of calculations. But this is very
uncommon that mistakes appear in basic functions.

Best regards

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

m.a.riosv mari...@miguelangel.mobi changed:

   What|Removed |Added

 Status|REOPENED|NEEDINFO

--- Comment #7 from m.a.riosv mari...@miguelangel.mobi ---
Hi @andis have you tried with a hard recalc?.

Expanding decimal places at maximum, values in:
B 52:  220,2624382700770
B113: 4405,2487654015700
after a hard recalc:
B 52:  220,2624382700780
B113: 4405,2487654015600

this I think now gives the same results than in 4.3.6.2

Please can you confirm.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

--- Comment #8 from andis.lazd...@gmail.com ---
(In reply to m.a.riosv from comment #7)
 Hi @andis have you tried with a hard recalc?.
 
 Please can you confirm.

What is hard recalc?

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

m.a.riosv mari...@miguelangel.mobi changed:

   What|Removed |Added

 Status|UNCONFIRMED |RESOLVED
 CC||mari...@miguelangel.mobi
 Resolution|--- |DUPLICATE

--- Comment #1 from m.a.riosv mari...@miguelangel.mobi ---
Hi @andis, thanks for reporting.

I think the issue it's that really you need to set up rounded values.

Precision for calculations is limited.

Please take a look to this bug report about MOD() function:

https://bugs.freedesktop.org/show_bug.cgi?id=50299#c4.

Specially Kohei comment #4.

Please see also: https://bugs.documentfoundation.org/show_bug.cgi?id=86174

Resolved as duplicate, please if you are not agree reopen it.

*** This bug has been marked as a duplicate of bug 50299 ***

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

--- Comment #2 from andis.lazd...@gmail.com ---
Thank you for response!

The problem is actually unexpected behaviour of newer version of the program.
Our greenhouse gas inventory is built of libreoffice calc and it's predecessor
openoffice.org since 2007 and it's the first time, when I got problems with
these formulas (of course, there were other problems). It's a question of
comfortable feeling using the program - should I check only my faults or should
I check also results of calculations.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 89373] Strange behaviour of sum() function in linked or complex spreadsheet

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=89373

--- Comment #3 from m.a.riosv mari...@miguelangel.mobi ---
But if with the sample file alone, issue can't be verified, no way to find what
the issue begin.

Without a minimal sample files where to see the different behaviour between
versions. I think very difficult if there is a way to find it.

Can a be a number or formula result that now gives a periodic decimal result,
producing rounding in the last number of the precision, provoking a different
result on sum the products than the product of the total, that can explain why
with ROUND() function works properly.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs