Running Average Excel 2007

[goodwill-granny]

I have a worksheet tracking data over time. I input new information each
week. I need to calculate the average each week for the information in
several columns. After 13 weeks I will have 13 rows of data, with the average
for each column. I do this by quarter to eventually have the average over 52
weeks.

I need to have the average recalculated each week. What I have been doing is
entering my sum as B4:B16/X, where X changes each week as I input the data.

Is there a formula I can use that will update the average each week when I
input new data?

 

[מיכ×ל (מיקי) ×בידן]

Could it be: =AVERAGE(B:B) ?
While there is nothing else in col. "B: beside the data.
Micky

 

[goodwill-granny]

I guess I wasn't as clear as I should be. (Easy to understand when it's ones
own problem)

Example:
A B C
wk #1 6037 1207 86%
wk #2 5918 1183 85%
wk #3 6022 1204 87%
wk #4
wk #5
wk #6

Weeks 4, 5, and 6 haven't happened yet. I need to sum the data in B and have
the average update each week when I enter the info. Right now I have B1:B6/3.
I would like to not have to change the divisor each week.

 

[JR Hester]

I am not sure where your "average is going, column C or Column D. try this
formula in either c1 or D1,to get the average of all date starting with week
#1
=average(b$1:B1)
then replicate this down the column.

This may create an error in those cells wher B is empty. If yiou want to
emilnate that error message try formula below in C1 or D1
=If(isblank(b1), "n/a", average(b$1:b1))
Then replicate down the column ( You can replace the "n/a" with "" if you
prefer a null entry rather than the n/a--Not available message)
hth
Put t

 

[goodwill-granny]

That didn't work.

Here is one of the actual worksheets I have.
I enter the info in C each week. D is the average over 5 days, and E is the
percentage of the goal (1392/day) I would like the total of D to
automatically change without having to go to the total of D and change the
divisor. (In this example, the divisor of the total of the numbers in D is 5.
When I input the numbers for 2/5/10, I will have to change the divisor to 6.)

A B C D
E
Date Daily# Wkly actual Actual daily Avg.
%age of goal
1/2/2010 1392 6,037 1207.40 86.74%
1/9/2010 1392 5,918 1183.60 85.03%
1/16/2010 1392 6,022 1204.40 86.52%
1/23/2010 1392 7,065 1413.00 101.51%
1/30/2010 1392 6,096 1219.20 87.59%
2/5/2010 1392 0.00 0.00%
2/13/2010 1392 0.00 0.00%
2/20/2010 1392 0.00 0.00%
2/27/2010 1392 0.00 0.00%
3/6/2010 1392 0.00 0.00%
3/13/2010 1392 0.00 0.00%
3/20/2010 1392 0.00 0.00%
3/27/2010 1392 0.00 0.00%

AVERAGE: 1392 6,227.60 1245.52 89.48%

Thank you.

 

[goodwill-granny]

Is this something that cannot be done?

Thanks.

That didn't work.

Here is one of the actual worksheets I have.
I enter the info in C each week. D is the average over 5 days, and E is the
percentage of the goal (1392/day) I would like the total of D to
automatically change without having to go to the total of D and change the
divisor. (In this example, the divisor of the total of the numbers in D is 5.
When I input the numbers for 2/5/10, I will have to change the divisor to 6.)

A B C D
E
Date Daily# Wkly actual Actual daily Avg.
%age of goal
1/2/2010 1392 6,037 1207.40 86.74%
1/9/2010 1392 5,918 1183.60 85.03%
1/16/2010 1392 6,022 1204.40 86.52%
1/23/2010 1392 7,065 1413.00 101.51%
1/30/2010 1392 6,096 1219.20 87.59%
2/5/2010 1392 0.00 0.00%
2/13/2010 1392 0.00 0.00%
2/20/2010 1392 0.00 0.00%
2/27/2010 1392 0.00 0.00%
3/6/2010 1392 0.00 0.00%
3/13/2010 1392 0.00 0.00%
3/20/2010 1392 0.00 0.00%
3/27/2010 1392 0.00 0.00%

AVERAGE: 1392 6,227.60 1245.52 89.48%

Thank you.