Extracting first value of each month
 

Hello,

How do you extract the first value (plus the date) of each month out of
a series?

Example
2/4/05 data
2/12/05 data
2/13/05 data
3/1/05 data
3/2/05 data
etc


In this sample I only want to pull:
2/4/05 data
3/1/05 data

In reality I have a large dataset I am working with (15 years with
weekly entries). I just want to pull the first value (and date) of each
month.


Thanks in advance for any help.

Al

Extracting first value of each month
 

=INDEX(B1:B1000,MATCH(MAX(IF((YEAR(A1:A1000)=2005) *(MONTH(A1:A1000)=2),A1:A1
000)),A1:A1000,0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Number_Cruncher" wrote in message
news:2006080515451816807-me@therecom...
Hello,

How do you extract the first value (plus the date) of each month out of
a series?

Example
2/4/05 data
2/12/05 data
2/13/05 data
3/1/05 data
3/2/05 data
etc


In this sample I only want to pull:
2/4/05 data
3/1/05 data

In reality I have a large dataset I am working with (15 years with
weekly entries). I just want to pull the first value (and date) of each
month.


Thanks in advance for any help.

Al

Extracting first value of each month
 

Different interpretation...

Assuming that A2:B10 contains the data, try the following formula which
needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

C2, copied down and over to Column D:

=INDEX(A$2:A$10,SMALL(IF(MATCH($A$2:$A$10-DAY($A$2:$A$10)+1,$A$2:$A$10-DA
Y($A$2:$A$10)+1,0)=ROW($A$2:$A$10)-ROW($A$2)+1,ROW($A$2:$A$10)-ROW($A$2)+
1),ROWS(C$2:C2)))

Hope this helps!

In article <2006080515451816807-me@therecom,
Number_Cruncher wrote:

Hello,

How do you extract the first value (plus the date) of each month out of
a series?

Example
2/4/05 data
2/12/05 data
2/13/05 data
3/1/05 data
3/2/05 data
etc


In this sample I only want to pull:
2/4/05 data
3/1/05 data

In reality I have a large dataset I am working with (15 years with
weekly entries). I just want to pull the first value (and date) of each
month.


Thanks in advance for any help.

Al

Extracting first value of each month
 

On 2006-08-05 18:09:35 -0500, Domenic said:

Different interpretation...

Assuming that A2:B10 contains the data, try the following formula which
needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

C2, copied down and over to Column D:

=INDEX(A$2:A$10,SMALL(IF(MATCH($A$2:$A$10-DAY($A$2:$A$10)+1,$A$2:$A$10-DA
Y($A$2:$A$10)+1,0)=ROW($A$2:$A$10)-ROW($A$2)+1,ROW($A$2:$A$10)-ROW($A$2)+
1),ROWS(C$2:C2)))

Hope this helps!

In article <2006080515451816807-me@therecom,
Number_Cruncher wrote:

Hello,

How do you extract the first value (plus the date) of each month out of
a series?

Example
2/4/05 data
2/12/05 data
2/13/05 data
3/1/05 data
3/2/05 data
etc


In this sample I only want to pull:
2/4/05 data
3/1/05 data

In reality I have a large dataset I am working with (15 years with
weekly entries). I just want to pull the first value (and date) of each
month.


Thanks in advance for any help.

Al

Thank You!