D
deer man
We are using MS excel 2003 on windows XP.
We have 2 worksheets as follows
Worksheet 1 has 2 columns; A is # weeks since Nov 1984, B
is avg snow depth that week. This worksheet has about
10000 rows of data. Further, this sheet only has data
for the winter months (26 weeks).
Worksheet 2 also has 2 columns; A is animal ID, B is #
weeks since nov 1984 that the animal was sampled (this
corresponds to column A of worksheet 1). THis worksheet
has 3500 rows of data.
We want to be able to calculate the total of the avg snow
depths of all weeks over a specified period relative to
the week the animal was sampled.
For example, on 15 Feb 1986 (week 68) we sampled an
animal and we want to relate this to the amount of
snowfall that had accumulated up to this point in this
winter. We have figured out conceptually that if we
subtract 26 weeks from the sampling date of the animal we
essentially have a value from which to start our addition
to determine cumulative snow depths aver all weeks (In
this case 68-26=42-week 42 is June and there will be no
snow from June up until autumn).
Our problem is trying to figure out how, for each of the
3500 animals, we can sum the snow depths for the
corresponding start and end week (in this case week 42
until week 68).
Any advise would be greatly appreciated.
Note: The actual structure of our dataset is much more
complex but if we can determine a method for this more
simple situation we should be able to apply is to
our 'real' data.
Thanks in advance
We have 2 worksheets as follows
Worksheet 1 has 2 columns; A is # weeks since Nov 1984, B
is avg snow depth that week. This worksheet has about
10000 rows of data. Further, this sheet only has data
for the winter months (26 weeks).
Worksheet 2 also has 2 columns; A is animal ID, B is #
weeks since nov 1984 that the animal was sampled (this
corresponds to column A of worksheet 1). THis worksheet
has 3500 rows of data.
We want to be able to calculate the total of the avg snow
depths of all weeks over a specified period relative to
the week the animal was sampled.
For example, on 15 Feb 1986 (week 68) we sampled an
animal and we want to relate this to the amount of
snowfall that had accumulated up to this point in this
winter. We have figured out conceptually that if we
subtract 26 weeks from the sampling date of the animal we
essentially have a value from which to start our addition
to determine cumulative snow depths aver all weeks (In
this case 68-26=42-week 42 is June and there will be no
snow from June up until autumn).
Our problem is trying to figure out how, for each of the
3500 animals, we can sum the snow depths for the
corresponding start and end week (in this case week 42
until week 68).
Any advise would be greatly appreciated.
Note: The actual structure of our dataset is much more
complex but if we can determine a method for this more
simple situation we should be able to apply is to
our 'real' data.
Thanks in advance