Sum

[Steve Webb]

I want to sum running totals, i.e Column a1=36 (sum total=36) a2=7
(sum total=43) so on to a12. Any help with this will be much
appreciated.

[Jason Morin]

Insert this into B1 and fill down:

=SUM(A$1:A1)

HTH
Jason
Atlanta, GA

>.
>

[Robert Rosenberg]

In B1 place the following formula...

=A1

In B2 use the following formula...

=B1+A2

Copy this formula down the B column.

--
______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel

"Steve Webb" <steph...@yahoo.com> wrote in message
news:ef70aca6.03013...@posting.google.com...

[Robert Rosenberg]

Much better solution than mine. I'm getting old.

______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel

"Jason Morin" <jason...@us.exel.com> wrote in message
news:080d01c2c956$abb547f0$8df82ecf@TK2MSFTNGXA02...

[Rita Palazzi]

That's what's great about these newsgroups!!!!  I used to do it your way, but do see that this saves us a step!

Thanks guys!

[Myrna Larson]

I don't know that one is any better than the other. You get the same result, no?

[Robert Rosenberg]

True, but it only requires one formula in the 1st position - a formula that
can be copied down the column. Mine needs a different formula for the first
cell.

______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel

"Myrna Larson" <myrna...@charter.net> wrote in message
news:8eol3vgt7i1dgotmk...@4ax.com...

[David McRitchie]

Hi Jason, Rob and Myrna,

The solutions are not equal when it comes to processing.
You do NOT want to use SUM for running balances
as in B2: =SUM(A$1:A2)
and filled down for 20,000 rows
Best solution remains: B2: =OFFSET(B2,-1,0) + A2
no problem filling down for 20,000 rows.

Jason Morin:

Insert this into B1 and fill down:

B1: =SUM(A$1:A1)
expected disadvantage has to sum many cells for
each balance. It is possible but not likely that
Excel would optimize that. No problem inserting
deleting rows. But be sure to read the timings below.

Rob Rosenberg:
B1: =A1
B2: =B1+A2
Easy to read, but hard to maintain.
disadvantage can't insert delete rows
without retying (redoing some of) the formulas

My suggestion would have been had I posted it before:
B1: =A1
B2: =OFFSET(B2,-1,0) + A2
disadvantage OFFSET is considered a VOLATILE function.
No problem inserting deleting rows except before the
1st row. I have a whole page on inserting rows maintaining
formulas at
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

Timing: you can forget about using =SUM(A$1:A1)
Don't try this at home.
Set up a series in cells A1:A20000
Turn off calculation
put formula into B1 and fill down double-click on fill-handle
Turn on calculation runs about 1 minute if I recall
but just shows 1's all the way down.
Repeat about 2 minutes completes the sum.
Now if that's not enough try to delete column B
with calculation off, Ran for of 31 minutes
before I start to terminate Excel (computer at 103ºF,
on case). Terminated Excel with 34.57 minutes of
CPU time shown for Excel (may represent 2 terminated runs).
You realize that each new row doubles the number of
calculations. so the Sum in Column B is how many
additions of cells must be performed. 200,010,000

Timing for =OFFSET(B2,-1,0)+A2 was .005 seconds
and for a recalculation nothing registered at .000 seconds.
Number of additions would be 20,000

Option Explicit
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Dim myTime(4) As Double
myTime(3) = Time()
myTime(1) = timeGetTime()
'... Your code here for timing
Application.Calculate
'... End of Your code here for timing
myTime(2) = timeGetTime()
Target.Value = Format((myTime(2) - myTime(1)) / 1000, "#.000") _
& " seconds"
Target.Offset(0, 1).Value = myTime(3)
Target.Offset(0, 2).Value = Time()
Target.Offset(0, 3).Value = Target.Offset(0, 2) - Target.Offset(0, 1)
Target.Offset(0, 1).Resize(1, 3).NumberFormat = "[hh]:mm:ss.00"
End Sub

---
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

"Robert Rosenberg" <bla...@email.msn.com> wrote...

> True, but it only requires one formula in the 1st position - a formula that
> can be copied down the column. Mine needs a different formula for the first
> cell.
>

> "Myrna Larson" <myrna...@charter.net> wrote in message
> news:8eol3vgt7i1dgotmk...@4ax.com...
> > I don't know that one is any better than the other. You get the same
> result, no?
> >
> > On Fri, 31 Jan 2003 10:37:05 -0800, "Robert Rosenberg"

[Steve Webb]

Thank you for the replies.
What an excellent group for all levels of expertise.

Stephen

[Aladin Akyurek]

"David McRitchie" <dmcri...@msn.com> wrote in message
news:ugDxrXayCHA.2592@TK2MSFTNGP10...
[...]

> My suggestion would have been had I posted it before:
> B1: =A1
> B2: =OFFSET(B2,-1,0) + A2
> disadvantage OFFSET is considered a VOLATILE function.
> No problem inserting deleting rows except before the
> 1st row. I have a whole page on inserting rows maintaining
> formulas at
> http://www.mvps.org/dmcritchie/excel/insrtrow.htm
>

[...]

What would be the timing results if you had...

B1: =A1
B2: =INDEX(B1,0,1) + A2

instead?

[David McRitchie]

Hi Aladin,
What is the point
the zero is meaningless it is the same as 1 or omitted
so index(B1,0,0) is the same as is index(b1,,) since
they simply refer to the B1. The BIG problem is that it
suffers from the same problem as =B1+A2
which is that you cannot insert/delete rows without
having to retie formulas back together again.

total in B20000 is 200,009,994
(previously reported incorrectly).

The timings are a little higher today:

=B1+A2 .030 seconds
cannot delete/insert rows without repairing formulas afterwards

=OFFSET(B2,-1,0)+A2 .060 seconds or .050 secons
allows you to delete/insert rows without no problems

=INDEX(B1,,)+A2 .040 seconds
=INDEX(B1,0,1) + A2 .040 seconds
equivalent to =B1+A2, suffers from same problem

---
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

"Aladin Akyurek" <aky...@xs4all.nl> wrote in message news:3e3d08d5$0$49099$e4fe...@news.xs4all.nl...

[David McRitchie]

Hi Aladin, (updated a little bit, new timings, slowresp.htm)

What is the point
the zero is meaningless it is the same as 1 or omitted
so index(B1,0,0) is the same as is index(b1,,) since
they simply refer to the B1. The BIG problem is that it
suffers from the same problem as =B1+A2
which is that you cannot insert/delete rows without
having to retie formulas back together again.

total in B20000 is 200,009,994
(previously reported incorrectly).

The timings are a little higher today

reason was VBA editor was open

=B1+A2 .030 seconds
cannot delete/insert rows without repairing formulas afterwards

.020 seconds with VBE editor closed

=OFFSET(B2,-1,0)+A2 .060 seconds or .050 seconds

allows you to delete/insert rows without no problems

.040 seconds with VBE editor closed

=INDEX(B1,,)+A2 .040 seconds
=INDEX(B1,0,1) + A2 .040 seconds
equivalent to =B1+A2, suffers from same problem

.040 seconds with VBE editor closed

Best solution remains: B2: =OFFSET(B2,-1,0) + A2
no problem filling down for 20,000 rows.

no problem inserting/deleting rows

I have a whole page on inserting rows maintaining
formulas at
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

and use of =SUM(B$1:B2) has been included as something
not to use in my Slow Response page
http://www.mvps.org/dmcritchie/excel/slowresp.htm