2009/1/14 Brian Barker <[email protected]>: > 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.
Yes I agree, an average is the sum of the elements divided by the number of elements. On further checking I see I made a slight error in the number of cells used to calcukate the average. The real number is 9 cells. > 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. > I trust this helps. Unfortunately not Brian, I will try and send the spreadsheet to you under separate cover. Regards Hylton --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
