Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting month from a date field | Excel Discussion (Misc queries) | |||
Extracting month as text | Excel Worksheet Functions | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
Formula for Extracting Month out of a Date column | Excel Discussion (Misc queries) | |||
Month Year Date Format | Excel Worksheet Functions |