From: mike scott [mailto:[EMAIL PROTECTED] Sent: Saturday, June 07, 2008 1:21 AM To: [email protected] Subject: Re: [users] Probably another stupid calc question
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 :-( -- In a database, you can indeed have true "null" values ... distinct from zero, space, and other surrogates for null values. Best, Elchanan --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
