On Sat, 07 Jun 2008 09:20:41 +0100 mike scott wrote: > On 7 Jun 2008 at 9:57, Johnny Rosenberg wrote: > ... > > > > 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. > > I see... thanks. I'd assumed (without checking!) that empty cell => > 0 value. Turns out charts also ignore empty cells. This feature isn't > mentioned in the help file that I can find - except that under > counta() help, it says empty cells are ignored for that function, and > for stdeva() we see that "text has the value 0" (which actually isn't > correct for a null string which is indeed ignored) > > Nevertheless, I think that giving a "generous" range is (a) > unaesthetic (b) makes assumptions about treatment of empty cells that > may not hold in general and (c) may break unexpectedly if additional > data are put into the same spreadsheet (my bp example is a case in > point) and (d) may cause problems if the user counts his own cells > for any reason. > > Messy, in other words :-( >
If it helps, you can highlight the area relevant to the calculation and give it a grey 5% background color. Clears up the range exceeded issue somewhat. It is *one* solution. It almost looks like you are scratching for reasons why you do not want to use it, so don't. The OP might like to, though. -- Michael All shall be well, and all shall be well, and all manner of things shall be well - Julian of Norwich 1342 - 1416 --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
