Results 1 to 7 of 7

Thread: Running data

  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: Running data

    I was wondering how I can keep a running sum of a currency column in my excell sheet. Here is an example.

    $1000
    $3500
    $2900...

    Sum=$7400

    The problem is, is that I am inporting the data from a SQL Server table and the data needs to be refreshed regularly. After a refresh there will be lines added to the Excel sheet and therefore overrun the sum. Can I make it so the sum cell moves down as need? I will also need to keep a running average. Is there some way to do this? In other words, I might have 50 rows added up, then refresh the table and there will be 55 rows. I will now need to take these new rows into the average.
    Thanks.
    Last edited by exdter; 01-22-04 at 16:05.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    The easiest way is to use a defined dynamic range for the data, then put your summary on another sheet.

    Let's say your data is on a sheet labeled Import, and column K has the totals. Go to INSERT > NAME > DEFINE. In the dialog box, give it a name, i.e. KTotal. In the Refers to box at the bottom put in this formula:

    =OFFSET(Import!$K$1,0,0,COUNTA(Import!$K:$K),1)

    Click ADD, then OK.

    Now on another sheet, put in this formula into cell A1:

    =SUM(KTotal)

    Now, when you refresh your data, the named range will appropriately change, and so the sum formula will reflect that change.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Aug 2003
    Posts
    328
    Thanks for your help. That's a little too involved for my needs. I worked it out. I just inserted some blank lines at the top of my sheet and made my sum and average formulas there. Thanks for your time.

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091

    Thumbs up

    Glad you found a solution.

    The reason I suggest another sheet is that it helps to divide the data from the summary page. That way you can maintain a pure data page.

    Dynamic named ranges can save considerable time adjusting formulas every time there is a change in the data length.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Aug 2003
    Posts
    328
    Ok thanks. The powers that be (my boss) wants to see it all on the same page.

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    BTW, you can still use the dynamic named range on the same page as you are doing. That way your formula will still reflect only the current used cells, and you never have to change/adjust the formula.
    Last edited by shades; 01-22-04 at 17:11.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Aug 2003
    Posts
    328
    That's what I did.
    Thanks.

Posting Permissions

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