HELP --"flexible" formula?

[chezlinds]

I have a formula that I would like to change as rows are added above it. If the formula is in A8 then I want the formula to SUM(A2:A7), but as rows are added I want the formula to account for this (ex./ SUM(A2:A59) if the formula is bumped down to A60). Basically, I want the formula to add up A2 through A"one cell above this position".

Any help GREATLY appreciated!!

Lindsay

 

[gplhl]

Normally when you insert a line above a SUM function it will also SUM the value of the added line.

Is this not happening on your sheet?

 

[chezlinds]

If I have sum(A2:A11) in cell A12 and then I insert a row above row 12, the formula stays sum(A2:A11). If I add rows further up in the spreadsheet, the formula changes like I want, but rows are always added at the bottom, so that doesn't help me.

Lindsay
This message was edited by chezlinds on 2002-02-26 17:18

 

[anno]

sounds like you need a dynamic named range to refer to in your formula instead of a fixed range of cells. dave hawley's site at http://www.ozgrid.com has more info on how to create these, or you could check the mr excel archive pages for the procedure.

 

[Aladin Akyurek]

On 2002-02-26 17:09, chezlinds wrote:
If I have sum(A2:A11) in cell A12 and then I insert a row above row 12, the formula stays sum(A2:A11). If I add rows further up in the spreadsheet, the formula changes like I want, but rows are always added at the bottom, so that doesn't help me.

Lindsay
This message was edited by chezlinds on 2002-02-26 17:18

If you can afford putting what follows in A1, you can delete from/add to the area below A1:

=SUM(OFFSET(A2,0,0,MATCH(9.99999999999999E+307,A:A)))

You can even have this in A2, provided that you change A2 in OFFSET to A3. The reason for this change is that the first arg of OFFSET tells where the data to sum begin.

 

[anno]

oops. forgot that you can't use a dynamic range if the formula that uses it is in the same column. aladin's is a much better idea, although i don't quite get what the match function is doing.
This message was edited by anno on 2002-02-26 18:51

 

[Legacy 344]

Why not just use :-

=SUM(A2:OFFSET(A8,-1,0))

 

[Derek]

removed
This message was edited by Derek on 2002-02-26 20:25

 

[Guest]

On 2002-02-26 19:07, Derek wrote:
Hi
If you still wanted the total below your sum, you could type A2 in cell A1 (or any other cell you choose) and refer to it indirectly like this.

=SUM(INDIRECT($A$1):A7)

Regards
Derek

I don't follow that. The ref A7 in the formula does not change if a row is inserted immediately above row 8.

 

[Guest]

On 2002-02-26 19:22, Anonymous wrote:

On 2002-02-26 19:07, Derek wrote:
Hi
If you still wanted the total below your sum, you could type A2 in cell A1 (or any other cell you choose) and refer to it indirectly like this.

=SUM(INDIRECT($A$1):A7)

Regards
Derek

I don't follow that. The ref A7 in the formula does not change if a row is inserted immediately above row 8.

The formula suggested by Autolycus (entered in A8) automatcally adjusts if row(s) are inserted immediately above the cell with the formula.