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 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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.