# Thread: Excel formula. finding the last 10

1. Registered User
Join Date
May 2010
Posts
8

## Unanswered: Excel formula. finding the last 10

Hi could someone help me.

I am trying to figure out an excel formula to caluclate the average of the last ten numbers.

I have three columns:

Column 1 - Date
Column 2 - Name
Column 3 - Levels

My rows exceed down to 708.

2. Registered User
Join Date
May 2010
Location
San Diego, CA
Posts
2
=average()

Here's an example...
=average(C2:C9)

It will take the average of C2 to C9.

3. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
When you say "last ten numbers", do you mean the last ten numbers physically within a column, or do you mean the ten smallest numbers in the column?

4. Registered User
Join Date
May 2010
Posts
8
I should clarify. I mean the last ten numbers at the end. Followed by another formula that will only take the last 20. I can do it by the =average() formula but my length chances every time. So I need it to do it automatically. After I refresh my page via a queries.
Is this making a little more sense?

5. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Assuming the numbers are contiguous - there are no spaces:

Define the following named formula:
Code:
```BIGNUM
=9.99999999999999E+307```
Then, assuming the last ten numbers you want to sum are in column D, use this:
Code:
`=AVERAGE(INDEX(D:D,MATCH(BIGNUM,D:D)-10+1):INDEX(D:D,MATCH(BIGNUM,D:D)))`
or variation thereof.

Hope that helps...
Last edited by Colin Legg; 05-10-10 at 21:38.

6. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
I've moved this thread into the Excel forum.

-PatP

7. Registered User
Join Date
May 2010
Posts
8
It worked!!!!! Thank you so much! I have been trying different formulas out for days with no luck. You really saved me.

THANKS AGAIN!!!!!

8. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
You're welcome. The numbers being together made the formula simpler than it would have been otherwise.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•