formula for averaging last 10 values after inserting rows

[darbary]

I need to find average of last 10 values in a column of data.

So my formula for averaging would be (say) =average(a1:a10).


I need to update a column of data by inserting rows.

Say I have added 2 rows, but now my formula changes to =average(a1:12) thus I do not get average of last 10 values & I need to manually change the formula to =average(a3:a12).


Is there any other way instead of manually changing the formula?


Thanks

 

[Luke M]

You need to first setup a Named Range

2003:

Insert - Named Range - Add

2007+

Formula - Name Manager - Add


Give it a name like "MyRange" with this formula:

=OFFSET($A$1,COUNTA($A:$A)-10,0,10,1)

Add, ok out.


*Note: I'm assuming there's no other data in column A.?* Formula works by figuring out many cells have data in them, and then offsetting by the correct amount.


Now, to get the average, your formula will be:

=AVERAGE(MyRange)

 

[darbary]

Dear Mr. Luke - There is a lot of Data in Column A and I need to add data monthly by using insert rows & then inputting the fresh Data & when I use insert row, it is then that the Average range includes the input data & not only the last 10 data, which I need.

 

[Luke M]

I'm sorry, but I got confused. Are you wanting the last 10 rows of data, as your first example showed, or the last entires that you make, regardless of where they are?


If having trouble visualizing the formula I gave, try a quick Google search for "dynamic ranges in excel" to see other examples.