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.
     
    Trainrobber, Sep 15, 2004
    #1
  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
     
    Rollin_Again, Sep 15, 2004
    #2
  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
     
    etaf, Sep 15, 2004
    #3
  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
     
    etaf, Sep 15, 2004
    #4
  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. :)
     
    Trainrobber, Sep 15, 2004
    #5
  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
     
    etaf, Sep 15, 2004
    #6

  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...
Similar Threads - EXCEL Running Average
  1. chrisb1978march

    Solved Help with a macro in Excel

    chrisb1978march, Jan 17, 2020 at 9:54 AM, in forum: Business Applications
    Replies:
    1
    Views:
    219
    chrisb1978march
    Jan 21, 2020 at 7:31 AM
  2. stevezal

    How to fire an Excel macro from MSAccess?

    stevezal, Jan 12, 2020, in forum: Business Applications
    Replies:
    2
    Views:
    411
    stevezal
    Jan 14, 2020
  3. Keebellah

    Solved Excel VBA set a filter for a exact filename using MsoFileDia

    Keebellah, Jan 6, 2020, in forum: Business Applications
    Replies:
    1
    Views:
    438
    Keebellah
    Jan 7, 2020
  4. mrl

    excel 365 sometimes loads as a very small box

    mrl, Dec 30, 2019, in forum: Business Applications
    Replies:
    4
    Views:
    496
    mrl
    Dec 31, 2019
  5. mterry

    Excel: Move object on chart-it returns to original position

    mterry, Dec 23, 2019, in forum: Business Applications
    Replies:
    0
    Views:
    485
    mterry
    Dec 23, 2019
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.
    Accept Learn More...
    Dismiss Notice