Reference every second cell in Excel formula

Question

I currently have a formula like this:

=ROUND((('Sheet1'!D77-'Sheet1'!D75)/'Sheet1'!D75)*100,1)

with the next cell below that:

=ROUND((('Sheet1'!D79-'Sheet1'!D77)/'Sheet1'!D77)*100,1)

What I want is to drag the formula down and to reference every 2nd cell. So for e.g. it should go from referencing D79-D77 to D81-D79 (and not D80-D78) etc. How can I go about doing this?

Thanks

Answer 1

You can also do it with INDEX, multiplying the row that the formula is in relative to the first row by 2 and adding it to either 75 or 77:

=ROUND(((INDEX(Sheet1!D:D,77+(ROWS(A$1:A1)-1)*2)-INDEX(Sheet1!D:D,75+(ROWS(A$1:A1)-1)*2))/INDEX(Sheet1!D:D,75+(ROWS(A$1:A1)-1)*2))*100,1)

Answer 2

Oh, this is interesting.

Assuming you have your first cell at

B1

and the value you are looking into is in column C, starting at cell

C1

To do this, you need to have number at column A. Start with number 1, counting up. (you may include this in dragging)

So side by side, you have number column at A, while the absolute percentage at column B, and values at C (in alternating row).

I did the arithmetic progression, combined with the address and indirect functions to do the task.

where:

an = a1 + (n-1) * d

in B1, the code should look like this:

=(INDIRECT("C"&(3+(A1-1)*2))-INDIRECT("C"&(1+(A1-1)*2)))/INDIRECT("C"&(1+(A1-1)*2))

I guess this should do it.

Cheers..