Hi guys, So I have a quarterly data for an index and I need to convert it to a monthly data by averaging the two quarterly points. For example if Sept 08 = 120 and Jun 08 = 126 then I would take the difference (6) and allocate it between July and August . So July = 122 and Aug = 124… Is there a formula to do it? Thanks in advance…

So, you have 4 columns: Say Jun is in cell A1 Jun Jul Aug Sep 120 x y 126 At the risk of oversimplifying what you’re trying to do, couldn’t you just say ($D2 - $A2) / 3 + A2 in Cell B2 and copy it over?

I tried… But then I have to retype the formula for each quarter

in a similar vain to Bankin’s solution, but don’t use absolute references ($) next to the columns. so A2= 120 B2= (D2 - A2)/3 + A2 C2= (D2 - A2)/3*2 + A2 D2=126 then copy B2 & C2 across the empty spaces. edit— just to add, it will probably be easier if you put the formula in Line 3, and have your data in Line2 such that A2 = 120 B3 = (D3 - A3)/3 + A3

whooshy Wrote: ------------------------------------------------------- > in a similar vain to Bankin’s solution, but don’t > use absolute references ($) next to the columns. > > so > A2= 120 > B2= (D2 - A2)/3 + A2 > C2= (D2 - A2)/3*2 + A2 > D2=126 > > > then copy B2 & C2 across the empty spaces. > > No that formula uses the wrong months… > > > edit— > > just to add, it will probably be easier if you put > the formula in Line 3, and have your data in > Line2 > > such that > A2 = 120 > B3 = (D3 - A3)/3 + A3 the 3 > C3 = (D3 - A3)/3*2 + A3 3 again > D2 = 126 > > A3 = blank > D3 = blank > > - drag A3-D3 using excel’s autofill for the entire > row. > - copy the row, paste special, and skip blanks.

march april may june july august september 120 126 132 120 122 124 126 128 130 132 I used this formula =IF(AND(E24="",D24=""),((SUM(F24:H24)-SUM(C24:E24))/3)*2+C25,IF(E24="",((SUM(F24:H24)-SUM(C24:E24))/3)+D25,E24))

what do you mean by “using the wrong month” if A2 = 120, and D2 = 126 then B2 = 122, and C2 = 124 If A2 = 126 and D2 = 120, then B2 = 124, and C2 = 122 Though i did make a mistake on the second half, instead of autofilling A3-D3, you should autofill the range A3-C3 to the end of the row.

Dude its easy, say you have this data: A1: 110 A2: blank A3: blank A4: 115 A5: blank A6: blank A7: 120 A8: blank A9: blank A10: 126 Then in B1 put =A1 In B2 put =(OFFSET(A2,2,0)-OFFSET(A2,-1,0))/3+B1 In B3 put =(OFFSET(A3,1,0)-OFFSET(A3,-2,0))/3+B2 Then highlight B1 thru B3 and drag it down and it works. Voila

whooshy Wrote: ------------------------------------------------------- > what do you mean by “using the wrong month” > > if A2 = 120, and D2 = 126 > then B2 = 122, and C2 = 124 > > If A2 = 126 and D2 = 120, then > B2 = 124, and C2 = 122 > > > > Though i did make a mistake on the second half, > instead of autofilling A3-D3, you should autofill > the range A3-C3 to the end of the row. Yep… I didn’t see the edit part when I posted… It worked! thanks a lot!

Thanks guys for your responses! All three suggestions worked:)