Hi,
I have 22 cells merged into 11 cells. So 1 merged cell is basically 2 individual cells. When i check the average over the cells, the result is basically half. I presume that its counting all the 22 cells instead of 11. Is there any particular way this should be done, or is it a known issue.
Suppose you merged the cells of the range B2:B23 two by two, and you asked =AVERAGE(B2:B23)
Cells are never actually merged. The area of one cell just covers the others and thus hides them for the view. The cells hidden in this way may still have any content, and also numerical values. Your observation indicates that prpobably all the hidden cells contain something numerical and have the value 0. Try =COUNT(B2:B23). It may return 22, thus signaling that all the 22 cells contain a numerical entry or result.
Despite the fact that you cannot enter the hidden cells, you can use a visible cell to ask =ISNUMBER(B5) if B5 is “merged” with B4. If you get TRUE and =(B4=0) is also TRUE things should be as I supposed.
(Merging cells is a plague. If at all, you should exclusively use it for text. Cells made invisible by merging should always be cleared before the merging.)
@PYS: Thanks for the hint.
Alas! I am too silly once again. I am not even capable of understanding in what way the dialog image added to the announcement you linked to should comply with the announced improvement itself. None of the options is talking of any updates to ‘Dependents’.
Will we have confusion again like with ‘Update references when sorting ranges of cells’ (V4.2.8)?
Will the average user understand the implications better than I do? (“Merge users” might not be the most advanced Calc users.)
How will the formula @achopra started with be “updated”? Reverse engineering needed? Damn it!
I should file a feature request to create a global option to abandon all these silly smart features. I would better like, however, these features be abandoned themselves.
@Lupp Difference hide-empty seems (to me…) clearer with this dialog, but I do not have time to test for now.
What about filtering, sorting …? These tools didn’t reasonably handle ranges with merged cells to date. Lets cultivate a field for new bugs instead of fixing old ones! Growth be the motto!
The only reasonable advice concerning merging of cells is to omit it.