Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting between different frequencies for time series data
I need to convert between high frequency (for example, daily) and low
frequency (annual) data. Ideally I would like the ability to do this on an average basis, end of period basis, beginning of period basis, etc. Is this possible? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting between different frequencies for time series data
Hi hello:
It is very easy using auto filter. Let's say your dates are in column A in A2 thru A355 and you data is in column B in B2 thru B355 with header cells in A1 and B1. In C2 enter: =TEXT(A2,"ddd") In D2 enter: =TEXT(A2,"d") copy C2 and D2 down. You will see: date measure day date 1/1/2006 1 Sun 1 1/2/2006 3 Mon 2 1/3/2006 4 Tue 3 1/4/2006 5 Wed 4 1/5/2006 5 Thu 5 1/6/2006 6 Fri 6 1/7/2006 7 Sat 7 1/8/2006 9 Sun 8 1/9/2006 10 Mon 9 1/10/2006 11 Tue 10 1/11/2006 12 Wed 11 1/12/2006 12 Thu 12 1/13/2006 14 Fri 13 1/14/2006 14 Sat 14 1/15/2006 16 Sun 15 1/16/2006 17 Mon 16 1/17/2006 18 Tue 17 1/18/2006 19 Wed 18 1/19/2006 19 Thu 19 1/20/2006 20 Fri 20 1/21/2006 22 Sat 21 1/22/2006 23 Sun 22 1/23/2006 23 Mon 23 1/24/2006 24 Tue 24 1/25/2006 25 Wed 25 1/26/2006 27 Thu 26 1/27/2006 27 Fri 27 1/28/2006 29 Sat 28 1/29/2006 29 Sun 29 1/30/2006 31 Mon 30 1/31/2006 32 Tue 31 2/1/2006 32 Wed 1 2/2/2006 33 Thu 2 2/3/2006 35 Fri 3 2/4/2006 35 Sat 4 2/5/2006 37 Sun 5 2/6/2006 38 Mon 6 2/7/2006 38 Tue 7 2/8/2006 39 Wed 8 2/9/2006 41 Thu 9 2/10/2006 42 Fri 10 2/11/2006 43 Sat 11 and so on. Next switch on autofilter and for column C select Sun. You will see: date measure day date 1/1/2006 1 Sun 1 1/8/2006 9 Sun 8 1/15/2006 16 Sun 15 1/22/2006 23 Sun 22 1/29/2006 29 Sun 29 2/5/2006 37 Sun 5 2/12/2006 44 Sun 12 2/19/2006 50 Sun 19 2/26/2006 57 Sun 26 3/5/2006 64 Sun 5 3/12/2006 71 Sun 12 3/19/2006 78 Sun 19 3/26/2006 86 Sun 26 4/2/2006 93 Sun 2 4/9/2006 100 Sun 9 4/16/2006 107 Sun 16 these are weekly samples If you use autofilter on column D selecting 1. You will see: date measure day date 1/1/2006 1 Sun 1 2/1/2006 32 Wed 1 3/1/2006 61 Wed 1 4/1/2006 92 Sat 1 5/1/2006 122 Mon 1 6/1/2006 152 Thu 1 7/1/2006 182 Sat 1 8/1/2006 213 Tue 1 9/1/2006 244 Fri 1 10/1/2006 275 Sun 1 11/1/2006 305 Wed 1 12/1/2006 336 Fri 1 or monthly samples -- Gary's Student "hello" wrote: I need to convert between high frequency (for example, daily) and low frequency (annual) data. Ideally I would like the ability to do this on an average basis, end of period basis, beginning of period basis, etc. Is this possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding data series to chart via macro | Charts and Charting in Excel | |||
2nd Axes - primary series data of only 1 series disappears! | Charts and Charting in Excel | |||
farmating a data series | Charts and Charting in Excel | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Extending a Chart Data Series from an Array - Can it be done? | Charts and Charting in Excel |