how can I drag formulas with other cell references

[Bram]

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.

 

[David McRitchie]

Hi Bram,
See http://www.mvps.org/dmcritchie/excel/snakecol.htm#snkAddr

B1: =stdev(OFFSET($A$1,6*ROW()-5,0):OFFSET($A$1,6*ROW(),0))
B2: =stdev(OFFSET($A$1,6*ROW()-5,0):OFFSET($A$1,6*ROW(),0))

It was easier to test and verify using SUM instead of stdev, but the formula is same
NOTE the formulas are in a different column than the data.