## Comparing y-t-d average between 2 years for each year without surpassing present year's week

### Comparing y-t-d average between 2 years for each year without surpassing present year's week

I need to compare data between 2 years giving a y-t-d average for both
years. The catch being that i cannot exceed the current year's week
for both y-t-d averages (for obvious reasons). Is there a formula that
will automatically re-adjust the calculation when data is entered in
the present year's week re-calculating both y-t-d averages up to the
present year's week? Here is my data set up:

I have 2 columns: column A = 2003 & column B = 2004
Column A has a full 52 weeks of data and column B has 26 weeks of
data.

I have not set up any time line for each week other then a 1 to
represent week 1 and so forth.

Any help would be appreciated.
I have Excel 2000 version.

Thank you,

Yvon

### Comparing y-t-d average between 2 years for each year without surpassing present year's week

I assumed that no other data is in column A or B and that cells A1:B
have the column titles.

This will calculate a variance comparing current ytd less prior ytd

=SUM(\$B\$2:INDEX(\$B\$2:\$B\$65536,COUNT(B:B)-1))-SUM(\$A\$2:INDEX(\$A\$2:\$A\$65536,COUNT(B:B)-1))

this will calculate a difference between the two averages.

=average(\$B\$2:INDEX(\$B\$2:\$B\$65536,COUNT(B:B)-1))-average(\$A\$2:INDEX(\$A\$2:\$A\$65536,COUNT(B:B)-1))

the formula works by setting the range by counting the number o
entries in column B, which has 27 entries, (26 weeks + 1 title) s
count(B:B)-1 gives you 26 weeks.

hope that help

### Comparing y-t-d average between 2 years for each year without surpassing present year's week

Thank you very much MTBer, the formula was very helpful.

Yvon