• Status: Solved
• Priority: Medium
• Security: Public
• Views: 277
• Last Modified:

# 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.

Doc1.doc
0
riverbank
Asked:
1 Solution

Commented:
Yes, type this:-

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

And that should do it for you.   Is that what you meant?
Matt
0

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.

=AVERAGE(INDIRECT("a1:a10"))

regards, barry
0

Senior Infrastructure Support Analyst & Systems DeveloperCommented:
VBA use function to calculate

Function Tryit()
Range("\$B\$1").Value = Application.WorksheetFunction.Average(Range("A1:A10"))
End Function
0

Data ManagerCommented:
Two named ranges defined:

ListA
=OFFSET(INDIRECT("A1"),0,0,COUNTA(Sheet1!\$A:\$A),1)

and List10 (The last 10 values)
=OFFSET(ListA,ROWS(ListA)-10,0,10,1)

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

=SUM(List10)

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

A>
Last10-aka.xls
0

Data ManagerCommented:
Alternatively if you like complex formulas

The sum you need is

=SUM(OFFSET(OFFSET(INDIRECT("A1"),0,0,COUNTA(Sheet1!\$A:\$A),1),ROWS(OFFSET(INDIRECT("A1"),0,0,COUNTA(Sheet1!\$A:\$A),1))-10,0,10,1))

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

A>
0

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

regards, barry
0

Data ManagerCommented:
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"!

A>
0

Author 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.
0
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.

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.