I found out, that the SUM() function will use values in merged cells which are not visible.
In example when I merge cell A1 with A2. Where A1 has a value of '9' which will be visible after the merge and A2 has a value of '8' which will be "deleted" after the merge. Maybe I've also cell A3 (value is '10') merged with A4 (value is '1') and I make the SUM function like SUM(A1:A4) the result of this SUM() is '28'. So even the invisible "deleted" values are used for the calculation of the SUM() function.
See example in attachment.
Created attachment 39043 [details]
Just FYI: There is an openoffice.org bug describing the same thing at
http://www.openoffice.org/issues/show_bug.cgi?id=78878 (no action since Feb 2008)
and related http://www.openoffice.org/issues/show_bug.cgi?id=105800 (although this involves linebreaks and merged cells)
Comment from the "upstream" bug is:
"the cells under the merged ones contain data and this data can be used in
calculations, therefore the SUM function works as expected. But is this the desired behavior ? As this is not a defect but an enhancement
request, user experience has to decide how to proceed."
The current behavior indeed causes an interop issue with other spreadsheet apps, not to mention not very intuitive. I'd say let's change it.
Taking ownership of this.
Calc internally represents a merged cell via cell attribute; the top-left cell has a "merged" flag while the rest of the cells have an "overlapped" flag. Other than that, a merged cell is internally represented as a set of individual cells with the aforementioned special flags.
With that in mind, what we need to do is when merging cells, those cells that would be overlapped need to get emptied upon merging. That way functions like SUM should behave more intuitively.
Total agree I was a little bit confused about this behavior and it took some
time to find the failure in my sheet ;) So for me it would be nice when this
will be changed.
Yes, that will help SUM(), but what about COUNT(IS_EMPTY() or similar? will they count the merged empty cells?
(The formula is wrong but you get the idea)
(In reply to comment #7)
> Yes, that will help SUM(), but what about COUNT(IS_EMPTY() or similar? will
> they count the merged empty cells?
> (The formula is wrong but you get the idea)
Hmm. That's sort of a gray area, but I would say in cases where the overlapped cells are referenced directly (i.e. A2 is referenced when A1:A2 are merged), they should behave like empty cells.
All Platforms, all OS!
Looks like the next significant release will be 3.4, not 3.3.1. Resetting the target accordingly.
I find no rule in ODF spec how functions should deal with "covered-table-cell". My personal suggestion would be, that all functions but SUBTOTAL should consider the content of a "covered-table-cell" same as a normal "table-cell". But SUBTOTAL should be extended in ODF in the way, that covered cells can be excluded as it is now for filtered cells or for collapsed cells. But that has to be discussed before writing a request to OASIS.
I disagree, that the covered cells should be emptied on merging. There are documents which use the feature that values in covered cells can be examined. For example the calendars from Bernhard Gottwald, http://www.welter.mynetcologne.de/kalender.html or http://live.prooo-box.org/de/templates/de/index.html "Kalender mit Mondphasen"
I have tested Excel and Gnumeric. They too consider the content of the covered cells, when they get a document, that has content in that covered cell. They behave the same as Calc in this regard. The difference is, that their UI does not allow to enter content in that covered cell.
I do not like to mimic this UI-as I already wrote. But we have already a dialog, if the cell, which will be covered, is not empty. What about changing our dialog in that way, that it offers three items: "shift content", "keep content in covered cell", "empty covered cell"?
Great idea Regina.
I think must be change to enchancement.
It's out of my hands for now.
Currently it's not common sense that that should be done, but IMHO a good idea.
Please read this message in its entirety before responding.
Your bug was confirmed at least 1 year ago and has not had any activity on it for over a year. Your bug is still set to NEW which means that it is open and confirmed. It would be nice to have the bug confirmed on a newer version than the version reported in the original report to know that the bug is still present -- sometimes a bug is inadvertently fixed over time and just never closed.
If you have time please do the following:
1) Test to see if the bug is still present on a currently supported version of LibreOffice (preferably 4.2 or newer).
2) If it is present please leave a comment telling us what version of LibreOffice and your operating system.
3) If it is NOT present please set the bug to RESOLVED-WORKSFORME and leave a short comment telling us your version and Operating System
Please DO NOT
1) Update the version field
2) Reply via email (please reply directly on the bug tracker)
3) Set the bug to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case)
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
LibreOffice is powered by a team of volunteers, every bug is confirmed (triaged) by human beings who mostly give their time for free. We invite you to join our triaging by checking out this link:
There are also other ways to get involved including with marketing, UX, documentation, and of course developing - http://www.libreoffice.org/get-help/mailing-lists/.
Lastly, good bug reports help tremendously in making the process go smoother, please always provide reproducible steps (even if it seems easy) and attach any and all relevant material
Still not working on LibreOffice 22.214.171.124 on Mac OSX.
Hey Steffakasid -
Thanks for the update.
I've done further tests and this is definitely inherited from OOo and was probably designed this way to be interoperable with other spreadsheets out there.
That being said - just going to update the version field (version is oldest version where we can confirm the issue).
As this is a valid enhancement - it'll take a volunteer to decide to tackle this. Probably once a year there will be a ping to verify that the issue is still present.
Bug is still present in LibO 126.96.36.199 (with Win 7 prof 64 bit)
as you can see by report #95338.
In my opinion cells, wich are covered, should not be used in function/formula. Result of LibO-calculation must be the same as by calculation in mind seein some cells an some other not. And results should be the same when using MS Excel ore other Soffice-software.
I don't see any reason to keep LibO's actual behaviour. Is there any example justifying this Point?
(In reply to QA Administrators from comment #17)
> Please read this message in its entirety before responding.
> If you have time please do the following:
> 1) Test to see if the bug is still present on a currently supported version
> of LibreOffice (preferably 4.2 or newer).
> 2) If it is present please leave a comment telling us what version of
> LibreOffice and your operating system.
(In reply to WoSch from comment #20)
> Bug is still present in LibO 188.8.131.52 (with Win 7 prof 64 bit)
> as you can see by report #95338.
It is not a bug.
> In my opinion cells, wich are covered, should not be used in
> function/formula. Result of LibO-calculation must be the same as by
> calculation in mind seein some cells an some other not. And results should
> be the same when using MS Excel ore other Soffice-software.
No one forces you to use that feature.
> I don't see any reason to keep LibO's actual behaviour. Is there any example
> justifying this Point?
See the mentioned document in my comment #11.
LibreOffice is no clone of Excel. If Excel does not have an UI for this feature, that is no reason to reduce LibreOffices capabilities.
(In reply to Regina Henschel from comment #21)
> (In reply to WoSch from comment #20)
> > Bug is still present in LibO 184.108.40.206 (with Win 7 prof 64 bit)
> > as you can see by report #95338.
> It is not a bug.
It's a bug! I have to contradict. Not because of MS Excels behaviour.
But because of experience of life. In school we've had folding boards - starting calculations on the one behind and going on with interim results
(like SUBTOTAL) on the front one. Nowadays they might use Whiteboards, copyboards or other electronic devices - but the strategiy is the same.
You will transfer interim results to a new page and contionue calculating without using terms of the deleted board again.
It's a question of well-known usabillity!
> > In my opinion cells, wich are covered, should not be used in
> > function/formula. Result of LibO-calculation must be the same as by
> > calculation in mind seein some cells an some other not. And results should
> > be the same when using MS Excel ore other Soffice-software.
> No one forces you to use that feature.
Shure am I forced to use merging cells and/or usig properly working formula/function!
> > I don't see any reason to keep LibO's actual behaviour. Is there any example
> > justifying this Point?
> See the mentioned document in my comment #11.
Please give actual links - the published do not work. I'm very interestet in seeing, how actual behaviour is deadly neccessary. I have an idea using this, but i can create same results by simple using =A1&A2&A3 or CONCATENATE or something like that.
Only if the actual behaviour of merging cells is meaningfull and important, LibO Calc needs a second Dialog "clear covered cells?".
*** Bug 95338 has been marked as a duplicate of this bug. ***
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":
tdf#30456 Enable to empty or not merged cells
It will be available in 5.3.0.
The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
Affected users are encouraged to test the fix and report feedback.
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":
tdf#30456 Add help to Merge Cells dialog
*** Bug 103122 has been marked as a duplicate of this bug. ***