Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    return last entered value (excel 2003)

    anyone know of a way to return the last value in a range of cells.
    a1:a10
    only a1:a6 have values in them the rest are blank
    is the a function that will return the last value in this case it is a6 at 600

    for example
    a1 200
    a2 300
    a3 400
    a4 600
    a5 800
    a6 600
    a7
    a8
    a9
    a10

    last value = 600

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: return last entered value (excel 2003)

    Try this formula:

    =INDEX(A1:A10,MATCH(9.99999999999999E+307,A1:A10))

    9.99999999999999E+307 is the largest number you can enter in a cell in Excel, so presumably it won't occur in your range. Therefore MATCH returns the index of the last non-blank value, and INDEX is used to return that value.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: return last entered value (excel 2003)

    A minor nit/clarification. (whcih I am sure you are aware of). Your formula returns the last number in the range. It may not be the "last non-blank value".

    If you text values in cells after that number, it will still return the number, even though there are non-blank cells after it.

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: return last entered value (excel 2003)

    Yep, thanks for pointing it out. I assumed that Matix only had numeric values.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: return last entered value (excel 2003)

    Yes, based on the example I presume this also.

    My concern was people seeing the "trick" and applying it in sheets with numeric and text values and not getting the results they anticipate.

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: return last entered value (excel 2003)

    yes you are correct my application will only apply to numbers, thanks for the trick i will add it to my list of tricks

Posting Permissions

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