Auto Increment Formula

[bgates]

Hi,

I hope someone can help me.

I'm trying to write a formula where one value is incremented automatically when clicking and dragging, while the other values don't change.

For example:

A$1/(A$2-1)
A$1/(A$2-2)
A$1/(A$2-3)
etc.

Cheers,

Bill

[Richard Buttrey]

Hi,

Assuming you enter the formula on row 1 then

=A$1/(A$2-ROW())

If it starts on any other row then just add the number of the row before the one on which the formula is entered. e.g. if the formula is on row 10 then

=A$10/(A$11-ROW()+9)

[Pete_UK]

Use this for the first formula:

=A$1/(A$2 - ROWS($1:1))

then copy down as required.

Hope this helps.

Pete

[bgates]

Thanks guys, both suggestions seem to work fine.

What about doing the same thing except clicking and dragging "up" the page instead of down, is this possible?

For example:

etc.
A$1/(A$2-3)
A$1/(A$2-2)
A$1/(A$2-1) start here and drag up

Cheers,

Bill

[Pete_UK]

It depends on which row you want that first formula to go into. If it is before row 100 (for example), then you could have this in the first cell:

=A$1/(A$2 - 100 + ROWS($1:99))

which is equivalent to:

=A$1/(A$2 - 100 + 99) = A$1/(A$2 - 1)

Then when you drag this up it will increment the number to be subtracted.

Hope this helps.

Pete

[bgates]

Thanks Pete, I'll let you know how it goes.

Cheers,

Bill