wrong reference after dragging down a formula

[Bram]

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

[Tom Ogilvy]

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" <Bram@discussions.microsoft.com> wrote in message
news:AA60F127-E4FD-4B50-8DE1-DF5BD18A6084@microsoft.com...
> 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
>