Created attachment 39043 [details]
Example given

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.

Regards
Steffen

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:
https://wiki.documentfoundation.org/QA/BugTriage

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 4.3.2.2 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.