Formula for: Running-total for bottom portion of column?

  • Thread starter Thread starter turtleman2
  • Start date Start date
T

turtleman2

[Excel 2002] I'm looking for the formula that will calculate a runnin
total for a column from a point/cell halfway down that column
extending down to-and-including the most recent entry.
Obviously, for an entire-column running total, th
"=SUM(column_name:column_name)" would work, but I don't want to includ
the first
-n- group of cells.
Thanks for your time & attention to detail ;-}

Terry
 
J.E. McGimpsey resonded to a strikingly similar posting earier today.

Quote.........................

I'm not sure what you mean by a "running total" with only one cell
to sum in. To sum a "column total of all cells in column C"
excluding cells C1:C21:

H10: =SUM(C22:C65536)

If you want a "running" total where H10 sums C22, H11 sums C22:C23,
H12 sums C22:C24, etc, enter:

H10: =SUM(C$22:C22) and copy down.

End quote..............................

Gord Dibben XL2002
 
Gord: Thanx for the reply...2 things:
1.] I definitely want the SUM result to appear in just one cel
(e.g., H10), instead of taking up another entire column just to kee
track of one dynamic value...(&)
2.] I realize that something like " C22:C65536 -would- work, but
didn't really want Excel to have to check the entire column all the wa
to absolute worksheet-bottom =}

Isn't there some expression that would cover the effect: 'From cel
C22 all the way down to the first empty cell, wherever that occurs'?

Thanks again for the time.

Terry
 
t-man

In that case, set up a Dynamic Range Name for that column

Insert>Name>Define

In name box enter "myrange"(no quotes)

In refers to box enter =OFFSET(Sheet1!$C$22,0,0,COUNT(Sheet1!$C:$C),1)

The Sheet1 is example only. Your sheetname may differ.

OK your way out.

In H10 enter =SUM(myrange)

As you add entries to column C, myrange will expand to take those in.

Gord Dibben XL2002
 
If you're trying to avoid summing empty cells, don't worry about it-
SUM is optimized to calculate over only the used range, even if you
specify C22:C65536. It won't slow you down.
 
ONCE AGAIN: Thanks, Gord, for the help.
Your formula is -just- the sort of thing I was looking for...How th
heck did you come up with that?
Why did you use the 'OFFSET' function? My book says that 'offset' i
used to "Return a reference to a range that is a specified number o
rows and columns from a cell or range of cells."
So, you started by naming`$C$22' as start point--& then were require
to use the `0,0' arguments even tho you were going `0 rows down' and `
columns over' from the start point?
But doesn't the `COUNT(Sheet1!$C:$C),1)' tell the formula to includ
the count of the number of -all- the cells in column C?
How does the formula know to not include the first 8 cells in column?
Excuse me for saying so but, this is driving me nuts. :p

If you come up w/ 5 spare minutes could you please try to shed som
light on my ??'s, above? Other than that, please accept my stron
THANKS for taking the time to help! Have an excellent day!

Terr
 
Hi

P.e. into B2 enter the formula
=SUM(A:A)-SUM(A$1:A2)

--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets


turtleman2 said:
[Excel 2002] I'm looking for the formula that will calculate a running
total for a column from a point/cell halfway down that column,
extending down to-and-including the most recent entry.
Obviously, for an entire-column running total, the
"=SUM(column_name:column_name)" would work, but I don't want to include
the first
-n- group of cells.
Thanks for your time & attention to detail ;-}

Terry.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Hi Arvi,
That's an interesting variation for a running total and probably one
that would take longer than the other solutions with SUM. However, and this
applies to the other responses as well. Once you have a lot of rows and it will
slow down the entire workbook. . You not will notice the slowdown for a small
worksheet, so it may not seem to matter at first. But adding another row around
row 4000 is going to require something like 4000 additions plus 4000 subtractions
for that cell compared to 1 addition and 1 subtraction using OFFSET to
use the Balance Sum from the previous row.

You will have to be concerned with empty rows in between unlike the
use of SUM for a running total.

As far as calculation speed goes, I would avoid all use of SUM for
a running total. See the following with it's links in
Slow Response and Memory Problems
http://www.mvps.org/dmcritchie/excel/slowresp.htm

