2008/6/6 Jerry Feldman <[EMAIL PROTECTED]>:
> 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.
>
> After getting a brilliant (as usual) answer from Brian Barker in a
different but related thread which I started for the purpose, I have come to
the following potential solution.
Put a magic text value in the *last* row of the column under consideration,
let's say A. Such a magic value used to be called a sentinel in the
computing environment in which I grew up. Make sure the sentinel always
remains in the *last* row, however many and wherever other rows get
added/deleted.
Assuming the sentinel has the value "x!x", Then a formula to manipulate the
numbers above the sentinel is
=<functionName>(INDIRECT("A1:A"&MATCH("x!x";A1:A56000)-1))
where "<functionName>" is the name of the function needed e.g. SUM, STDEV,
MIN, etc.
The MATCH function returns the row number in which the sentinel is found
within rows 1 to 56000. We need the row above this, hence the "-1".
The "&" is the text concatenation operator. It appends the just-calculated
row number to the preceding "A" so that if, for example, the row number is
93, the "&" results in "A93".
The quoted string [INDIRECT("...)] now contains, using 93 again as an
example, "A1:A93". The INDIRECT function acts as an interpreter (thanks,
Brian Barker) and allows this text string to be treated as a genuine row
range.
So, if the function is STDEV, and again using 93 as an example, the net
result is STDEV(A1:A93). As the sentinel moves (up if rows are deleted, down
if rows are added) the formula adjusts itself automatically because the
MATCH returns different results and the INDIRECT parses the current result.
--
Harold Fuchs
London, England
Please reply *only* to [email protected]
