Running Total problem
Posted by EcK on January 16, 2002 12:06 PM
I have read the entire Excel help file looking for the answer to this simple problem. Youd think with all the financial functions they have, they would have a function that reports the running total of a column. The column is very long, and i would like the running total also displayed in a cell up top so i dont have to scroll down a million entries every time i want to check it. Would appreciate any help on this..
Posted by Mark W. on January 16, 2002 12:18 PM
If your values are in column A beginning at cell
A2 you can create a running total beginning in
cell B2 by entering the formula, =A2+N(B1), and
copying down for the last value in column A. If
you want to track the last of these running totals
use =SUM(A:A).
Posted by Adam S. on January 16, 2002 12:43 PM
Hiya,
I see 2 interpretations from your problem description (revolving around how you're pulling the running total).
1: (The simple explanation) You have a column that fills in over time and want to know that the current total is w/out having to edit something like: =sum(D2:D2300) to =sum(D2:2550)
If this is the case you can use =sum(D:D) - if the cells underneath are blanks,
or possibly =sum(indirect("D2:D"&row(counta(D:D)) ->assuming there IS data under the sum-column and no blanks in the data itself.
2. You have a long column of -say 3000 records- and you want to find the running total at a point somewhere in the middle.
I envision your setup to be something like:
Date Data
1/1/1994 3000
1/2/1994 3400
.
.
(go down 3000-ish rows)
.
.
3/3/2002 4650
So at the top of the sheet you may wish to find the running total at - say 4/15/1998. If this is the case, have a cell set aside for the input field (in this case 4/15/1998). Insert a column with the row number (1:3000 ish).
Actually I'm sure some people can simplify this a bit, but this would be my first crack (assuming the inserted column is in column C now)
=sum(indirect("B2:B"&vlookup(InputCell,A:C,3,false)))
Pieced together, basically it's reduced to =sum("B2:B"&Rownumber)...so if 4/15/98 was on row 2200, it reduces to =sum(B2:B2200) -your running total at that time.
Hope that helps out,
Adam S.
