asked on

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

2toria

Yes, type this:-

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

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

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Raheman M. Abdul

VBA use function to calculate

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

Anil

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

Anil

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>

barry houdini

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

regards, barry

Anil

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>

riverbank

ASKER

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.