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!
Created attachment 145691 [details] Sheet with error I should also note that this error occurs across multiple workbooks.
If you use "," instead of "." everything will be ok in all your sheets.
You used "." where is bold and "," elsewhere.
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:p> <text:span text:style-name="T1">576.70</text:span> </text:p> </table:table-cell> 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
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.
Created attachment 145701 [details] Illustration that LO ADDS B8 without error
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.
Now i see it clear I agree with your comments saying it needs a warning Think it can be set as new Best regards
(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 https://help.libreoffice.org/6.2/en-US/text/shared/optionen/detailedcalculation.html?DbPAR=SHARED#bm_id3146799 why do you want to make LO become incompatible with excel? https://a4accounting.com.au/handling-text-in-calculation-cells-in-excel/
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
@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
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).
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. Specifically: --) 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.