find last value

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: find last value

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    is there an excel formula for determining the last value entered in a range (column of data) or can the answer only be achieved thru writing code?

    thanks

  2. #2
    Board Regular
    Join Date
    May 2002
    Location
    Canada
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =lookup(9.99999999999999E+307,A:A)

    This will return the last numeric entry in column A


    Cam B.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks cam - that did the trick.

  4. #4
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If your data is in say B5:B50 -

    To find the last number:

    =INDEX(B5:B50,MATCH(9.99999999999999E+307,B5:B50))

    or

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


    If your range includes text (or a mixture of numbers and text) use:

    =INDEX(B5:B50,COUNTA(B5:B50),1)


    Regards

    Mike

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,469
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-08-29 12:31, Ekim wrote:
    If your data is in say B5:B50 -

    To find the last number:

    =INDEX(B5:B50,MATCH(9.99999999999999E+307,B5:B50))

    or

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


    If your range includes text (or a mixture of numbers and text) use:

    =INDEX(B5:B50,COUNTA(B5:B50),1)


    Regards

    Mike
    The last one won't work when you have blanks interspersed with mixed data.


    [ This Message was edited by: Aladin Akyurek on 2002-08-29 12:42 ]

  6. #6
    Board Regular XL-Dennis's Avatar
    Join Date
    Jul 2002
    Location
    Östersund, Sweden
    Posts
    1,920
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    mcarter973,

    If there exist no empty cells in the range then following will work:

    =INDEX(A:A,COUNTA(A:A))

    If there exist empty cells then following array-formula will find the last value:
    {=INDEX(A:A,MAX(ROW(1:1000)*(A1:A1000>0)))}

    (You use Ctrl+Shift+Enter when entering this formula and XL will add the brackets.)

    If You´re not comfortable with array-formulas following non-array formula will also find the last value:
    =OFFSET(A1,MATCH(MAX(A1:A1000)+1,A1:A1000)-1,0)

    You may need to adjust the width of the range, i e change A1000.

    Kind regards,
    Dennis


    Kind regards,
    Dennis

    Developing solutions with B4J and B4A

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com