Ask Your Question

How to shift the cell coordinate by constant number?

asked 2021-05-03 09:16:40 +0200

Zigmantas gravatar image

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2021-05-03 11:10:48 +0200

keme gravatar image

updated 2021-05-03 12:23:33 +0200

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 ...".
Please double check your data intervals.

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:

  • 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().
  • If your layout is not so straightforward, please provide detail.
edit flag offensive delete link more





karolus gravatar imagekarolus ( 2021-05-03 11:51:13 +0200 )edit



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

keme gravatar imagekeme ( 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)?

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


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

keme gravatar imagekeme ( 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.

Zigmantas gravatar imageZigmantas ( 2021-05-03 16:07:46 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-05-03 09:16:40 +0200

Seen: 20 times

Last updated: May 03