Results 1 to 6 of 6
Thread: running total (2000 sr1)

20020423, 18:19 #1
 Join Date
 Apr 2002
 Location
 NJ, USA
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
running total (2000 sr1)
How do I set a running total down one column as I enter numbers in each row.

20020423, 19:16 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: running total (2000 sr1)
Lets say you are entering numbers in column A and you want the running total in column B and the you enter the first number in A1. Here is what you do:
1 In B1 enter the formula: =A1
2 In B2 enter the formula: =IF(A2<>"",B1+A2,"")
3 Fill the formulas in B2 as far down the column as you will be entering data in A.Legare Coleman

20020423, 19:50 #3
 Join Date
 Apr 2002
 Location
 NJ, USA
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: running total (2000 sr1)
I am trying to Add A1,A2,A3,A4and put the sum in A5. Then have the option to put a number in A5 and have the total automatically drop to A6.

20020423, 22:51 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: running total (2000 sr1)
OK, then try this:
1 Put your number in A1:A4
2 Put this formula in A5:
<pre>=SUM(A1:OFFSET(A1,ROW()2,0,1,1))
</pre>
Now, always insert a row/cell above this formula where you want the new value. When you type the new value, it should be included in the sum.Legare Coleman

20020424, 05:17 #5
 Join Date
 Apr 2002
 Posts
 2
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: running total (2000 sr1)
I've used this problem as a teaching example to explain the differance between absolute and relative referances. Most cell referances you enter are relative, that means if you copy them they will be adjusted. For example if b2 contains sum(a1..a2) and you copy it down the rest of the b column you will get sum(a2..a3) then sum(a3..a4) because the copy function adjusts the cell addresses. If instead b1 contained sum($a$1..a2) when you copied the formula down the $a$1 entry is "absolute" ie it is not adjusted. So b2 will have sum($a$1..a2) and b3 will have sum($a$1..a3)  you just got your runnng total.
Any address can be full absolute ($a$1) or absolute in only row or column ($a1 or a$1). The F4 key will toggle through the possabilities.

20020424, 05:27 #6
 Join Date
 Jan 2001
 Location
 Perth, Western Australia, Australia
 Posts
 190
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: running total (2000 sr1)
An alternative is to place the column total at the top of the page.
The formula is (from memory) =Sum(Column_range) where column_rage is a fixed range eg a2:a50 or a dynamic range eg =offset(A2,0,0,counta(a2,a5000),1)
Note the above formulas assume the column to be summed starts in cell A2. Cell A1 should contain the total.
You can then use freeze panes to ensure that the total is always displayed. This has the added advantage that the total is always located in the same place.
HIH