Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
dlanc
 
Posts: n/a
Default Daily data to weekly data conversion in Excel?

Is there a way that I can convert daily time series data into weekly data in
Excel? I'd like to take 2 columns of data where column 1 is the date (M-F)
and column 2 is the value associated with that date and create a new column
that has only the Fri dates and the values associated with the Fri dates.
I'd like to similarly further convert the end-of-week data to end-of-month
data. Thank you.
  #2   Report Post  
Biff
 
Posts: n/a
Default

Hi!

Column A, A1:A100 are dates
Column B, B1:B100 corresponding values

Formula in D1 entered as an array:

=INDEX(A$1:A$100,SMALL(IF(WEEKDAY($A$1:$A$100,2)=5 ,ROW($1:$100)),ROW(1:1)))

Copy across to E1 then down until you get #NUM! errors meaning the data has
been exhausted.

Format column D as DATE and column E as GENERAL.

Will extract all Friday dates and their corresponding values.

Biff

"dlanc" wrote in message
...
Is there a way that I can convert daily time series data into weekly data
in
Excel? I'd like to take 2 columns of data where column 1 is the date
(M-F)
and column 2 is the value associated with that date and create a new
column
that has only the Fri dates and the values associated with the Fri dates.
I'd like to similarly further convert the end-of-week data to end-of-month
data. Thank you.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I import formmail data to a custom excel template? cxlough41 Excel Worksheet Functions 1 July 1st 05 12:59 AM
Refresh Imported Data - Does the Excel file have to be open? tinkertoy Excel Discussion (Misc queries) 0 June 23rd 05 07:51 PM
Linking the data from one excel to another mrbalaje Excel Discussion (Misc queries) 2 June 13th 05 10:44 AM
Importing xml Data into Excel 2002 CMichaelAPCC Excel Discussion (Misc queries) 0 June 9th 05 03:14 PM
Fan charts Dean Charts and Charting in Excel 7 May 30th 05 11:51 AM


All times are GMT +1. The time now is 11:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"