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.
The result of the average is not particularly relevant, of course. The AVERAGE() function would not be trying to divide anything by this final result. Instead, AVERAGE() needs to divide by the number of elements it is given, of course, so the obvious possibility is that you are asking it to average a set with no elements.
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.
It would be nice to know that the functions included in Calc actually work though, without having to know a long work around.
It may still be that AVERAGE() is doing the right thing with what you have given it ...
I trust this helps. Brian Barker --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
