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.
Incidentally, there is no purpose in the SUM() function in your
previous formulae:
=SUM((P8-P4)/L8)*100
should read:
=(P8-P4)/L8*100
and so on. There are other rogue SUM() references elsewhere in the
sheet. And other errors too, I think.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
