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

[Guest]

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

 

[Bernd]

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

Regards,
Bernd

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Peo Sjoblom]

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

 

[Harlan Grove]

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.

 

[Harlan Grove]

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.

 

[Erny]

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

 

[Bernd]

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

 

[Erny]

Hi Bernd,

If this can happen, I would use:

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

Kind regards,
Erny

 

[Harlan Grove]

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?

 

[Erny]

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