Keeping a range constant when inserting rows

[Jeff Olson]

Hello,

I'm trying to keep a range of cells constant within a
function when I insert a row (e.g. average(a1:a6) becomes
average(a1:a7) but I want it to keep the a1:a6 range).
Even if I use absolute cell references ($a$1:$a$6), it
doesn't help.

I would greatly appreciate any ideas.

Thanks,
Jeff

 

[EarlK]

Jeff,

In your formula, use:

=AVERAGE(INDIRECT("A1:A6"))

Absolute cell references (dollar signs) do one thing only: They keep any
copies you make of the cell references from changing relatively as they're
copied. They still change when the cells to which they refer are moved (as
with an insert or delete).

 

[Jeff Olson]

EarlK,

Thanks so much - exactly what I wanted!

Jeff

 

[cecil]

I am trying to do the same only using about 2,000 rows. Is
there a way to use this and not have to manually put in
each cell. In other words, I am using =VALUE(A1) in cell
A2. And I want to do that from A1 through A2000 but using
your formula below, it won't allow the copy function with
changing A1 to A2 and so forth.

 

[Dave Peterson]

So you want to end up with 2000 formulas like:

=value(indirect("a157"))

How about a couple of steps.

first put this formula in your cell

="$$$VALUE(INDIRECT(""A"&ROW(A1)&"""))"
Drag it down 2000 rows.

You'll end up with a cells that evaluate to this:

$$$VALUE(INDIRECT("A1"))
$$$VALUE(INDIRECT("A2"))
$$$VALUE(INDIRECT("A3"))
$$$VALUE(INDIRECT("A4"))

Now select that range of 2000 cells.
Edit|copy
Edit|paste special|Values

Now Edit|replace
$$$
with
= (equal sign)

You've got 2000 formulas.

An aside: Do you really need the =Value() portion?