Getting the first data point from a list of data

  • Thread starter Thread starter CEGavinMcGrath
  • Start date Start date
C

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.
 
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.).
Click to expand...

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
 
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.
 
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.
 
Back
Top