1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

EXCEL: Running Average

Discussion in 'Business Applications' started by Trainrobber, Sep 15, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. Trainrobber

    Trainrobber Thread Starter

    Joined:
    Sep 15, 2004
    Messages:
    2
    I have percentages in a column (C3 thru Cx) updated daily. I need to show a running average of all successive daily averages. The daily and overall "process average" will then be displayed on a chart. (no problems doing that). The daily figures are calculated from two other columns that make up the %=x/y function.
    I can't seem to use any of the existing AVERAGE functions to automatically recalculate the new number of cells to be averaged as they are added each day. I don't want to have to manually write in a c3,c4,c5 then next day c3,c4,c5,c6 etc. averaging sequence.
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    So how do you add new daily data? Are you adding new lines to the bottom of the existing workbook and entering new data each day? Can you ZIP and post a sample workbook?

    Rollin
     
  3. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,294
    First Name:
    Wayne
    you should be able to use something like

    $A$1 - this fixes the cell
    then A1 is the varible cell
    so something like
    $A$1:A1
    copy that down the sheet

    so example
    row1 = SUM($A$1:A1) this adds up date to row1
    copied down the column will give a running total to each cell

    so at row 200 = sum($A$1:A200) this adds up the column from A1 through to A200

    I have a formula which will only produce 1 number in the last updated cell - i'll find a post
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,294
    First Name:
    Wayne
    =IF(AND(D28<>"",D29=""),SUM($D$3:E28),"")

    This checks to see if a cell is not blank and fills only i cell with a sum as a running sub-total
     
  5. Trainrobber

    Trainrobber Thread Starter

    Joined:
    Sep 15, 2004
    Messages:
    2
    etaf,
    I didn't know that the $ would lock the cell. Thanks a million. This solved the issue (and many more). As to checking for a null in the cell, unfortunately two subsequent days often have the same percentage so the <> portion of the formula prevents a percentage from being calculated. The check for a blank cell is really not a critical issue however. Thanks Again. :)
     
  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,294
    First Name:
    Wayne
    the $ locks the reference
    so the $ infront of the column locks the column
    so if you have $A1 and copy across columns - the A is locked
    but if you copy down the row isnt

    A$1 locks the row - so if you copy down the row stays as 1

    absolute addressing and relative addressing - i seem to remember the terms

    if you still need to fix it - let us know in detail what you are trying to do and what type of data you may get - nulls etc - or attach a sample spreadsheet
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/274282

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice