Brian Barker wrote:
At 11:25 16/01/2009 +0200, Hylton Conacher wrote:
2009/1/14 Brian Barker:
At 10:08 13/01/2009 +0200, Hylton Conacher wrote:
I have merged a few cells and entered a SUM formula in there to
provide a numerical result. I have a column next to that that takes
the AVERAGE of all the merged cells and represents it as a single
number. I recently added a 10th merged cell to the AVERAGE formula
and the result came back that it cannot divide by zero, however the
result is far far away from zero if I calculate manually.
[...]
If you give the AVERAGE() function a set of cells that contain
nothing numerical, it will count the number of values as zero and
divide the (zero) sum by that zero to get its result - with exactly
the effect you describe. Its not clear what you are merging here,
but my guess is that you have merged a number of cells that
previously contained numerical values. There are two alternatives
when you do this: did you leave the values in the hidden cells or
did you allow the merge to move them into the visible, merged cell?
If the latter, what you now have is not any numbers but a text item,
formed by concatenating the former numerical values with intervening
spaces. An attempt to average this will find no values and
certainly give you a #DIV/0! error.
All the merged cells in question have numbers in them calculated by a
non-circular formula. These cells are then averaged again to obtain a
Mean.
It may still be that AVERAGE() is doing the right thing with what
you have given it ...
It would seem that the Average function is NOT doing the right thing,
unfortunately. I have checked and re-checked all the cell formulae
and cannot find an error, yet the moment I add the ninth average
number to the mean, I get the error that it cannot be divided by 0.
All I meant by that comment, of course, was that you hadn't given
enough information for anyone to be able to reproduce the problem and
confirm your conclusion.
Brian, I will try and send the spreadsheet to you under separate cover.
I think the problem is fairly straightforward. It seems that merged
cells do not work exactly as you may imagine (and, I have to admit, as
I would have expected them to behave). You will have noticed that my
first reply above referred to the "hidden cells" that are present when
you merge cells. What happens when you merge cells is apparently that
a single (upper, leftmost) cell expands to fill the space occupied by
the merged cells, with the other cells being not destroyed but only
hidden. If your answer to Calc's question "Should the contents of the
hidden cells be moved into the first cell?" is "No", any values in the
hidden cells remain there.
If you demerge the merged cells that you need to average, you will see
that two of them (U4 and U47) retain values in the hidden cells - four
values in one case and five in the other. The parameter you have
given in your AVERAGE() function reference is not a list of the
separate merged cells, but - reasonably enough, one might think - a
cell range (U3:U53) which thus includes all those hidden cells as well
as the visible cells you need. Even before you saw the error result,
these rogue hidden values will have been giving you incorrect results
for your average. But the bigger problem is that three of these
hidden cells contain formulae that refer to rows in which you have yet
to enter data and happen themselves to result in #DIV/0! errors. Your
AVERAGE() formula is giving a #DIV/0! error not because it is finding
itself required to divide by zero but simply because it is reporting
on - and repeating - the error values it is being asked to calculate
with.
Brilliant description.
Thanks.
--
Bill Drescher
william {at} TechServSys {dot} com
