Description: Not sure, HOW I generated the file, which I will attach. There you will find two colums which are summed up each. The result ist WRONG. The sum checked by a pocket calculator will show you. It took me long to find out the reason: The moment, I remove the "huge" ("merged") cell formation and place them into an one-line-cell, the addition works. Not sure, how I generated this merge. However it shall never happen, that the result of a sum becomes false. Steps to Reproduce: my version is 7.0.0.2 wich is not listed abve 1. open the attached file 2. go to the very last line, which shows the 2 sum formulas 3. figure out in your head, that the results must be wrong or take a calculator Actual Results: give the correct result! (this will be done, if the "merged" cell is "solved" into a single line) Expected Results: Give at least out a warning, the sum-calculation may be fuzzy Reproducible: Always User Profile Reset: No Additional Info: figure correctly
Created attachment 168683 [details] File with sum bug shown
Could you please update to 7.0.4.2. I think it's solved already
Unmerge the cells. Then merge again. Note the dialog that pops up when you merge the cells, which asks you if you want to keep the values in hidden cells, or merge all text into one cell, or to remove it. Click "Help" button in the dialog, which will open up https://help.libreoffice.org/7.1/en-US/text/scalc/01/05060000.html, telling you that in case of "Keep the contents of the hidden cells" "the results of formulas referring to the hidden cells will not change", which means that if a SUM was referring the range with values, after merge those cells, even hidden, will still be counted in the SUM. It is a decision of spreadsheet creator to keep the values in hidden cells, thus it's expected that the decision will affect formulas accordingly. Closing NOTABUG.
Created attachment 168684 [details] Dialog screenshot
Thank you for the comments. And yes, I will upgrade and test the file. Thank you for the hint. Thank you for the workarounds, - but I refuse them. They are not the idea! Most often people will not even recognize, the result of the looong columns are wrong. It is not acceptable, that it is necessary, to check results by a pocket-calculator.
(In reply to VistaMail1 from comment #5) > Thank you for the workarounds, - but I refuse them. > They are not the idea! > > Most often people will not even recognize, the result of the looong columns > are wrong. It is not acceptable, that it is necessary, to check results by a > pocket-calculator. They are not workarounds. They are *explanation* why has that happened. And that happens *as designed*, works *as intended*. And if users use the software wrong, don't take a second to read what the dialog tells them, and in case of doubt, don't read help, it's user error, not software problem.
(In reply to VistaMail1 from comment #5) > Most often people will not even recognize, the result of the looong columns > are wrong. It is not acceptable, that it is necessary, to check results by a > pocket-calculator. @VistaMail. ' Their are multiple opinions/views. I'm pretty much on your side with this. Even though 'side' maybe wrong word. As this would create fractions. There is no clear cut good/wrong. The feedback isn't great. And this can go unnoticed. Which can give surprising results. And I'm already thinking about those who open spreadsheet from someone else. Unaware of the merging. So I'm seeing a flaw/ or room for improvement in principle. Except lacking a concrete suggestion how this could be improved in a way being helpful without being disturbing etc. Any suggestions what could be done? FWIW: the list of things which could be improved is pretty long, so and there are only so many developers.. so in any case don't expect a change soon.. Including UX in the loop
(In reply to Telesto from comment #7) > Including UX in the loop What do you expect from UX in spite of (In reply to Mike Kaganski from comment #6) > that happens *as designed*, works *as intended*... * Users want to merge cells * Merged cells should not be visible * If cells to be merged contain values, some users want to keep it other want to clear * Some users may want to count merged/hidden values other not We could a) ignore the user request and always clear data on merge (MSO does this) b) give users a clear feedback what happens and let them decide how to deal with the data (it's not even necessary to read the text on this dialog) The decision is of course b). What we could do is to mark calculations with references to hidden cells, eg per small triangle on the cell. Different topic, though.
(In reply to Heiko Tietze from comment #8) > What we could do is to mark calculations with > references to hidden cells, eg per small triangle on the cell. I would definitely welcome some decorations like that for different corner cases; note that we have multiple ways to hide cells (e.g., filtering or hiding columns etc.)... > Different topic, though. Definitely :-)
(In reply to Heiko Tietze from comment #8) > The decision is of course b). What we could do is to mark calculations with > references to hidden cells, eg per small triangle on the cell. Different > topic, though. Is there some bug report to follow?
unfortunately the bug is NOT solved. I updated today to 7.0.4.2 and the problem persists. This bug is major, because you cannot recommond any enterprise to use a software, which is not able to do correct sums! Wrong figures can mean severe consequences to an enterprise and the loss of much money because of wrong decisions.
(In reply to Mike Kaganski from comment #3) > Unmerge the cells.... It is a decision of spreadsheet creator to keep > the values in hidden cells, Still NAB
Is LibreOffice a software which wants to compete with MS? Or is it a software for bug-searcher, nerds and freaks?! You cannot give a finance-calculation software to an enterprise and say, - oh, yes, we still have some bugs, just do a workaround, perhaps you recognize, that there are merged cells, if not, oh, bad luck, then your balance sheet is wrong?! Such an attitude damages the reputation of LibreOffice! And "not a bug"?! What else? And "resolved"? - How? It is still there in the new release version. This calculation-error is a no go.
(In reply to VistaMail1 from comment #13) 1. LibreOffice goal is *not* competing with anything. It is providing a tool. 2. Read slowly and try to comprehend. Comment 2 was completely clueless, written without trying to understand your issue, and referring to a different - actual - issue, likely bug , actually fixed in latest versions. But *this* issue is *NOT* a bug. It is *intended* that if you hide cells - but not delete their contents - they are counted. It is NOT A BUG. It will be closed as such. And trying to force your point of view without understanding that there are *users* who want it work this way, and starting edit wars - is damaging, and showing your destructive attitude (which I hope you have not). The idea that such a situation needs an indication (*not* a "fix", this should keep working) - as suggested in comment 8 - needs a separate enhancement request. This one is closed. Please do not reopen. Thanks.
(In reply to Mike Kaganski from comment #14) > likely bug , I intended to mention bug 137248 there.
@VistaMail1 I'm looking at you're file right now.. which I didn't before What result did you expect in A23 and B23: as it's not totally clear what the desired result should be. I do see something which I possibility not being correct (at least what I think I would expect). But well.. comment 0 is partly bug report partly airing but not properly (objectively) in describing the expected/desired result And makes a different in order (doing merging before or after sum make sense?). What if you change different part of the column.. Not a regular calc user myself..