Bug 120149 - Calc gives wrong results
Summary: Calc gives wrong results
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.1.1.2 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-09-27 08:40 UTC by Andreas Zieritz
Modified: 2019-03-31 16:09 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Testfile for checking numbers (59.94 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-09-27 08:42 UTC, Andreas Zieritz
Details
Test file after replace all dots to commas (68.90 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-09-27 09:21 UTC, Roman Kuznetsov
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Andreas Zieritz 2018-09-27 08:40:35 UTC
Description:
I've imported a text file to calc, changed the default decimal delimiter settings, and tried to sort the list. Problem is that the listing is done alphanumerical, not numerical (10, 100, 20 instead of 10, 20, 100).

Tried to find out what was going on and did some calculations.
90 / 10 = 9; 100 / 10 = 10
But 9 > 10 = false, 90 > 100 = true

Steps to Reproduce:
1. Open file
2. Check calculations in row N69
3.

Actual Results:
90 > 100 = TRUE

Expected Results:
90 > 100 = FALSE


Reproducible: Always


User Profile Reset: No



Additional Info:
Comment 1 Andreas Zieritz 2018-09-27 08:42:00 UTC
Created attachment 145206 [details]
Testfile for checking numbers

Check field N69
Comment 2 Roman Kuznetsov 2018-09-27 09:20:19 UTC
In your file all numbers have a dot as decimal separator and set up Norvegian language for cells. But in this country should be comma as decimal separator.

Replace all dots to commas and check your "problem" else one

IMHO -> NOTABUG
Comment 3 Roman Kuznetsov 2018-09-27 09:21:54 UTC
Created attachment 145207 [details]
Test file after replace all dots to commas
Comment 4 Andreas Zieritz 2018-09-27 09:29:25 UTC
We need to have dot as decimal delimiter, there are other programs that require that.

In Windows settings we have changed that setting on all PC's.

I have manually set language to english for that file.
Comment 5 Oliver Brinzing 2018-09-27 16:16:13 UTC
(In reply to Andreas Zieritz from comment #4)
> We need to have dot as decimal delimiter, there are other programs that
> require that.
> 
> In Windows settings we have changed that setting on all PC's.
> 
> I have manually set language to english for that file.

Did you change "Tools/Options.../Language Settings/Languages"
-> Locale Setting ?
Comment 6 Andreas Zieritz 2018-09-27 17:27:36 UTC
Yes, correct.
I changed that one to English / Great Britain, and checked Decimal Delititer to local setting(.).
Btw., I have no idea what it means if this checkbox is unticked.
Comment 7 Oliver Brinzing 2018-09-27 17:33:42 UTC
(In reply to Andreas Zieritz from comment #6)
> Yes, correct.
> I changed that one to English / Great Britain, and checked Decimal Delititer
> to local setting(.).
> Btw., I have no idea what it means if this checkbox is unticked.

from help:

Decimal separator key - Same as locale setting
Specifies to use the decimal separator key that is set in your system when you press the respective key on the number pad.
If this checkbox is activated, the character shown after "Same as locale setting" is inserted when you press the key on the number pad. If this checkbox is not activated, the character that your keyboard driver software provides is inserted.
Comment 8 Roman Kuznetsov 2018-09-27 17:46:57 UTC
read about locale settings for decimal separator and about problems with working in Calc when you import data from one locale to another
Comment 9 Oliver Brinzing 2018-09-27 17:54:38 UTC
ok, i changed my settings to "English (UK)" and after a restart 
i opened your test file:

numbers are displayed (e.g. B6) as text:
' 10.000000 

notice: there is a leading and trailing space,
xml representation is "string" too:

content.xml:
<table:table-cell office:value-type="string" calcext:value-type="string">
  <text:p>
  <text:s/>10.000000 </text:p>
</table:table-cell>

you can select each row and use Menu "Data/Text to Columns..." 
to convert text to numbers, or, if your source data file is a *.csv 
for example, you will see the "Text Import" dialog during opening.

tip: select Menu "View/Value Highlighting", so numbers will appear blue.
Comment 10 Andreas Zieritz 2018-09-27 18:09:44 UTC
Apparently there are 2 things going on here.

1.) Decimal delimiters
2.) Type of content

Apparently the contents of the cells are strings. The only way to see that is checking View/Value Highlighting.

But then my first question is, why can I do calculation with strings at all? As the calculations do show, I can divide the contents of the cell by 10, and do the same logical test to those results. And voila - the result suddenly is correct.

In my opinion, a string '+' of 10 + 10 should be 1010, and not 20, as it actually does. So those operations do not behave like string operations, but numeric ones.
But when it comes to > they do behave like string operations.

The other thing is that you should have a look at "Data/Text to columns...", because I really would not understand what this function would do.

But ok, if I do open this function, I can choose e.g. column B. Column type however gives me no choice to choose anything useful (standard, text, date).
Maybe I am missing something here.

Last thing: Wouldn't it be nice if LibreOffice used the Windows Decimal Delimiter setting for Windows builds?
Comment 11 Oliver Brinzing 2018-09-28 07:40:25 UTC
> But then my first question is, why can I do calculation with strings at all?

please have a look at:
https://help.libreoffice.org/Calc/Converting_Text_to_Numbers

> The other thing is that you should have a look at "Data/Text to columns...",
> because I really would not understand what this function would do.

with "Locale Setting" Englisch (UK)
- select column B
- open dialog Data/Text to columns... 
- press [OK]

this function will convert text values to float.