DIFFICULT: Converting monthly data into daily data, how

[sneeky]

Hi. I have two columns, one with a date every month for a couple of years (usually last day) and another column, with a value like. 0.23788 for that particular date. How do i break this down into a daily series with corresponding values. I think this is asking for some sort of regression or something, and data to be assumed from that as we not given a daily value and presume it from sort of maths/stats??? it's related to finance if that helps.
.............. SORRY I HAVE TO AMEND. IT'S NOT ASKING FOR THIS.

All it wants me to do, is break the month into days with the SAME value given for that month that's all. So if it is 0..2999 for 30th March - all days in march should have that value.
How would i do this - what formulas or similar can i do to break it down to days quickly with corresponding same value if the month is the same without manually typing it all in. 7
Thx

 

[sneeky]

i have added an image of what it is asking me to do, and I know it's not asking me to manually to this by drag and drop... wants me to use formulas and something which if the value of the last is XX so will all the dates that month..

This is a screenshot, jpeg:
https://www.dropbox.com/s/o5w9colxwakmtfq/TEST.jpg

 

[MARK858]

Seeing as it looks like a school project you will need to adjust the ranges yourself and read up on Sumproduct to answer question 4.

Excel Workbook
	A	B	C	D	E
1	31/01/1999	3		01/02/1999	7
2	27/02/1999	7		02/02/1999	7
3	12/03/1999	4		03/02/1999	7
4				01/03/1999	4
5				02/03/1999	4
6				03/03/1999	4
7				06/01/1999	3
8
Sheet2

 

[MARK858]

Before you ask about the year, I deliberately left it out so that you can learn the syntax.

 

[sneeky]

Before you ask about the year, I deliberately left it out so that you can learn the syntax.

Mark thanks a lot. sumproduct I came across before but couldn't get it to work for matching the date.
However, I'm struggling more with the splitting the months into their days/dates..I.e
The range. I really don't think it's expecting me to put a date in and drag it down a few thousand cells; I think it wants some function or technique where it will auto populalise all days from say, 1999-2002. Is that even possible? Or is drag down really the only option? Just seems unprofessional.

 

[MARK858]

Or is drag down really the only option?

In an isolated column (i.e. no data in the next column for autofill to work out where to fill to) and without Vba probably.

Mark thanks a lot. sum product came across before but couldn't get it to work for matching the date.

Matches fine in the example I posted as does the Index/Match below

Excel Workbook
	A	B	C	D	H
1	25/02/2000	3		01/02/1999	7
2	27/02/1999	7		02/02/1999	7
3	12/03/1999	4		03/02/2000	3
4	06/01/1999	9		01/03/1999	4
5				02/03/1999	4
6				03/03/1999	4
7				06/01/1999	9
Sheet2

 

[sneeky]

think i just hallucinated. brb

 

[MARK858]

should i be using match index, or a lookup?

Not my project and so can't answer that one. If it was me I would favour Index/Match but that's personal preference.

Sumproduct with date including year based on data I posted earlier

Excel Workbook
	A	B	C	D	E
1	25/02/2000	3		01/02/1999	7
2	27/02/1999	7		02/02/1999	7
3	12/03/1999	6		03/02/2000	3
4	06/01/1999	9		01/03/1999	6
5				02/03/1999	6
6				03/03/1999	6
7				06/01/1999	0
Sheet2

 

[sneeky]

=INDEX($G$2:$G$180,MATCH(L2-DAY(L2),INDEX($F$1:$F$180-DAY($F$1:$F$180),0),0))

this is working better, but a month ahead?

 

[MARK858]

Your ranges don't match, you are using 2 to 180 at the start and 1 to 180 in the second part (and i should have changed $A$3 & $B$3 to $A$4 and $B$4 in my last post).