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!!!

 

[David Jessop]

Hi,

I don't think you can do this directly (or at least I couldn't get it to
work!). The two ways I found are to use either =OFFSET() or =INDIRECT().
Create a column with 2, 4, 6, ... in it (which you can drag down correctly).
Let's say this is column D. Then the formula

 

[David Jessop]

Hi,

I don't think you can do this directly (or I couldn't get it to work). The
two ways to to do this are using either =OFFSET() or =INDIRECT(). Suppose
you put 2, 4, 6 in column D, then put the formulae
=OFFSET($A$1,D1-1,0)
=OFFSET($A$1,D2-1,0)
or

=INDIRECT("A"&D1)
=INDIRECT("A"&D2)

Then this will do what you want.

Regards,

David Jessop

P.S. Obviously in the first case it would be easier to put 1, 3, 5 .. in the
D column, but for reasons of exposition the above is, I hope, more obvious.

 

[Max]

See one response at your multi-post in .worksheet.functions

Please do not multi-post

 

[Max]

Think we could use ROW() or ROWS() functions
as the incrementer for copying down*,
then add-on the necessary arithmetic adjustments to suit
the interval to be skipped and the starting cell / cell referenced

*Likewise, for copying across purposes, we could
use COLUMN() or COLUMNS() as the incrementer

For example, this was the response to the OP
in .worksheet.functions (his multi-post there)
--
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)
--
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Hi,

I don't think you can do this directly (or I couldn't get it to work). The
two ways to to do this are using either =OFFSET() or =INDIRECT(). Suppose
you put 2, 4, 6 in column D, then put the formulae
=OFFSET($A$1,D1-1,0)
=OFFSET($A$1,D2-1,0)
or

=INDIRECT("A"&D1)
=INDIRECT("A"&D2)

Then this will do what you want.

Regards,

David Jessop

P.S. Obviously in the first case it would be easier to put 1, 3, 5 .. in the
D column, but for reasons of exposition the above is, I hope, more

obvious.