Q: Dynamic Sum function?

[MarkD]

Using Excel 2000.


Hi,

I know I can create a sum function "=sum(a5:a10)" which
will sum the rows 5-10 for column A. But what if I wanted
to make that dynamic so that the sum function calculates
all rows above it -- something like "=sum(a5:A[row number
above the row this cell is in])". How would I do this?

(This way, if I insert rows *at the end*, I don't have
change the sum calculation.)

 

[Frank Kabel]

Hi
try
=SUM(INDIRECT("A5:" & ROW()-1))

 

[hgrove]

Frank Kabel wrote...

try
=SUM(INDIRECT("A5:" & ROW()-1))

...

There are times when it's more appropriate to use R1C1 addressing, an
this is one.

Either

=SUM(INDIRECT("R5C1:R[-1]C1",0))

or

=SUM(INDIRECT("R5C:R[-1]C",0))

depending on whether the column (A) should be absolute (former) o
relative (latter)

 

[Guest]

=SUM(INDIRECT("A5:A" & ROW()-1))

Hi
try
=SUM(INDIRECT("A5:" & ROW()-1))

--
Regards
Frank Kabel
Frankfurt, Germany

Using Excel 2000.


Hi,

I know I can create a sum function "=sum(a5:a10)" which
will sum the rows 5-10 for column A. But what if I wanted
to make that dynamic so that the sum function calculates
all rows above it -- something like "=sum(a5:A[row number
above the row this cell is in])". How would I do this?

(This way, if I insert rows *at the end*, I don't have
change the sum calculation.)

 

[Frank Kabel]

Frank Kabel wrote...

..

There are times when it's more appropriate to use R1C1 addressing, and
this is one.

Hi Harlan
totally agree
thanks for the addition!

Frank

 

[Guest]

Nice

Frank Kabel wrote...

try
=SUM(INDIRECT("A5:" & ROW()-1))

...

There are times when it's more appropriate to use R1C1 addressing, and
this is one.

Either

=SUM(INDIRECT("R5C1:R[-1]C1",0))

or

=SUM(INDIRECT("R5C:R[-1]C",0))

depending on whether the column (A) should be absolute (former) or
relative (latter).

 

[Dana DeLouis]

If you would like to try something different, you can make a Range Formula.
For example, to Sum from Row 5 to the cell above, you can add it manually,
or have this macro add it for you to the active sheet:

ActiveWorkbook.Names.Add "SumR5", "=SUM(R5C:R[-1]C)"

In a cell, just type in "=SumR5" and it will sum from Row 5 to the cell
above.

However...the standard warning is that you can not make a copy of this sheet
without crashing Excel. (Can't handle the duplicate range name I believe).

Change R5C to R1C to Sum from Row 1, etc...

HTH
Dana DeLouis