Ask Your Question

average over merged cells

asked 2016-09-20 20:41:08 +0200


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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2016-09-21 00:20:03 +0200

Lupp gravatar image

updated 2016-09-21 00:21:28 +0200

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

edit flag offensive delete link more



FYI the future 5.3 brings a new option to clarify the operation (see release notes


pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-09-21 10:38:50 +0200 )edit

@pierre-yves samyn: 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'.

Lupp gravatar imageLupp ( 2016-09-21 11:52:18 +0200 )edit

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 gravatar imageLupp ( 2016-09-21 12:02:25 +0200 )edit

@Lupp Difference hide-empty seems (to me...) clearer with this dialog, but I do not have time to test for now.

pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-09-21 12:07:52 +0200 )edit

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.

Lupp gravatar imageLupp ( 2016-09-21 12:13:57 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-09-20 20:41:08 +0200

Seen: 130 times

Last updated: Sep 21 '16