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. |
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. |
All times are GMT +1. The time now is 04:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com