Results 1 to 6 of 6
  1. #1
    New Lounger
    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.

  2. #2
    Uranium Lounger
    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

  3. #3
    New Lounger
    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.

  4. #4
    Uranium Lounger
    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

  5. #5
    New Lounger
    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.

  6. #6
    2 Star Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •