How to offset certain cells after dragging down a formula
 

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+Sh eet1!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

How to offset certain cells after dragging down a formula
 

Just apply the same principle that you have already been provided:

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

--
Regards,
Tom Ogilvy



"Bram" wrote in message
...
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+Sh eet1!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