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 …)?
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:
=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 ofROW(). - If your layout is not so straightforward, please provide detail.
better:
=OFFSET(A$1;(ROW()-1)*9;0)
better:
=OFFSET(A$1;(ROW()-1)*9;0)
Updated accordingly, and to also paste a sensible “anchor address” with horizontal distribution. Thanks!
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)?
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()
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.