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
Created attachment 130501 [details] Minimal non-working example
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. ;)
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?
The reason is that A2 has the number format language English (USA).
I tried switching it to French format, with no result.
You have to input the value again.
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.
Ok, I guess this can be closed.
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.
Thanks for reopening and considering this.
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.
Yes it is still present, just open my attachment in #c1 and you should see it by yourself.
Created attachment 147395 [details] 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
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.
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
Yes, it started as a bug because I couldn’t see the issue, but was latter re-titled and switched to “enhancement”. ;)
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?
(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!
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.
Right, was thinking along those lines. I don't think we need to bother UX team, let's close.
(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.