Excel formula. finding the last 10

freeman77 · #1 (**permalink**) 05-05-10, 15:54

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.

Please help!!!!

CariNet · #2 (**permalink**) 05-10-10, 19:31

=average()

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

It will take the average of C2 to C9.

Colin Legg · #3 (**permalink**) 05-10-10, 20:36

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?

freeman77 · #4 (**permalink**) 05-10-10, 21:05

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?

Colin Legg · #5 (**permalink**) 05-10-10, 21:32

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

Pat Phelan · #6 (**permalink**) 05-10-10, 23:20

I've moved this thread into the Excel forum.

-PatP

freeman77 · #7 (**permalink**) 05-11-10, 14:40

It worked!!!!! Thank you so much! I have been trying different formulas out for days with no luck. You really saved me.

THANKS AGAIN!!!!!

Colin Legg · #8 (**permalink**) 05-11-10, 17:58

You're welcome. The numbers being together made the formula simpler than it would have been otherwise.

Internet Services

Web Development

Digital Marketing

Consumer Tech