# How to shift the cell coordinate by constant number?

I want to pick 3000 data points from a large dataset. The data is placed in the cell, which coordinate constantly increase by number of 9. For instance the first data point of interest is in a cell A1, the second in A9, the third in A19. So how to make a formular to shift picking cell by 9 (something like that A1+9=A10, A10+9=A19 ....)?

edit retag close merge delete

Sort by » oldest newest most voted

From A1, 8 rows down to A9, 10 further rows down to A19.
I assume you meant for the second cell to be A10, as indicated in your final "something like ...".

If your formulas to pick data are in consecutive cells down a column, you can use OFFSET() and ROW(). Assuming your formulas are starting from row 1:
=OFFSET(A1;(ROW()-1)*9;0)
=OFFSET($A$1;(ROW()-1)*9;0)

• If your formulas start further down the sheet, subtract the starting row number from ROW() in the inner parenthesis.
• If your formulas are spread horizontally, you need the COLUMN() function instead of ROW().
more

1

better:

=OFFSET(A$1;(ROW()-1)*9;0) ( 2021-05-03 11:51:13 +0200 )edit better: =OFFSET(A$1;(ROW()-1)*9;0)

Updated accordingly, and to also paste a sensible "anchor address" with horizontal distribution. Thanks!

( 2021-05-03 12:21:46 +0200 )edit

Thanks a lot for the reply. Somehow it does not work, as it should. I will specify the problem. Lets say I have three data blocks: The first in A1, .., A9 the 2nd in A10, ..., A18, the 3rd in A19, ...A27. I want to have the value from A1, A10 and A19 in one separate column like A1, A10 and A19 values located for instance in B column, raws 1, 2, 3. So the 1st data point I can write in B1: =A1, the 2nd (in B2) =OFFSET(A$1;(RAW()-1)*9;0), the 3rd (in B3) =OFFSET(A$1;(RAW()-2)*9;0)?

( 2021-05-03 13:42:51 +0200 )edit

No.

In B1: =OFFSET(A\$1;(ROW()-1)*9;0)The offset will then be zero, so you will be referring to A1

• Copy that formula down as far as you need.
You should not need to edit the formula further.
• When the formula is pasted into in B2 it will evaluate to OFFSET(A1;(2-1)*9;0)=OFFSET(A1;9;0)=A10
• In B3 it will evaluate to OFFSET(A1;(3-1)*9;0)=OFFSET(A1;18;0)=A19
• ...

Note the spelling. Function ROW(), not RAW()

( 2021-05-03 14:52:09 +0200 )edit

Thank a lot for your help. Now it works. The problem was that I did not copy the formula down, but tried to change (ROW()-1) to (ROW()-2) etc. The tricky part is that you do not see, how the number under ROW() function changes, when you copy a formula down. Thanks a lot once more for solving my problem.

( 2021-05-03 16:07:46 +0200 )edit