Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post

    Simple Cell Formula Question

    I don't know very much about using cell formulas in Excel -- I just use Excel to store values at this point. I have a spreadsheet that has several columns for names and dates and a column showing dollar values (amounts borrowed on different dates) and I would like to add another column that shows a running total on each line, so that when I add a new borrowed value the total so far will be displayed in another column on that line.

    I feel sure this is a very basic thing to do - right?

    Thanks,
    Bill

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bill,

    Assuming your dollar amount is in column D you would place the following formula in the 2nd row in column E:
    =$E1+$D2
    You would then fill the formula down.

    Note: this assumes that you have column headers in row 1. If row 1 has a value in Col E then you would use: =$D1 in row 1 and then the first formula in row 2 and fill down.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,175
    Thanks
    47
    Thanked 981 Times in 911 Posts
    What you can't do with formulas! You really must get stuck in to them.

    Running total is just taking the running total value immediately above and adding the borrowed amount from the left (usually).
    If you have the borrowed amount in A1 to A5 and want the running total in B1 to B5 then enter this in B1:
    =A1

    Now in B2:
    =A2+B1

    Using the fill function drag B2 into B3 to B5. The fill function is used by holding the left mouse button down on the little black square in the bottom right corner of the selected cell and dragging down, or across, until you get to the end of the fill range, then let go of the button.

    cheers, Paul

  4. #4
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post
    Thanks, RG and Paul. After having done what either of you suggested down through the existing rows, will it repeat automaticaly each time I add a new row in the future? Suppose I discover that I have missed an item from the past and I insert a row in the middle of an existing list will all the ones below it adjust automatically?

    Thanks,
    Bill

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bill,

    No it will not be automatically added in either case!. In fact if you insert in the middle the other entries will adjust IGNORING the added entry. The workaround is to just fill down again from the line ABOVE the added line(s). For automatic processes you need macros. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post
    OK, filling down again is a reasonable step to take to refresh the list. That is all I need.

    Thanks, RG.
    Bill

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Bill,

    To avoid having to enter the formula with each new entry, you can copy this modified formula down to the bottom.

    In cell E2 enter =IF(D2="","",$E1+$D2)

    The formula will just sit there and not display a value until you enter a new line and place a value in Column D. Without the if statement, a zero (because of the blank) will be added to the running total and the last value will run down the page.

    With IF Statement
    IfStatement.png

    Without IF Statement
    IfStatement2.png

    HTH,
    Maud
    Last edited by Maudibe; 2014-11-09 at 03:36. Reason: image added

  8. #8
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,175
    Thanks
    47
    Thanked 981 Times in 911 Posts
    You can also fill up and double clicking on the "fill" square will automatically fill down to the end of the existing data immediately to the left. e.g. If you have data in A1 to A56 and formula in B1 to B11, auto-fill will fill from B11 to B56.

    cheers, Paul

  9. #9
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post
    Maud & Paul T, thanks for the additional info. I'm learning and I am better off than I was before. My worksheet is working fine now.

    Thanks,
    Bill

Posting Permissions

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