Calculating latest 10 results in excel 2003

Hi there,

I'm having real difficulty finding a formula to calculate the last 10 results in a range of cells.
Say I have 10-1 in Cells A1-A10. So 10 in A1, 9 in A2 etc...
I want to find a formula which will calculates the average of these cells, but everyday someone inputs a new figure at the top of this set in A1 by inserting a new cell.
So lets say they add 11 to A1, the rest of the column will have 10 in A2, 9 in A3 etc... all the way to A11. Each day a new figure is added at the top, pushing the others downwards.
Is there a formula which will calculate only the A1 through to A10 averages?

Thanks, Screenshot attached to help visualise.

Who is Participating?
barry houdiniConnect With a Mentor Commented:
If you always want to average A1:A10 and don't want that to change when you insert cells then try using INDIRECT, i.e.


regards, barry
Yes, type this:-


And that should do it for you.   Is that what you meant?
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
VBA use function to calculate

Function Tryit()
Range("$B$1").Value = Application.WorksheetFunction.Average(Range("A1:A10"))
End Function
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Two named ranges defined:


and List10 (The last 10 values)

The sum of the last 10 numbers is the attached file is always 55 (Sum of the last 10 numbers, 1 to 10) no matter how many cells you insert in A1.

The sum you need is always


Hope I understood this well. I tested on Excel 2003.

Alternatively if you like complex formulas

The sum you need is


With a word of warning, inserting a blank cell spoils the result. However if you always have numbers in column A it works.

barry houdiniCommented:
Wouldn't my suggestion be a little simpler?  :)

regards, barry
That's correct Barry. riverbank - Please ignore my answer. I actually calculated the sum of last 10 entries, not the first 10 entries. Obviously Read it in a hurry. "latest" is bit different from "last"!

riverbankAuthor Commented:
Thanks Barryhoudini, that has worked a treat. Thank you akajohn also for your suggestions, however barry's solution worked so I didn't try yours.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.