Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How would I use a formula to find the last value or entry in a column. For example - a simple checkbook register where you keep a running balance. How you you reference that last balance if you wanted to be able to refer to it from another worksheet?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The following formula will return the last numeric value in column A:

    =INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you. Can you explain how that works please?

    [quote name='HansV' post='771321' date='19-Apr-2009 22:44']The following formula will return the last numeric value in column A:

    =INDEX(A:A,MATCH(9.99999999999999E+307,A:A))[/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    9.99999999999999E+307 is the largest value one can enter in an Excel sheet.

    MATCH without a third argument looks for the largest value in the range that is less than or equal to the search value, searching from top to bottom. Since the search value isn't found in this case, MATCH returns the last numeric value it encountered.

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Very clever. Thanks for the explanation.

    [quote name='HansV' post='771325' date='19-Apr-2009 23:01']9.99999999999999E+307 is the largest value one can enter in an Excel sheet.

    MATCH without a third argument looks for the largest value in the range that is less than or equal to the search value, searching from top to bottom. Since the search value isn't found in this case, MATCH returns the last numeric value it encountered.[/quote]

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='Don_Sadler' post='771686' date='22-Apr-2009 01:55']Very clever. Thanks for the explanation.[/quote]
    Hi Don
    One point in addition to Hans' explanation. The MATCH function in this case returns the location of the last and not necessarily the largest entry because it expects the range to be sorted in ascending order.
    Regards
    Don

Posting Permissions

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