EXCEL: Running Average

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 
Joined
Sep 4, 2003
Messages
4,916
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
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,468
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

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,468
=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
 

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. :)
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,468
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top