how to customize autofill in excel2010

Question

like making a rule or something. I am trying to autofill a function, the first cell is (B14-B2)/B2, the second is (B26-B14)/B14, the third is (B38-B26)/B26, so I wanna to increase number by 12, however, the excel didn't get it and try to fill in (B17-B5)/B5;(B29-B17)/B17;(B41-B29)/B29; how can I let excel autofill function in the way I want it to?

Answer 1

If you want your ratios in C2, C3, C4, ..., for example, enter

=(OFFSET($B$2,12*(ROW()-1),0) - OFFSET($B$2,12*(ROW()-2),0)) / OFFSET($B$2,12*(ROW()-2),0)

into C2 and autofill down to C3, C4, ....  ROW() returns the row number of the cell it is in, so we get

row #     12*(ROW()-1)     12*(ROW()-2)
  2            12                0
  3            24               12
  4            36               24

OFFSET($B$2, number, 0) refers to the cell number rows below B2 (and 0 columns to the right).