Drag across formula and but reference ONE column

Question

I am collecting 25 samples of a certain rock and in that, analyzing data on 8 different elements. I want to drag across a formula that, simply, would be =(C15/C3)

I have these selected rows:

and want to put them here:

But, of course, when I drag it across, it changes the column. Is there a way that I can change the row when I drag across? (both the denominator and the numerator need to change)

Answer 1

Use INDEX()

=INDEX($C:$C,COLUMN(A:A) -1 + 15)/INDEX($C:$C,COLUMN(A:A)-1 + 3)

The COLUMN(A:A) -1 is the counter, the part that moves to the next row as it is dragged across.

The 15 and 3 are the starting rows.