Getting the first data point from a list of data

[CEGavinMcGrath]

I am trying to make a Weekly data table from a Daily table for some financial
information, along the following lines:

Date Open High Low Close
29/08/2008 558 565 545 550
28/08/2008 527 560 516 558
27/08/2008 525 525 505 519
26/08/2008 515 530 513 522
22/08/2008 522 535 514 530
21/08/2008 540 541 516 516
20/08/2008 560 560 530 540
19/08/2008 576 578 543 546
18/08/2008 610 614 587 587
15/08/2008 608 620 591 610
14/08/2008 581 606 572 602
13/08/2008 602 602 565 571

I want to pull the Open data for the first day in each week in the data
series (ie 602 for 13/08/08, 610 for 18/8/08, 515 for 26/8/08, etc.). Does
anybody know how I might go about doing this?

Kind regards.

 

[T. Valko]

pull the Open data for the first day in each week in the data

series (ie 602 for 13/08/08, 610 for 18/8/08, 515 for 26/8/08, etc.).

How do you know which dates are the first day of the week? Each of your
stated dates are different weekdays:

602 for 13/08/08 - the 13th is a Wednesday
610 for 18/8/08 - the 18th is a Monday
515 for 26/8/08 - the 26th is a Tuesday

 

[CEGavinMcGrath]

That's part of the problem. If there is a national holiday on a Monday, for
example, there may be no data from a Monday. Therefore, Tuesday becomes the
first day of the week.

Any ideas would be greatly appreciated.

 

[T. Valko]

Ok, this is kind of complicated but it works.

Based on your posted sample data...

The data is in the range A2:E13. *A14 MUST BE AN EMPTY CELL*.

Enter this array formula** in say, G2:

=IF(ROWS(G$2:G2)<=SUM(--(WEEKDAY(A$2:A$13,2)<WEEKDAY(A$3:A$14,2))),INDEX(A$2:A$13,SMALL(IF(WEEKDAY(A$2:A$13,2)<WEEKDAY(A$3:A$14,2),ROW(A$2:A$13)),ROWS(G$2:G2))-MIN(ROW(A$2:A$13))+1),"")

Notice there are references to cell A14. This cell *must* be empty!

Enter this formula H2:

=IF(G2="","",INDEX(B$2:B$13,MATCH(G2,A$2:A$13,0)))

Select both G2 and H2 and copy down until your get blanks.

Format cells G2:Gn as Date.

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

I can post a link to a sample if it'll help.