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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
That won't work, using INDIRECT is the only way when it comes to inserting
rows/columns
 
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.
 
Peo Sjoblom said:
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.
 
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
 
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
 
Hi Bernd,

If this can happen, I would use:

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

Kind regards,
Erny
 
Erny said:
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?
 
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
 
Back
Top