CODEWITHSUNDEEP
excel-formulaexcel-2010
Isabella Solaro
Isabella Solaro

Reputation: 13

Drag across formula and but reference ONE column

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:

enter image description here

and want to put them here:

enter image description 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)

Upvotes: 1

Views: 41

Answers (1)

Scott Craner
Scott Craner

Reputation: 152495

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.

enter image description here

Upvotes: 1

Related Questions