2008/6/7, mike scott <[EMAIL PROTECTED]>: > > On 7 Jun 2008 at 7:11, Michael Adams wrote: > > > On Fri, 06 Jun 2008 09:14:50 -0400 > > Jerry Feldman wrote: > > > > > If I have a column of numbers, say A1 to A200 and a =SUM(A1:A200) > > > > > > Now, I replicate A200 down to A300. Is there any good technique I can > > > set up the sum so that it will reflect this change without manually > > > having to change is to =SUM(A1:A300). > > > > > > The actual problem I have is that I have a number of columns I need to > > > replicate as well as a number of different sums. > > > > I typically in this situation adjust the SUM calculation when i first > > set it up to go as far as i may need it in the future. If you go through > > and set it to =SUM(A1:A999), then whatever the number of cells gets > > reasonably changed too, the formula will still work. The problem you > > have then only occurs when you have a change of an order of magnitude. > > In my situation i normally put the SUM calculation at the top of the > > relevant column, or on a summary sheet. > > > Which is fine for sum(), but what about other functions, such as > median() or stddev()? You mustn't include empty cells in those! > > Yes, it works with median() and stddev(). I tested this a few seconds ago, and it gave exactly the same result for three values (located in A2:A4) for =median(A2:A4) as it did for =median(A2:A100). It just skips empty or non numerical cells. They are not calculated in any way. I also tried this with stddev() and it worked perfectly. There are also other functions (I don't know their English names) that considers empty cells having a value of 0 (zero), I think.
J.R.
