Converting Daily Info to Weekly

[CEGavinMcGrath]

I am trying to "pull" certain information from a Daily Table into a Weekly
Table.

For example, in the two tables below, I am trying to pull the "Open" figure
from the Daily Table into a Weekly Table, based on the criteria that as the
week changes (e.g. from WeekNum 34 to WeekNum 35) the Weekly Table will
automatically pull the Open figure (i.e. 515) into the relevant cell.

Daily Info Weekly Info

WeekNum Date Open WeekNum Date Open
35 29/08/08 558 35 26/08/08 515
35 28/08/08 526.5 34 18/08/08 610
35 27/08/08 524.5 33 11/08/08 603
35 26/08/08 515 32 04/08/08 538.5
34 22/08/08 522
34 21/08/08 540
34 20/08/08 560
34 19/08/08 576
34 18/08/08 610
33 15/08/08 608
33 14/08/08 581
33 13/08/08 602
33 12/08/08 630
33 11/08/08 603
32 08/08/08 598.5
32 07/08/08 607
32 06/08/08 624.5
32 05/08/08 540.5
32 04/08/08 538.5

This really as me stumped!! Any help would be appreciated.

Kind regards, Gavin.

 

[T. Valko]

My other suggestion worked but adding a helper column with the week num
makes it easy!

If D1 = weeknum = 35

Enter this formula in E1 and copy across to F1:

=LOOKUP(2,1/($A$1:$A$19=$D1),B$1:B$19)

Select both E1 and F1 and copy down as needed.

Format E1:En as Date

 

[CEGavinMcGrath]

Biff,

I have to say that I am very impressed. I am still not sure how the formula
works, but work it does!! I have one further "wrinkle" that I would like to
add, if I may: The data I am looking at is over a number of years and,
therefore, there are instances wher the WeekNum will be the same for
different years. How do I get the formula to pull up the data relevant to
the particular year only?

Many, many thanks, Gavin.

 

[T. Valko]

For the year 2008:

=LOOKUP(2,1/(($A$1:$A$19=$D1)*(YEAR($B$1:$B$19)=2008)),B$1:B$19)

 

[CEGavinMcGrath]

Biff,

Apologies for the delay in thanking you.

If I were to try and get the "Close" rather than the "Open" in a similar set
of data (i.e. it would be the final day in the particular WeekNum, as opposed
to the first), how might I do that?

Kind regards, Gavin.

 

[T. Valko]

Based on the same table...

Try this array formula** entered in E2:

=INDEX(B$2:B$20,MATCH(1,($A$2:$A$20=$D2)*(YEAR($B$2:$B$20)=2008),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Copy across to F2 then down as needed.

Format E2:En as Date