Formula to convert monthly data to weekly data

My probleem is as follows:

I have 12 observations per variable over 2013. At the end of each month I gathered the average value of X of said month. What I want to do is convert these observations to a list of weekly observations, where each week in januari gets the value of the januari observation

Example:

Month 1 2 3 4 5 6 7 8 9 10 11 12
Value 2 3 4 5 6 7 8 9 10 11 12 13


And get it like:

Week Value
1 2
2 2
3 2
4 2
5 2
6 3
7 3

and so on.

Since I've got a lot of variables I'd like to use some kind of formula, anybody here can help me out?

Thanks!

=MONTH(A6*7+DATE(2013,1,1))
will convert a week number to a month number then you can use index/match or hlookup to pick out the value.

Thanks ragulduy, but I want to do it the other way around. I have monthly observations and I want to get a list per week.

Yes, that is what I provided you with.

i.e. if your months are in A1:A12, values are in B1:B12, week numbers in C1:C52 then in D1:
=VLOOKUP(MONTH(C1*7+DATE(2013,1,1)),A1:B12,2,0)
and copy down.

Ah now I get it ragulduy and Kaper, I'm afraid I needed that extra bit of explanation

Thanks so far, I'll see if I can get it done now

No problem, for future reference, if you upload a workbook with the layout of your data, rather than examples in the text of the post, we can give you the formulas with specific cell references as an example and it makes it easier to understand.. You can upload a workbook by clicking "go advanced" beneath the reply box and then "attachments".

Still not there yet, so I'll give it a try ragulduy.

The first tab has the observations per month, as given by the data analysis tool. What I want is in the second tab; use Object to VLOOKUP/HLOOKUP Cluster and Index, and get the monthly observations per week. I'm stuck


convert month to week.xlsx

See attachment, it's based on the data still being for 2013.

I feel stupid, it actually was as simple as both of you described

Glad to say that I finally got what I wanted, thanks a lot Kaper and raguldguy!

/SOLVED