[Libreoffice-bugs] [Bug 105394] New: Strange error in mean calculation

[b***@bugs.documentfoundation.org]

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

Bug ID: 105394
Summary: Strange error in mean calculation
Product: LibreOffice
Version: 5.2.4.2 release
Hardware: x86-64 (AMD64)
OS: Linux (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: libreoffice-***@lists.freedesktop.org
Reporter: ***@gmail.com

Description:
I downloaded a file from Google Sheet (sorry, can’t link it, but see after),
which basically was just a minimal template of table with 6 rows, the 6th one
being the mean of the five firsts.

I then filed it locally using Calc. The mean value of the first line appeared
very strange to me, because I already had calculated it by other means, and it
wasn’t the same. So, I did copy by hand the content on the second line, and got
the correct result.

I narrowed it down to the first cell having a strange format, and behaving
differently if the number is round or not. See attached minimal non-working
example.

I haven’t found any thing special in formatting or else that could explain
this, but even so, I’m especially concerned about the fact this could go
undetected, and I wouldn’t have if hadn’t done the calculation myself
beforehand. So, file a bug as major severity.

I’ll try to be as helpful as I can regarding access to the original doc, but I
can’t promise anything.

Steps to Reproduce:
1. Open the file.
2. Mess around with A2 content.

Actual Results:
C2/D2 have weird output.

Expected Results:
Same as C3/D3.


Reproducible: Always

User Profile Reset: No

Additional Info:


User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:53.0) Gecko/20100101
Firefox/53.0

[b***@bugs.documentfoundation.org]

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

Bruno Pagani <***@gmail.com> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@gmail.com

--- Comment #1 from Bruno Pagani <***@gmail.com> ---
Created attachment 130501
--> https://bugs.documentfoundation.org/attachment.cgi?id=130501&action=edit
Minimal non-working example

[b***@bugs.documentfoundation.org]

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

--- Comment #2 from Bruno Pagani <***@gmail.com> ---
Small update: changing the comma to a dot in cell A2 fix this. Don’t know why
this cell behave this way, neither why 9.25 and 9,25 aren’t treated both as
numbers.

Still an issue however, because it’s abnormal such things can happen without
being noticed. Maybe there should be at least a warning if a cell in some area
doesn’t have the same numerical format as its surroundings or something like
that.

Thanks for considering this. ;)

[b***@bugs.documentfoundation.org]

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

--- Comment #3 from Bruno Pagani <***@gmail.com> ---
Plus I don’t know what it would do if someone else opens it with either Calc —
but you’re going to tell me that — or any other spreadsheets software
 Will it
work with the dot or the comma? Both? Neither?

[b***@bugs.documentfoundation.org]

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

--- Comment #4 from ***@gmail.com ---
The reason is that A2 has the number format language English (USA).

[b***@bugs.documentfoundation.org]

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

--- Comment #5 from Bruno Pagani <***@gmail.com> ---
I tried switching it to French format, with no result.

[b***@bugs.documentfoundation.org]

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

--- Comment #6 from ***@gmail.com ---
You have to input the value again.

[b***@bugs.documentfoundation.org]

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

--- Comment #7 from Bruno Pagani <***@gmail.com> ---
OK, so switching to french format added a ' at cell value beginning, removing
it made it work.

So, is this considered to be an issue with the person who created the document,
with Google or whatever?

Also, maybe the warning I mentioned should then be when they are cells in same
area with different number format.

[b***@bugs.documentfoundation.org]

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

Buovjaga <***@suomi24.fi> changed:

What |Removed |Added
----------------------------------------------------------------------------
Status|UNCONFIRMED |RESOLVED
CC| |***@suomi24.fi
Resolution|--- |NOTABUG

--- Comment #8 from Buovjaga <***@suomi24.fi> ---
Ok, I guess this can be closed.

[b***@bugs.documentfoundation.org]

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

--- Comment #9 from Bruno Pagani <***@gmail.com> ---
Well, I don’t agree, because if I hadn’t noticed the wrong result in my
original file (thanks to having calculated it before hand), it would have been
accepted as is and that would have had real consequences.

I think a warning should be issued if one of the cells included in the
computation has a value of any kind in it that is not taken into account in the
result.

I’ll let the decision to reopen to you, but without this being fixed it
basically means I should never trust a calc file.

[b***@bugs.documentfoundation.org]

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

Buovjaga <***@suomi24.fi> changed:

What |Removed |Added
----------------------------------------------------------------------------
Status|RESOLVED |UNCONFIRMED
Resolution|NOTABUG |---
Summary|Strange error in mean |A warning should be issued
|calculation |if one of the cells
| |included in the computation
| |has a value of any kind
| |that is not taken into
| |account in the result
OS|Linux (All) |All
Severity|normal |enhancement

[b***@bugs.documentfoundation.org]

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

--- Comment #10 from Bruno Pagani <***@gmail.com> ---
Thanks for reopening and considering this.

[b***@bugs.documentfoundation.org]

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

Xisco Faulí <***@libreoffice.org> changed:

What |Removed |Added
----------------------------------------------------------------------------
Ever confirmed|0 |1
CC| |***@libreoffice.org
Status|UNCONFIRMED |NEEDINFO

