2009/1/16 Brian Barker <[email protected]>: > 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.
<snip> > 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. Brian, Thank you. I had not realised that the 'hidden' cells in a merged block would influence the answer. I have cleaned out the 'hidden cells' and now the formula works as expected. I also did not realise that I didn't need the SUM function as I thought all formula had to have a function before the brackets. I may edit it but at the moment the SUM function is doing no harm there. Thanks again Regards Hylton --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
