Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting the first data point from a list of data
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting the first data point from a list of data
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 -- Biff Microsoft Excel MVP "CEGavinMcGrath" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting the first data point from a list of data
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" wrote: 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 -- Biff Microsoft Excel MVP "CEGavinMcGrath" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting the first data point from a list of data
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. -- Biff Microsoft Excel MVP "CEGavinMcGrath" wrote in message ... 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" wrote: 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 -- Biff Microsoft Excel MVP "CEGavinMcGrath" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving a line chart data point revises data table value in Excel ' | Charts and Charting in Excel | |||
show 3 sets of data per data point in a scatter plot | Charts and Charting in Excel | |||
Data point on line is not over the point/tick in X axis... | Charts and Charting in Excel | |||
excel 2007, how to select a data point and cycle through data points | Charts and Charting in Excel | |||
Including data in data table but not charting as a series or point | Charts and Charting in Excel |