Results 1 to 12 of 12
  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

    Last entry (2002 SP-3)

    What with being an Excel dunce (sic), I hope I can pose the question correctly; I don't even know enough to find the issue in help <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I have a need to return the last entry in a column to the top row of a different column. As these entries are calculated, I will need to return values that are greater than zero.
    C1=last entry greater than 0 in column F

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

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Last entry (2002 SP-3)

    If the result must display in cell F1, evaluating the last cell in column C:

    Put this formula into cell F1: =IF(INDEX(C:C,COUNTA(C:C))>0,INDEX(C:C,COUNTA(C:C) ),"")
    Regards,
    Rudi

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

    Re: Last entry (2002 SP-3)

    Rudi:

    Thanks for the reply! This is a little more complex than I thought, so I better get "real" with you on the cells involved. I'll start with one and presume the formula will work as well with the others.

    The first eleven rows contain both numeric and alpha entries. The first entry I want to return will be to C8, and it should be for the last entry in column D. I modified your suggestion slightly because I wasn't certaing if there would be a problem with alpha entries within the first eleven rows.
    =IF(INDEX(D121000,COUNTA(D121000))>0,INDEX(D121000,COUNTA(D121000)),"")
    The value that is being returned is one of three entries (can't tell which) between D40 and D43, and not the last entry, which is in D49. Weird! If it matters, there are no entries (null?) in a few cells in that column.

    Thanks!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    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: Last entry (2002 SP-3)

    I assume that it is not in row 1 (confirm with ctrl-shift-enter):

    =SUM(IF(ROW(F2:F65536)=MAX(IF((F2:F65536>0)*ISNUMB ER(F2:F65536),ROW(F2:F65536))),F2:F65536))

    This will work with blanks, negative numbers, zeroes, text

    Steve

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last entry (2002 SP-3)

    Define BigNum as 9.99999999999999E+307

    =INDIRECT(ADDRESS(MAX(MATCH(BigNum,A:A),MATCH(REPT ("z",90),A:A)),1))

    entered with just Enter

    or use

    =LASTROW(A:A)

    from the MOREFUNC addin

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Last entry (2002 SP-3)

    Bryan,

    [Edited because I forgot about the offset]

    You should be able to use something like:
    =INDEX(D121000,MAX((ROW(D121000)-11)*(D121000<>0)))
    array-entered in C8 (i.e. press Ctrl+Shift+Enter)
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    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: Last entry (2002 SP-3)

    Your first formula requires that both text and numbers be present in the list. It will also pick a zero or a negative number as the last item if they are the last numbers in the column (rather than choosing a number >0 earlier in the list)

    Steve

  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

    Re: Last entry (2002 SP-3)

    Rory:
    <img src=/S/bullseye.gif border=0 alt=bullseye width=45 height=15>
    Thanks!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Last entry (2002 SP-3)

    Thanks for the input! I went with Rory's suggestion.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  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

    Re: Last entry (2002 SP-3)

    Steve:

    Thanks for the help! I went with Rory's suggestion.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  11. #11
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last entry (2002 SP-3)

    Although the OP is satisfied with Rory's solution, a non array formula learned (stolen) from one of Aladin's post

    =LOOKUP(2,1/(A2:INDEX(D,MATCH(bignum,D))>0),A2:INDEX(D,MATCH(bignum,D)))

    BTW,

    Rory's formula also returns the last entry be it be text or greater than 0

  12. #12
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last entry (2002 SP-3)

    make that for non-numerical values are also involved...

    If D2 is the cell where the range starts:

    In E2 enter:

    =MATCH(9.99999999999999E+307,D)

    In E3 enter:

    =LOOKUP(2,1/((D2:INDEX(D,E2)>0)*ISNUMBER(D2:INDEX(D,E2))),D2:INDEX(D,E2))

    If the range of interest is definite like D21000, replace D with D21000.
    Microsoft MVP - Excel

Posting Permissions

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