Results 1 to 8 of 8
  1. #1
    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.

    Please help!!!!

  2. #2
    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. #3
    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. #4
    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. #5
    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. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've moved this thread into the Excel forum.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

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