Formula Question...PLEASE PLEASE help!

[Anant]

Hi everybody,
I am new to Excel and have a simple question about patterns in formulas. I
have a column like this:

=A2
=A4
=A6

How can I simply do a drag or something to continue that forumla pattern all
the way throughout the spreadsheet? I know if you just had A2 and dragged it
from the lower right corner, you'd see A3, A4, A5 below it. Also, I have a
column like this:

=RANK(C2,C:C)
=RANK(C4,C:C)
=RANK(C4,C:C)

Again, how can I simply continue this pattern for many rows? Thanks!!!

 

[Max]

One way ..

To get:

=A2
=A4
=A6

in successive cells when copying down

Put in the starting cell, say B2:

=INDIRECT("A"&ROWS($A$1:A1)*2-2+2)

B2 will return the same as: =A2

Copy B2 down to return in successive cells
(B2, B3, etc):

=A4
=A6
etc

Similarly, applied to:

=RANK(C2,C:C)
=RANK(C4,C:C)
=RANK(C6,C:C) [corrected]

Put in the starting cell, say D2:

=RANK(INDIRECT("C"&ROWS($A$1:A1)*2-2+2),C:C)

D2 will return the same as: =RANK(C2,C:C)

Copying D2 down returns in successive cells:
(D2, D3, etc):

=RANK(C4,C:C)
=RANK(C6,C:C)

Adapt this part: ... ROWS($A$1:A1)*2-2
in the starting cell's formula to suit the interval

If you want to skip 3 cells instead of 2,
i.e. get =A2, =A5, =A8, etc

just change it to: ROWS($A$1:A1)*3-3

Change the last number: ... +2)
to : ... +1)
if you want to begin in the starting cell
with: =A1 instead of : =A2
(the "+2" is just an arithmetic adjustment)

 

[Max]

Typo correction:

(B2, B3, etc):
(D2, D3, etc):

should read as:

 

[Aladin Akyurek]

Hi everybody, I am new to Excel and have a simple question about
patterns in formulas. I have a column like this:

=A2 =A4 =A6

How can I simply do a drag or something to continue that forumla
pattern all the way throughout the spreadsheet? I know if you just
had A2 and dragged it from the lower right corner, you'd see A3, A4,
A5 below it.

Supposing that you want every 2nd value from column A, starting with A2,
in column B from B2 downwards...

In B2 enter & copy down as far as needed:

=INDEX(A:A,CELL("Row",$A$2)+(ROW()-ROW(B$2))*2)

Also, I have a column like this:

=RANK(C2,C:C) =RANK(C4,C:C) =RANK(C4,C:C)

Again, how can I simply continue this pattern for many rows?
Thanks!!!

Supposing that you want to rank every 2nd value from column C, starting
with C2...

In D2 enter & copy down as far as needed:

=INDEX(C:C,CELL("Row",$C$2)+(ROW()-ROW(D$2))*2)

In E1 enter:

=LOOKUP(2,1/ABS($D$2:INDEX($D:$D,MATCH(9.99999999999999E+305,$D:$D))>0),

ROW($D$2:INDEX($D:$D,MATCH(9.99999999999999E+305,$D:$D))))-CELL("Row",$D$2)+1

In D2 enter & copy down:

=IF(ROW()-ROW(D$2)+1<=$E$1,RANK(D2,$D$2:INDEX(D,$E$1+CELL("Row",$D$2)-1)),"")