find last value

Thanks:  0
Likes:  0

1.
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. =lookup(9.99999999999999E+307,A:A)

This will return the last numeric entry in column A

3. thanks cam - that did the trick.

4. 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. 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.
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

## User Tag List

#### Posting Permissions

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