I want to prevent a formula from being affected by insert line
 

I have a formula which is average A1 to A10
When i insert a line in A1 it modifies the formula
to avegare A2-A12 but I want it NOT to change.

Any suggestions ?

Thanks

I want to prevent a formula from being affected by insert line
 

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

Regards,
Bernd

I want to prevent a formula from being affected by insert line
 

I have a formula which is average A1 to A10
When i insert a line in A1 it modifies the formula
to avegare A2-A12 but I want it NOT to change.

Use $A$1 to $A$10 instead of just A1 to A10.

Rick

I want to prevent a formula from being affected by insert line
 

That won't work, using INDIRECT is the only way when it comes to inserting
rows/columns


--
Regards,

Peo Sjoblom


"Rick Rothstein (MVP - VB)" wrote in
message ...
I have a formula which is average A1 to A10
When i insert a line in A1 it modifies the formula to avegare A2-A12 but
I want it NOT to change.

Use $A$1 to $A$10 instead of just A1 to A10.

Rick

I want to prevent a formula from being affected by insert line
 

"Rick Rothstein \(MVP - VB\)" wrote...
I have a formula which is average A1 to A10
When i insert a line in A1 it modifies the formula
to avegare A2-A12 but I want it NOT to change.

Use $A$1 to $A$10 instead of just A1 to A10.

You don't seem to be too familiar with how Excel actually works. You
should try testing before responding. Enter the formula

=AVERAGE($A$1:$A$10)

in cell C1. Then insert two rows between rows 4 and 5. The formula
becomes

=AVERAGE($A$1:$A$12)

Absolute referencing only leaves references unchanged during copy and
paste or fill operations. It does nothing at all to prevent changes
during row/column insert/delete operations.

I want to prevent a formula from being affected by insert line
 

"Peo Sjoblom" wrote...
That won't work, using INDIRECT is the only way when it comes to
inserting rows/columns
....

Don't make overly broad assertions. Try to fubar

=AVERAGE(INDEX($1:$65536,1,1):INDEX($1:$65536,10,1 ))

with row insertion/deletion.

I want to prevent a formula from being affected by insert line
 

Actually I would prefer using then:

=AVERAGE(A$1:OFFSET(A$1;9;0))
this will not be affected by inserting lines.

Kind regards,
Erny

"StiDude" schrieb im Newsbeitrag
...
I have a formula which is average A1 to A10
When i insert a line in A1 it modifies the formula
to avegare A2-A12 but I want it NOT to change.

Any suggestions ?

Thanks

I want to prevent a formula from being affected by insert line
 

Hi Erny,

What if you insert a row before row 1?

Another suggestion:
function avg1to10() as double
avg1to10 =
application.worksheetfunction.average(range(cells( 1,1),cells(10,1)))
end function

[untested]

Regards,
Bernd

I want to prevent a formula from being affected by insert line
 

Hi Bernd,

If this can happen, I would use:

=AVERAGE(INDIRECT("A$1"):INDIRECT("A$10"))

Kind regards,
Erny

"Bernd" schrieb im Newsbeitrag
ups.com...
Hi Erny,

What if you insert a row before row 1?

Another suggestion:
function avg1to10() as double
avg1to10 =
application.worksheetfunction.average(range(cells( 1,1),cells(10,1)))
end function

[untested]

Regards,
Bernd

I want to prevent a formula from being affected by insert line
 

"Erny" wrote...
Hi Bernd,

If this can happen, I would use:

=AVERAGE(INDIRECT("A$1"):INDIRECT("A$10"))
....

Really? What benefits would your formula provide that the shorter

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

doesn't?

I want to prevent a formula from being affected by insert line
 

None probably...:-)

Thanks for the tip, I didn't think one could also use an area with INDIRECT
formula, and of course, using INDIRECT, the $ is not required anymore.
Your formula is shorter (and probably faster), but both would work!

Erny

"Harlan Grove" schrieb im Newsbeitrag
oups.com...
"Erny" wrote...
Hi Bernd,

If this can happen, I would use:

=AVERAGE(INDIRECT("A$1"):INDIRECT("A$10"))
...

Really? What benefits would your formula provide that the shorter

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

doesn't?