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

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

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

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

Yvon

1. Mac shipments lowest in 1.5 years - first year-over-year drop in 5.5 years

2. Mac shipments lowest in 1.5 years - first year-over-year drop in 5.5 years

3. Year Parameter, gives year and previous year (beginner)

4. How can it go sideway year by Year ( TahunPel/Year Servie).

5. Efficient query for this year/last year/two years ago

6. Outlook show week number(1-52); day of year / days left in year

7. How do I crate a function for week of year + year in same cell.

8. Program Year vs Calendar Year - Tracking the weeks

9. how to calculate average within a year before grouping by year

10. Compare given period in current year / previous year

11. compare this year to last year

12. Date restrictor comparing this year to last year

13. Compare this year/qtr/mo to date with prior year/qtr/mo to date

14. Compare this year vs. previous year data in one graph (date vs. quantity)

15. compare last year and this years sales

3 post • Page:**1** of **1**