Results 1 to 10 of 10
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Totals (Excel 2000/SR-!a)

    Hi All:
    I'm trying to do something in Excel which ought to be very simple. I have 4 columns. Column A
    has dates of entries. Column B has amount of payments made, but most cells are blank. Column
    C has amounts of money paid back. I want Column D to show a running total of all payments in
    Column B minus the payments in Column C. In other words, it would look like this:

    <table border=1><td>Col A</td><td>Col B</td><td>Col C</td><td>Col D</td><td>1/1/02</td><td>500</td><td></td><td>500</td><td>2/1/02</td><td></td><td>50</td><td>450</td><td>2/15/02</td><td></td><td>100</td><td>350</td><td>2/20/02</td><td>50</td><td></td><td>400</td></table>
    How can I do this? Any help would be appreciated.

  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 Totals (Excel 2000/SR-!a)

    Put the formula below in D1:

    <pre>=B1-C1
    </pre>


    Then put the formula below in D2 and fill it down as far as needed:

    <pre>=IF(AND(B2="",C2=""),"",D1+B2-C2)
    </pre>

    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Totals (Excel 2000/SR-!a)

    Hi Phil,

    I am sure that in the AM there might be some fancier solutions, but in the meantime, try the following in D2 on down:
    <pre>=D2+B3-C3
    </pre>


    and the following in D1

    <pre>=B2-C2
    </pre>


    Tried to attach a workbook, but got an internal server error message.

    HTH,

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Running Totals (Excel 2000/SR-!a)

    Hi Phil,

    If you want col D to show blank when both B and C are blank for that row, then Legare's approach is what I would have suggested. It wasn't clear if that's what you wanted. If not, then
    -start with B1-C1 in D1 as Legare suggested
    - enter in D2: =D1+B2-C2
    - fill the formula in D2 down

    It wasn't clear if you'd have a row with both B and C blank. Even if this could be but you want the total on that row to be the same as the row above, then use the above.

    As far as filling goes (regardless of what you want in D), move your mouse to the lower right corner of D2. The cursor changes to + Now with your mouse over the lower right corner (the + sign showing), left click and drag down until you've dragged over as many rows as needed.

    If you are going to add more dates in the future, you just need to fill the last D to the next row as I've described.

    BTW: I don't owe you any money, do I?

    Fred

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Totals (Excel 2000/SR-!a)

    I did that so that the formula could be filled down past the last row of entries so that the new running total would just appear when a new row was added. If you use your suggestion, it will show a constant running total for rows after the last if you fill the formula down past the last row.
    Legare Coleman

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Running Totals (Excel 2000/SR-!a)

    Thanks for the pointer. That's why I mentioned about filling D after he creates a new entry. But better to have them pre-filled and show blank while the row is not being used. I do this for some of my own financial sheets. My problem is that when I add a new entry that goes beyond where I filled col D thru that I forget why D is blank. But then comes the "aha". <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    What I do in these cases, assuming Phil wanted to have an entry when B and C are blank but the date in col A is NOT blank, is test col A. If it's blank, then in all likelihood it's because he hasn't entered anything on the row yet. If col A is not blank, then have D as I did. But if col A is not blank, it is also probably true that B and/or C has an entry. So your way is probably best.

    Fred

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Totals (Excel 2000/SR-!a)

    Thank you all for your quick responses (as you can tell, I really know nothing about Excel). I won't be able to try them until tomorrow, but I wanted to thank you right away. Oh, Fred, I won't know if you owe me money until I get this working. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Running Totals (Excel 2000/SR-!a)

    I would tend to put in D2 (or wherever it starts)
    =sum(B$2:B2)-sum(C$2:C2).

    When this is copied down it automatically picks up all the values in B less all the values in C up to the current row. It will always put a value in each cell in col D.

    If that is what you wanted . . .

    Sum() does not mind blank cells.
    David Grugeon
    Brisbane Australia

  9. #9
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Totals (Excel 2000/SR-!a)

    Say I use this formula for a running total and fill it down for a number of rows, then put in values in the B or C columns, is there a way to insert a new row in the middle of the column without throwing off the balance in the last cell of the D column?

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Totals (Excel 2000/SR-!a)

    If you insert a row or rows, then the cell in column D in that row or rows will not contain the formula. In addition, the first cell in column D below where the row or rows were inserted will not have the correct formula. What you have to do after inserting the row or rows, is to fill the formula from the cell in Column D one row above where the row or rows were inserted down to one row beyond where the row or rows were inserted.
    Legare Coleman

Posting Permissions

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