drag formula down with other reference

[Bram]

Is it possible to drag a formula in that way that the result is not a
successive cell number

example
cell A1 contains formula "=stdev(a2:a7)
cell a2 contains formula "=stdev(a8:a13)
Is it possible to drag the formula down so that cell a3 contains
"=stdev(a14:19) and cell a4 contains "=stdev(a20:a25) and so on for cells a5,
a6, a7 etc. etc.

 

[Tom Ogilvy]

you could use a function like offset

A1: =stdev(offset(sheet1!$A$1,(row(A1)-1)*6+1,0,6,1))

then drag fill down the column

However, the scenario you show would just result in a mess, so I assume you
are not really having the formula refer to the same column as the column in
which you place the formula. In my example, I added a sheet reference to
allude to data on a separate sheet than where the formula is being entered.

Nonetheless, it represent referring to the pattern you have asked about.
A2:A7
A8:A13
A14:A19
etc

 

[David McRitchie]

I answered this question of yours in a different group (excel.misc) are you also looking for a
programming solution? See reply to your other post
http://groups.google.com/[email protected]

Please post to only one group, and if you wanted a different solution you should have
continued there.

 

[Dave Peterson]

Are you sure you really want those formulas in column A? It seems pretty weird.

But I put these formula in B1 and dragged down:
="$$stdev(a"&((ROW()-1)*6)+2&":a"&((ROW()-1)*6)+7&")"

This formula evaluated to:
$$stdev(a2:a7)

So I selected that range (B:Bxx)
edit|copy
edit|paste special|values

So now I had strings that looked like:
$$stdev(a2:a7)

So with that range still selected:
edit|replace
what: $$
with: = (equal sign)
replace all

Excel converted my strings to formulas.

 

[Bram]

Many thanks for all of your replies. I think I didn't formulate my problem
clear enough. 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, A13:A14 etc represents January till December
The cells B1:B12, B13:B26 is the same company name per 12 cells
The cells C1:C12 is the monthly performance for company x
The cells C13:C24 is the monthly performance for company y

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