HELP --"flexible" formula?

chezlinds · Feb 26th, 2002, 05:44 PM

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 · Feb 26th, 2002, 05:54 PM

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 · Feb 26th, 2002, 06:09 PM

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 · Feb 26th, 2002, 07:00 PM

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 · Feb 26th, 2002, 07:26 PM

Quote:

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 · Feb 26th, 2002, 07:48 PM

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 ]

Autolycus · Feb 26th, 2002, 07:57 PM

Why not just use :-

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

Derek · Feb 26th, 2002, 08:07 PM

removed

[ This Message was edited by: Derek on 2002-02-26 20:25 ]

Feb 26th, 2002, 08:22 PM

Quote:

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.

Feb 26th, 2002, 08:26 PM

Quote:

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

Quote:

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.

Feb 26th, 2002, 05:54 PM	#2
gplhl Board Regular Join Date: Feb 2002 Location: Chippenham, UK Posts: 136	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? __________________ Regards, Gary Hewitt-Long

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 26th, 2002, 05:44 PM	#1
chezlinds New Member Join Date: Feb 2002 Posts: 40	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

Feb 26th, 2002, 06:09 PM	#3
chezlinds New Member Join Date: Feb 2002 Posts: 40	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 ]

Feb 26th, 2002, 07:00 PM	#4
anno Board Regular Join Date: Feb 2002 Posts: 202	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.

Feb 26th, 2002, 07:48 PM	#6
anno Board Regular Join Date: Feb 2002 Posts: 202	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 ]

Feb 26th, 2002, 07:57 PM	#7
Autolycus Join Date: Feb 2002 Posts: 39	Why not just use :- =SUM(A2:OFFSET(A8,-1,0))

Feb 26th, 2002, 08:07 PM	#8
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	removed [ This Message was edited by: Derek on 2002-02-26 20:25 ]