running total

[John Pollard]

Excuse the primitive nature of the question; I searched for the
answer in a couple of Excel groups, but I suspect it's so simple
that the question isn't asked.

I rarely use Excel, but I need to produce a running total: I
want each cell in column B to contain the total of the cells in
column A starting with A1 thru the current row in column A. So:
B1=A1, B2=A1+A2, B3=A1+A2+A3, etc.

I can't figure how to specify a formula that I can copy into
each cell in column B to accomplish this. I'm assuming that I
can use SUM, but I can't seem to find a way to specify the cell
range so that copying the formula from one cell in column B, to
the remaining cells in column B, retains the correct cell
references.

Thanks.

 

[Peo Sjoblom]

In B1 put

=SUM($A$1:A1)

then copy the formula down as long as needed

 

[Pete_UK]

Try it this way:

B1: =A1
B2: =A2+B1
B3: =A3+B2

Copy B2 (or B3) down for as many values as you have in column A.

Hope this helps.

Pete

 

[John Pollard]

Thanks, just what I needed.

In B1 put

=SUM($A$1:A1)

then copy the formula down as long as needed



"John Pollard" wrote

 

[John Pollard]

I was playing with something like this, but never got it quite
right. Thanks.

 

[Roger Govier]

Hi John

Just a note.
If it is going to be a long list, then although both Peo's method and
Pete's achieve the same result.
the number of processes that Excel has to perform to get the answer is
much reduced in Pete's solution, so it will be faster.

On a small range, you won't notice the difference.

 

[Peo Sjoblom]

Hello Roger,

another thing is if the results in B are derived from for example a formula
where the result can be a "blank" like "" then the formula using operators
will return a value error while SUM will ignore any text

 

[Roger Govier]

Hi Peo

Yes that is quite correct.
It's just that I am ever mindful of the timings that David McRitchie
posted when there are large ranges involved
http://snipurl.com/1gbgf
and since then I have usually avoided this type of formula

I suppose one could overcome the problem of summing blanks by using
=N(A2)+B1

 

[John Pollard]

Thank you all. I love the internet and newsgroups, and those
who help. I'm saving all this; hopefully next time I won't need
to ask.