Results 1 to 7 of 7
  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 (2000/SR1-a)

    Hi All:
    I want to have a column of running totals next to a column of figures, so I need a formula like:
    =sum($B1:B1)
    =sum($B1:B2)
    =sum($B1:B3)
    - - - - - - - - -
    =sum($B1:Bn)

    Is there an easy way to get the fill-down feature to put these in my column. What I've been doing is make the first cell:

    1. =sum(Y:B1) where Y is not being used. The cell says "#Name?"
    2. Then I drag it down using the fill handle. The result is "#Name?" in all the cells.
    3. Then with the cells selected, I replace all "Y" with "$B1".

    This works, but I'm wondering if there's an easier way to put the formulas in all the cells.

    Thanks,

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Totals (2000/SR1-a)

    Change the formula to =sum($b$1:b1) in the first cell.

    Then double click the fill handle at the bottom right of the cell.
    The column will autofill with the first refernece absolute (non-changing) and the second relative (auto incrementing).

  3. #3
    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 (2000/SR1-a)

    Ahh. Thank you so much. However, why do you double click the fill handle? I found that it seems to behave the same whether you drag it, or double click & then drag. Double click by itself didn't do anything. Should it?
    Thanks again,

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Totals (2000/SR1-a)

    The double Click should cause it to autofill in the list for you with out draging. This of course assumes that you are putting your formulas in an adjacent colomn

    HTH

    Peter

  5. #5
    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 (2000/SR1-a)

    Thank you. I didn't realize that it only fills if the column is adjacent AND if each cell in the original column isn't blank. Now I know. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>
    Thanks again.

  6. #6
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Totals (2000/SR1-a)

    I never knew about double clicking the autofill <img src=/S/cool.gif border=0 alt=cool width=15 height=15>.

    I would like to add a note here, as a help to those new to absolute referencing. Rather than trying to remember where the $ are placed in the formula, (because I always get it wrong), highlight the cells in the formula that need to be absolute and press the F4 function key at the top of the keyboard.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Totals (2000/SR1-a)

    Just another tip:

    Drag the fill handle *with your right mouse button* and you'll get a context menu when you release with a number of nice options.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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