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

[Yvon]

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

 

[MTBer]

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

 

[Yvon]

I assumed that no other data is in column A or B and that cells A1:B1
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 of
entries in column B, which has 27 entries, (26 weeks + 1 title) so
count(B:B)-1 gives you 26 weeks.

hope that helps

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

Yvon