Try the below formula in f11, f20, f29, f38 and so on.....
Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

=AVERAGE(IF(MOD(ROW(OFFSET(F2,0,0,ROW()-2,1)),9)=1,
OFFSET(F2,0,0,ROW()-2,1)))

If this post helps click Yes
---------------
Jacob Skaria


"Illiniki2000" wrote:

Hi,
I have an excel worksheet with weekly data being totaled for 49 weeks. I
want to have an average each week that includes all preceding weeks; which is
then multipled by 49. Basically I want to predict a yearly total based on the
average of actual weekly total thus far.

Here's the data I want:
week 1: average week 1*49
week 2: average (week 1+ week2)*49
week 3: average (week 1+ week 2+ week 3)*49
week 4: average (week 1+ week 2+ week 3 + week 4)*49
and so on...

The formula I'm currently using is
F11=AVERAGE(F10)*49
F20=AVERAGE(F10,F19)*49
F29=AVERAGE(F10,F19,F28)*49

However, each time I paste the formula into the next week's cell, it is
dropping F10 from the average and I have to manually enter F10 back into the
formula.

Is there a way to paste the formula so that it adds the new cell without
dropping a cell (i.e. by week 49 there will be an average of data from 49
cells). Is there a better formula I should be using? Is there a name for
what I'm trying to do?

TIA for help - please let me know if I wasn't clear with my question.

Nicole

Should be

=AVERAGE(IF(MOD(ROW(OFFSET(F2,0,0,ROW()-2,1)),9)=1,
OFFSET(F2,0,0,ROW()-2,1)))*49


If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Try the below formula in f11, f20, f29, f38 and so on.....
Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

=AVERAGE(IF(MOD(ROW(OFFSET(F2,0,0,ROW()-2,1)),9)=1,
OFFSET(F2,0,0,ROW()-2,1)))

If this post helps click Yes
---------------
Jacob Skaria


"Illiniki2000" wrote:

Hi,
I have an excel worksheet with weekly data being totaled for 49 weeks. I
want to have an average each week that includes all preceding weeks; which is
then multipled by 49. Basically I want to predict a yearly total based on the
average of actual weekly total thus far.

Here's the data I want:
week 1: average week 1*49
week 2: average (week 1+ week2)*49
week 3: average (week 1+ week 2+ week 3)*49
week 4: average (week 1+ week 2+ week 3 + week 4)*49
and so on...

The formula I'm currently using is
F11=AVERAGE(F10)*49
F20=AVERAGE(F10,F19)*49
F29=AVERAGE(F10,F19,F28)*49

However, each time I paste the formula into the next week's cell, it is
dropping F10 from the average and I have to manually enter F10 back into the
formula.

Is there a way to paste the formula so that it adds the new cell without
dropping a cell (i.e. by week 49 there will be an average of data from 49
cells). Is there a better formula I should be using? Is there a name for
what I'm trying to do?

TIA for help - please let me know if I wasn't clear with my question.

Nicole