Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: Last Value

  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts
    I'm trying to find the last value in a column (I've asked this before but the formula is weird in that post - has a happy face in it).
    I can't seem to make it work. I entered:
    =INDEX(B6:B365,MATCH(9.99999999999999E+307,B)
    but get and error:
    #NAME?

    Any help greatly appreciated.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    [quote name='bfxtrfcmgr' post='783547' date='08-Jul-2009 09:09']I'm trying to find the last value in a column (I've asked this before but the formula is weird in that post - has a happy face in it).
    I can't seem to make it work. I entered:
    =INDEX(B6:B365,MATCH(9.99999999999999E+307,B)
    but get and error:
    #NAME?

    Any help greatly appreciated.[/quote]
    What post number was that - I'm having trouble finding it.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

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

    or somewhat shorter

    =LOOKUP(9.99999999999999E+307,B:B)

    If you only want to look at B6:B365:

    =LOOKUP(9.99999999999999E+307,B6:B365)

  4. #4
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts
    [quote name='WebGenii' post='783550' date='08-Jul-2009 08:14']What post number was that - I'm having trouble finding it.[/quote]

    Hans had the answer for me (a guardian angel of mine):
    =LOOKUP(9.99999999999999E+307,B6:B365)

    I haven't been around much since the new format and I can't (believe it or not) find the post number - certainly it can't be "3" in the far right corner.
    Little help?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    I think you are blending two different formulas from that previous post Final Value in a Column
    There were two options offered:
    =LOOKUP(9.99999999999999E+307,A:A)

    and I see Hans has beat me to the rest ...
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='783552' date='08-Jul-2009 08:15']Try

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

    or somewhat shorter

    =LOOKUP(9.99999999999999E+307,B:

    If you only want to look at B6:B365:

    =LOOKUP(9.99999999999999E+307,B6:B365)[/quote]

    Thanks friend! I opted with the last one and it works great.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

  8. #8
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Question

    [quote name='WebGenii' post='783557' date='08-Jul-2009 08:25']I think you are blending two different formulas from that previous post Final Value in a Column
    There were two options offered:
    =LOOKUP(9.99999999999999E+307,A:A)

    and I see Hans has beat me to the rest ...[/quote]

    Hey! It happened again when I quoted Hans in my reply to him.
    I leave for a few months, come back, and then break the Lounge - GREAT!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    This version of the Lounge uses shortcuts for smileys, e.g. :) becomes and B) becomes
    If you want to use one of those shortcuts as "normal" text, you have to be very careful...

  10. #10
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='783562' date='08-Jul-2009 08:38']This version of the Lounge uses shortcuts for smileys, e.g. :) becomes and B) becomes
    If you want to use one of those shortcuts as "normal" text, you have to be very careful... [/quote]

    I kinda figured. . . so what's the trick? Top secret stuff?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can make one of the characters bold, for example. I typed this: [b]B[/b]) to get B)

  12. #12
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='783564' date='08-Jul-2009 08:55']You can make one of the characters bold, for example. I typed this: [b]B[/b]) to get B)[/quote]
    You are always so creative. Thanks!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  13. #13
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Here is a more elegant and robust formula from Dick Kusleika
    {=INDEX(A:A,MAX(NOT(ISBLANK(A:A))*(ROW(A:A)),1))}

    I like a couple of things about this formula
    1 - it will recognize both text and numbers
    2 - blanks don't bother it

    Finally, I think the core of the formula {=MAX(NOT(ISBLANK(A:A))*(ROW(A:A)),1)} will be useful for finding all sorts of "last cell" variations.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  14. #14
    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
    You may have to settle with some XL versions with the array (confirm with ctrl-shift-enter):
    =INDEX(A1:A65535,MAX(NOT(ISBLANK(A1:A65535))*(ROW( A1:A65535)),1))

    Earlier versions did not allow using the full column in the array formula...

    Steve

  15. #15
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    good call
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Page 1 of 2 12 LastLast

Posting Permissions

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