# Thread: Last entry (2002 SP-3)

1. ## 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.

2. ## 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) ),"")

3. ## 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!

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

Define BigNum as 9.99999999999999E+307

entered with just Enter

or use

=LASTROW(A:A)

6. ## 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.

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

Rory:
<img src=/S/bullseye.gif border=0 alt=bullseye width=45 height=15>
Thanks!

9. ## Re: Last entry (2002 SP-3)

Thanks for the input! I went with Rory's suggestion.

10. ## Re: Last entry (2002 SP-3)

Steve:

Thanks for the help! I went with Rory's suggestion.

11. ## 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. ## 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.

#### Posting Permissions

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