I don't know if you will recive this since I am reply sooooo late and I
apologize
as I haven't had a chance to get back to you till now but thank you very much
your new formula works perfect :)


"Toppers" wrote:

Dan,

try:

=INDIRECT("B"&INT((MONTH($D1)-1)/3)+1+(YEAR($D1)-1947)*4)

Sorry about delay in reply .... time difference ... I have to sleep sometime!

HTH

"Dan Thompson" wrote:

Im not sure if you got my reply to your reply but your formula worked
unfortunatly my data set is more than just one year I need to be able to
convert
the data for several years and when I use your formula for more than one year
I end up with the same data for each year. in other words the values for
194701 100
194702 50
194703 200
194704 1000

end up being the same for 1948 1949 ect..

do you know how to fix this ?

Thanks in Advance
Dan Thompson


"Toppers" wrote:

Dan,

Try this:

Assume your quarterly data is A1:B4.

In (for example) d1, type 01/01/1947 (to use your year) and format as
date;in D2 downwards, type/copy down "=d1+7" until 31/12/1947 reached; this
will give weekly dates.

In E1 put "=indirect("B" & int((Month(D1)-1)/3)+1)" and copy this down to
last non-blank cell of column D. This should give weekly data corresponding
to the quarterly figures.

HTH

"Dan Thompson" wrote:

I have been trying to write a module in vba that will convert a dataset that
is quarterly data to a dataset that is a weekly data set, (as the data I have
is only available in quarterly data format) but I would like to cross plot it
against some different weekly data I have in a excel chart with a weekly date
basline.

Example...

My quarterly data set looks somthing like this.
YearQuarter Data
194701 100 <--Q1 Data
194702 50 <--Q2 Data
194703 200 <--Q3 Data
194704 1000 <--Q4 Data

This is what it should be after conversion macro is run.
1-Jan-47 100 <-Start of Q1 Data
8-Jan-47 100
15-Jan-47 100
22-Jan-47 100
29-Jan-47 100
5-Feb-47 100
12-Feb-47 100
19-Feb-47 100
26-Feb-47 100
5-Mar-47 100
12-Mar-47 100
19-Mar-47 100
26-Mar-47 100
2-Apr-47 50 <-- Start of Q2 Data
9-Apr-47 50
16-Apr-47 50
23-Apr-47 50
30-Apr-47 50
7-May-47 50
14-May-47 50
21-May-47 50
28-May-47 50
4-Jun-47 50
11-Jun-47 50
18-Jun-47 50
25-Jun-47 50
2-Jul-47 200 <--Start of Q3 Data
9-Jul-47 200
16-Jul-47 200
23-Jul-47 200
30-Jul-47 200
6-Aug-47 200
13-Aug-47 200
20-Aug-47 200
27-Aug-47 200
3-Sep-47 200
10-Sep-47 200
17-Sep-47 200
24-Sep-47 200
1-Oct-47 1000 <-- Start of Q4 Data
8-Oct-47 1000
15-Oct-47 1000
22-Oct-47 1000
29-Oct-47 1000
5-Nov-47 1000
12-Nov-47 1000
19-Nov-47 1000
26-Nov-47 1000
3-Dec-47 1000
10-Dec-47 1000
17-Dec-47 1000
24-Dec-47 1000
31-Dec-47 1000