wrong reference after dragging down a formula
 

I have a large dataset (over 20000 rows). column A is the date, column b is
the company name, column c is the monthly performance per company.
So the cells A1:A12, represents January till December for company x
the cells A13:A26 represents January till december for company y
The cells B1:B12, company name x
the cells B13:b26 company name y
The cells C1:C12 is the monthly performance for company x
The cells C13:C24 is the monthly performance for company y
and so on

I want to compare the standard deviation of every company of the first six
months of the year and the last six months of the year. So I make a new
worksheet with in column A the company name, Column B the standard deviation
of the first 6 months and column C the standard deviation of the last 6
months.
In this new worksheet cell
B1 contains the formula =stdev('sheet1'!A2:A7)
C1 contains the formula =stdev('sheet1'!A8:A13)
B2 contain the formula =stdev('sheet1'!A14:A19)
C2 contains the formula =stdev('sheet1'!A20:A25)
If I drag down these formula from cell A2 to A3 and so on, the result is
=stdev('sheet1'!A4:A9) instead of =stdev('sheet1!A26:A31).

I hope that you understand what I mean and that you can help me solve this
'problem'.

Thanks and regards,

Bram

wrong reference after dragging down a formula
 

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 large dataset (over 20000 rows). column A is the date, column b
is
the company name, column c is the monthly performance per company.
So the cells A1:A12, represents January till December for company x
the cells A13:A26 represents January till december for company y
The cells B1:B12, company name x
the cells B13:b26 company name y
The cells C1:C12 is the monthly performance for company x
The cells C13:C24 is the monthly performance for company y
and so on

I want to compare the standard deviation of every company of the first six
months of the year and the last six months of the year. So I make a new
worksheet with in column A the company name, Column B the standard
deviation
of the first 6 months and column C the standard deviation of the last 6
months.
In this new worksheet cell
B1 contains the formula =stdev('sheet1'!A2:A7)
C1 contains the formula =stdev('sheet1'!A8:A13)
B2 contain the formula =stdev('sheet1'!A14:A19)
C2 contains the formula =stdev('sheet1'!A20:A25)
If I drag down these formula from cell A2 to A3 and so on, the result is
=stdev('sheet1'!A4:A9) instead of =stdev('sheet1!A26:A31).

I hope that you understand what I mean and that you can help me solve this
'problem'.

Thanks and regards,

Bram