# EXCEL: Running Average

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

Not open for further replies.

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

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. ### etafModerator

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. ### etafModerator

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

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. ### etafModerator

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

As Seen On