drag and drop a formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a problem. This is what my sheet looks like:
sheet1
In column A is the name of the company. in column B the name of the company
and In column c is the monthly return of the company. I'm interested in the
compounded half year return. In a new worksheet (sheet2)I want the company
name in column A, the compounded half year return (first six months) in
column b and the compounded half year return (last six months) in column c.
The formula for the compounded half year return(first six months in cell b2
is
=((1+'sheet1'!a2)*(1+'sheet1'!a3)*(1+'sheet1'!a4)*(1+'sheet1'!a5)*(1+'sheet1'!a6)*(1+'sheet1'!a7))-1

The formula for the compounded half year return(last six months in cell c2 is:

=((1+Sheet1!A8)*(1+Sheet1!A9)*(1+Sheet1!A10)*(1+Sheet1!A11)*(1+Sheet1!A12)*(1+Sheet1!A13))-1

I want to drag down this formula so that the formula for cell b3 is

=((1+Sheet1!A14)*(1+Sheet1!A15)*(1+Sheet1!A16)*(1+Sheet1!A17)*(1+Sheet1!A18)*(1+Sheet1!A19))-1

and so on.

I hope my question is clear enough so that somebody can help me out.

Thanx and regards

Bram
 
You would need to adapt the same principle as previously provided by these
formulas

in A2 (for company name)
=Offset(Sheet1!$A$1,(ROW(C1)-1)*12+1,0,1,1)
in B2
=stdev(offset(sheet1!$A$1,(ROW(A1)-1)*12+2,0,6,1))
In C2
=stdev(offset(sheet1!$A$1,(ROW(A1)-1)*12+8,0,6,1))
then select A2:C2 and drag fill down the column
 
Back
Top