From: mike scott [mailto:[EMAIL PROTECTED] Sent: Saturday, June 07, 2008 12:35 AM To: [email protected] Subject: Re: [users] Probably another stupid calc question
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! As a practical example, I've a growing spreadsheet tracking blood pressure against time - entries are added at the bottom in chronological order as they're taken, with columns for time, systolic, diastolic pressures and pulse rate. There are 6 cells showing median and spread for each data column, and a chart displaying the 3 sets of data points against time. Adding a single new entry requires changing all of the 6 cells' and the chart's data ranges. It's /tedious/, /error-prone/, and really should be easier! But I can't see an obvious and reasonable way round it. (Does Excel handle this better, btw?) -- A true database (e.g., Base) would likely handle this better. Then you'd query and skip all blank or zero or null values and perform your calculations only upon "actual" data. At least, I think this would work. Anyone else? Elchanan --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
