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?
If you want your ratios in
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)
C2 and autofill down to
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).