obtaining value every 10th rows 3

[COEngineeer]

Lets say Column A I have a column full of data (lets say 100 data). What formula do I need to put on B so I can easily drag it down to however many row so that it will obtain A10,A20,A30...,A100. ? Am I making any sense? I dont want to type in =A10, =A20 .... etc. manually.

Never, but never question engineer's judgement

 

[MintJulep]

=IF(MOD(ROW(A1),10)=0,A1,"")

 

[handleman]

=INDIRECT("A"&10*(CELL("row",B1)))

Put this formula in B1 and drag it down to return the value in A10, A20, etc.

-handleman, CSWP (The new, easy test)

 

[joerd]

Many ways to skin a cat:

[tt]=INDEX($A:$A,10*ROW(A1))[/tt]
or
[tt]=OFFSET($A$1,10*ROW(A1)-1,0)[/tt]

I like the INDEX one because it is easy to understand, and the fastest. I also noted that using ROW(A1) is about 15% faster than CELL("row",A1)

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[COEngineeer]

thank you guys.. I have not tried any of them yet. I dont know which one to use yet. Appreciate it!

Never, but never question engineer's judgement