Select every Nth cell in column? Help please

[abqhusker]

3 columns A2:C100 contains data. In E2:E14, I want a formula or
function that will get every 3rd cell from Column A, F2:F14 every 3rd
cell from Col B, G2:G14 every 3rdcell from Col C E.g.:

A B C D E F G
Days Week Mon RDays RWeek RMon
1 2 3 7 8 9
4 5 6 4 5 6
7 8 9 9 8 7
10 110 12 3 3 3
1 2 3
4 5 6
7 8 9
12 11 10
9 8 7
6 5 4
3 2 1
3 3 3

I'm guessing Vlookup somehow, but I sure cant figure out how to do it.
Appreciate any help you can provide. Basically, I'm just trying to get
a smaller set of data to work with in Cols E:G versus using the data in
Col A:C which currently is over 5000 rows. So I want to be able to get
every 20, 30 or 50 numbers. Hope I explained it good enough so that you
understand what I'm needing. Thanks again.

Ed

 

[Peo Sjoblom]

=INDEX($A$2:$C$1100,ROWS($A$2:$A2)*3,COLUMNS($A$2:A$2))

copy across 3 cells and then copy down will give you your values

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Peo Sjoblom]

Oops! Change $C$1100 to $C$100

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[abqhusker]

=INDEX($A$2:$C$1100,ROWS($A$2:$A2)*3,COLUMNS($A$2:A$2))

copy across 3 cells and then copy down will give you your values

That works fine; however, I am wondering if there is a way to do the
same thing without always having to include/select the 5000+ row range.
If not that's fine, this formula does exactly what I wanted. I wish I
understood what Index, Rows, and Columns function do and how this
formula works but I'm happy it works regardless of whether I understand
it or not. Thanks for answering right away. You guys are awesome.
Nice web site by the way.

Ed

 

[Peo Sjoblom]

You could use this

=OFFSET($A$2,ROWS($A$2:$A2)*3-1,COLUMNS($A$2:A$2)-1)

where you just have to put in the first upper left cell, however that
formula is volatile and if you have a lot of these formulas it might slow
down the performance

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[abqhusker]

You could use this

=OFFSET($A$2,ROWS($A$2:$A2)*3-1,COLUMNS($A$2:A$2)-1)

where you just have to put in the first upper left cell, however that
formula is volatile and if you have a lot of these formulas it might
slow down the performance

Thanks, Peo. This is more of what I as looking for. Both formualas
work perfectly. If there was one thing I wish I could be smart in excel
in, it's this type of formula building. You are really smart. Thanks
again.

Ed