Use of SUM Worksheet Function for running balances as in
=SUM(D$2:D2) filled-down can take excessive time (misc 2003-01-31).
Use of SUM is fine for simple totals as in
=SUM(D$2:OFFSET(D20001,-1,0)), but not for running balances.
The use of OFFSET in running balances as in =OFFSET(D3,-1,0)+D3 is
much better than =D2+D3 when it comes to maintaining formulas and the
timing difference is insignificant relative to maintenance.

More on Running totals and the use of OFFSET in
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Arvi Laanemets said:
Hi

P.e. into B2 enter the formula
=SUM(A:A)-SUM(A$1:A2)

--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets


turtleman2 said:
[Excel 2002] I'm looking for the formula that will calculate a running
total for a column from a point/cell halfway down that column,
extending down to-and-including the most recent entry.
Obviously, for an entire-column running total, the
"=SUM(column_name:column_name)" would work, but I don't want to include
the first
-n- group of cells.
Thanks for your time & attention to detail ;-}

Terry.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
Click to expand...
 
David/Arvi

As it turned out, the OP(turtleman) did not require a true "running total" as
we usually think of it.

He wanted a simple SUM in a single cell that would account for additional rows
being added to column C.

J.E. suggested just =SUM($C$22:C65536) which would work fine.

OP feels that summing the entire column would take longer than a fixed range,
say C22:C100.

I provided the dynamic range formula just as an alternate.

Gord

Hi Arvi,
That's an interesting variation for a running total and probably one
that would take longer than the other solutions with SUM. However, and this
applies to the other responses as well. Once you have a lot of rows and it will
slow down the entire workbook. . You not will notice the slowdown for a small
worksheet, so it may not seem to matter at first. But adding another row around
row 4000 is going to require something like 4000 additions plus 4000 subtractions
for that cell compared to 1 addition and 1 subtraction using OFFSET to
use the Balance Sum from the previous row.

You will have to be concerned with empty rows in between unlike the
use of SUM for a running total.

As far as calculation speed goes, I would avoid all use of SUM for
a running total. See the following with it's links in
Slow Response and Memory Problems
http://www.mvps.org/dmcritchie/excel/slowresp.htm

Use of SUM Worksheet Function for running balances as in
=SUM(D$2:D2) filled-down can take excessive time (misc 2003-01-31).
Use of SUM is fine for simple totals as in
=SUM(D$2:OFFSET(D20001,-1,0)), but not for running balances.
The use of OFFSET in running balances as in =OFFSET(D3,-1,0)+D3 is
much better than =D2+D3 when it comes to maintaining formulas and the
timing difference is insignificant relative to maintenance.

More on Running totals and the use of OFFSET in
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Arvi Laanemets said:
Hi

P.e. into B2 enter the formula
=SUM(A:A)-SUM(A$1:A2)

--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets


turtleman2 said:
[Excel 2002] I'm looking for the formula that will calculate a running
total for a column from a point/cell halfway down that column,
extending down to-and-including the most recent entry.
Obviously, for an entire-column running total, the
"=SUM(column_name:column_name)" would work, but I don't want to include
the first
-n- group of cells.
Thanks for your time & attention to detail ;-}

Terry.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
Click to expand...
Click to expand...
 
Terry

See in-line for my take.

ONCE AGAIN: Thanks, Gord, for the help.
Your formula is -just- the sort of thing I was looking for...How the
heck did you come up with that?
Click to expand...

I didn't .....learned from someone else. Stephen Bullen as a matter of fact,
when I was browsing around looking for a method of naming a dynamic range for
a Chart Series.

But as J.E. pointed out, there would be no degradation in calculation by using
the =SUM($C$22:C65536).
Why did you use the 'OFFSET' function? My book says that 'offset' is
used to "Return a reference to a range that is a specified number of
rows and columns from a cell or range of cells."
So, you started by naming`$C$22' as start point--& then were required
to use the `0,0' arguments even tho you were going `0 rows down' and `0
columns over' from the start point?
Click to expand...

See Help for OFFSET arguments. Basically the OFFSET($C$22,0,0 does set the
start point at C22 and 0 rows down and 0 columns over.
But doesn't the `COUNT(Sheet1!$C:$C),1)' tell the formula to include
the count of the number of -all- the cells in column C?
How does the formula know to not include the first 8 cells in column?
Click to expand...

Not sure on this. I am assuming that the OFFSET($C$22,0,0 lets Excel know not
to count any of Column C above this point. The logic and mechanics of this
have eluded me and I have learned to accept.

Perhaps someone else can jump in with a definitive answer for this. Please!!
 
Back
Top