Bug 120577 - SUM and other functions taking NumberSequence ignore Text type cells, indicator about not matching cell content could enhance user experience.
Summary: SUM and other functions taking NumberSequence ignore Text type cells, indicat...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
4.0 all versions
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Keywords: bibisected
Depends on:
Blocks: Calc-UX Calc-Function
  Show dependency treegraph
Reported: 2018-10-13 23:54 UTC by Jon R Kibler
Modified: 2020-10-24 06:17 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

PDF showing error. (17.50 KB, application/pdf)
2018-10-13 23:54 UTC, Jon R Kibler
Sheet with error (11.11 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-10-13 23:55 UTC, Jon R Kibler
Illustration that LO ADDS B8 without error (12.19 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-10-14 12:04 UTC, Jon R Kibler
Illustration that OO throws an error on additiong (30.27 KB, application/pdf)
2018-10-14 12:07 UTC, Jon R Kibler
ODS example in latest Excel (59.85 KB, image/gif)
2018-10-17 09:52 UTC, Jon R Kibler

Note You need to log in before you can comment on or make changes to this bug.
Description Jon R Kibler 2018-10-13 23:54:42 UTC
Created attachment 145690 [details]
PDF showing error.

Sum function produces an incorrect result.

See attached PDF and ODS files.

An actual cell is omitted from the calculation!
Comment 1 Jon R Kibler 2018-10-13 23:55:46 UTC
Created attachment 145691 [details]
Sheet with error

I should also note that this error occurs across multiple workbooks.
Comment 2 BogdanB 2018-10-14 05:15:06 UTC
If you use "," instead of "." everything will be ok in all your sheets.
Comment 3 BogdanB 2018-10-14 06:07:17 UTC
You used "." where is bold and "," elsewhere.
Comment 4 Oliver Brinzing 2018-10-14 07:26:16 UTC
enable Menu View/Value Highlighting:
value in cell B8 is a string, not a number

xml representation:

<table:table-cell table:style-name="ce3" 
  office:value-type="string" calcext:value-type="string">
   <text:span text:style-name="T1">576.70</text:span>

i can imagine how this can happen:

- Menu Tools/Options.../Language Settings/Languages
  change Locale setting to a locale where "," is the decimal separator
- enter all values with ","
- but enter value in cell B8 wrongly with "." 576.70
- save and close
- change Locale setting to [English (USA)]
- restart LO
- open file
- result: all values show "." but B8 is a string
Comment 5 Jon R Kibler 2018-10-14 12:03:35 UTC
Even given the previous comments, SOMETHING is not right!

Consider the updated spreadsheet, attached.

The "string" in B8 ADDS correctly, but does not SUM correctly.

At a minimum, this is incorrect behavior. Additionally, if you are trying to SUM a string, it should throw an error.

The behavior needs to be consistent! Furthermore, OO at least throws an error when you try to add the value in B8. LO should do at least the same. See second attachment.
Comment 6 Jon R Kibler 2018-10-14 12:04:59 UTC
Created attachment 145701 [details]
Illustration that LO ADDS B8 without error
Comment 7 Jon R Kibler 2018-10-14 12:07:02 UTC
Created attachment 145702 [details]
Illustration that OO throws an error on additiong

I believe the correct fix is for both addition and sum to throw a #VALUE error when encountering a string. That would result in consistent behavior that would give the clue that one of the numbers is being viewed as a string.
Comment 8 Xavier Van Wijmeersch 2018-10-14 12:30:43 UTC
Now i see it clear
I agree with your comments saying it needs a warning

Think it can be set as new

Best regards
Comment 9 Oliver Brinzing 2018-10-14 13:41:42 UTC
(In reply to Jon R Kibler from comment #7)
> I believe the correct fix is for both addition and sum to throw a #VALUE
> error when encountering a string. That would result in consistent behavior
> that would give the clue that one of the numbers is being viewed as a string.

you can adjust LO for basic operations to your needs via
Menu "Tools/Options.../LibreOffice Calc/Formula"
-> Detailed Calculation Settings

why do you want to make LO become incompatible with excel?
Comment 10 BogdanB 2018-10-15 10:59:07 UTC
What I image like a good solution to help user and also have right sum is to show an error like this:
"Your values that you sume contains also string values that are not sum up in this total. Look for example at B5 (this is not a number). You have 18 more string values in total."

And like Oliver said in comment 9 to offer also some help about what can be done.
Something like "Click here to learn more" --> https://help.libreoffice.org/6.2/en-US/text/shared/optionen/detailedcalculation.html?DbPAR=SHARED#bm_id3146799
Comment 11 Xisco Faulí 2018-10-15 12:06:03 UTC
@Eike, I thought you could be interested in this issue

The behaviour changed from showin #VALUE to show the value in the range https://cgit.freedesktop.org/libreoffice/core/log/?qt=range&q=4b4ca8030285bd66526ff5bb2b6ea5a75a6c6bc7..d59024b652ccfaf7247da113ec36788fe260de74
Comment 12 Eike Rathke 2018-10-15 14:14:03 UTC
The SUM() function never results in #VALUE! for such cases because it ignores Text type cells per definition (takes a NumberSequence). Whether arithmetic operators (+,-,*,/,...) result in #VALUE! or convert Text to Number and how depends on the settings under Tools -> Options -> Calc -> Formula, Detailed Calculation Settings, Custom, Details..., Contents to Numbers.

Adding some indicator in case a cell content does not match similar content type-wise in the same column might be helpful. There are/were other bugs mentioning such (no specific bug number at hand now).
Comment 13 Jon R Kibler 2018-10-17 09:52:33 UTC
Created attachment 145774 [details]
ODS example in latest Excel

I checked out the behavior of this issue in Excel. Note that the behavior matches neither LO nor OO exactly.

   --) OO throws value errors that neither Excel nor LO throws
   --) Excel flags the bad cell that neither LO nor OO flags

Not shown in the attached screen capture is that Excel gives a pop-up when opening the ODS file that indicates one or more cells are strings used in a numeric context.