--- Comment #11 from Xisco Faulí <***@libreoffice.org> ---
Dear Bruno Pagani,
Could you please try to reproduce it with the latest version of LibreOffice
from https://www.libreoffice.org/download/libreoffice-fresh/ ?
I have set the bug's status to 'NEEDINFO'. Please change it back to
'UNCONFIRMED' if the bug is still present in the latest version.

[b***@bugs.documentfoundation.org]

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

Bruno Pagani <***@gmail.com> changed:

What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |UNCONFIRMED
Ever confirmed|1 |0

--- Comment #12 from Bruno Pagani <***@gmail.com> ---
Yes it is still present, just open my attachment in #c1 and you should see it
by yourself.

[b***@bugs.documentfoundation.org]

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

--- Comment #13 from Xavier Van Wijmeersch <***@gmail.com> ---
Created attachment 147395
--> https://bugs.documentfoundation.org/attachment.cgi?id=147395&action=edit
cell A2 is formatted with English (USA) local setting

In my opinion there is no bug at all and its working as expected when all cells
have the same local setting exp in my case Dutch(Belgium).
Cell A2 is formatted with the English(USA) local setting, see attachment
It should be closed as WFM

Best regards

[b***@bugs.documentfoundation.org]

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

--- Comment #14 from Bruno Pagani <***@gmail.com> ---
Yes, it works if the locale setting is correct for all cells, and since this
has been understood the issue has been about how this could go unnoticed and
have adverse effects. For instance, my case was for the grade of students, and
the effect would have been for their final grade to not take into account the
first exam of the year. And it this precise case, that was enough for the
student to pass or not. It would have gone unnoticed if I hadn’t computed the
mean of grades independently.

All I’m asking for is to issue a warning to the user when they apply any
formula on cells with different locale settings amongst them, or maybe even
better to issue a warning when some non-empty cells are not considered for
computation in the selected area. Because visually, there is nothing that tells
you the first cell has a wrong format.

[b***@bugs.documentfoundation.org]

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

--- Comment #18 from Bruno Pagani <***@gmail.com> ---
(In reply to Buovjaga from comment #17)

This is in a way similar to bug 92419, which was recently closed as WONTFIX.
UX team: do you agree that this should be closed as well?

They are similarities for sure, but you won’t be able to close it for the same
reason: “an user deleting things should know what they do”
 How are you going
to translate this here?

I mean especially, in this other bug, you actually have an error shown
somewhere (and that is here because of something you did). Here the main issue
is that this might go unnoticed!

[b***@bugs.documentfoundation.org]

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

m.a.riosv <***@libreoffice.org> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@libreoffice.o
| |rg

--- Comment #19 from m.a.riosv <***@libreoffice.org> ---
I'm not for that, It implies analyze if it is a value as text for every cell,
and only can carry to reduce the calc's performance, that is currently not done
on ranges.

It can be controlled with formulas like, creating an 'Error' style.

=AVERAGE($A2:A2)+STYLE(IF(SUMPRODUCT(ISNUMBER($A2:A2))<>COLUMNS($A2:A2);"Error";""))

The same can be done with conditional format.

[b***@bugs.documentfoundation.org]

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

Buovjaga <***@suomi24.fi> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC|libreoffice-ux-***@lists |
|.freedesktop.org |
Resolution|--- |WONTFIX
Status|UNCONFIRMED |RESOLVED
Keywords|needsUXEval |

--- Comment #20 from Buovjaga <***@suomi24.fi> ---
Right, was thinking along those lines. I don't think we need to bother UX team,
let's close.

[b***@bugs.documentfoundation.org]

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

Xavier Van Wijmeersch <***@gmail.com> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@gmail.com

--- Comment #15 from Xavier Van Wijmeersch <***@gmail.com> ---
Thx for your explanation, comment 14, and yes a warning should be helpful.
Its seems more a request than a bug. Maybe asking one of the teams if it can be
done.


Best regards

[b***@bugs.documentfoundation.org]

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

Buovjaga <***@suomi24.fi> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |libreoffice-ux-***@lists
| |.freedesktop.org
Keywords| |needsUXEval

--- Comment #17 from Buovjaga <***@suomi24.fi> ---
This is in a way similar to bug 92419, which was recently closed as WONTFIX.
UX team: do you agree that this should be closed as well?

[b***@bugs.documentfoundation.org]

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

Heiko Tietze <***@gmail.com> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@gmail.com

--- Comment #21 from Heiko Tietze <***@gmail.com> ---
(In reply to Buovjaga from comment #17)

This is in a way similar to bug 92419, which was recently closed as WONTFIX.
UX team: do you agree that this should be closed as well?

Sure, WF/NAB. Only solution that comes in my mind is to have different styles
for numbers and text showing clearly that 9,25 is not 9.25. No idea if that's
feasible though as it requires some analysis and would have an impact on
performance.

[b***@bugs.documentfoundation.org]

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

--- Comment #16 from Bruno Pagani <***@gmail.com> ---
Yes, it started as a bug because I couldn’t see the issue, but was latter
re-titled and switched to “enhancement”. ;)