find last value

[mcarter973]

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

[Cam]

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

This will return the last numeric entry in column A

[mcarter973]

thanks cam - that did the trick.

[Ekim]

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

[Aladin Akyurek]

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 ]

[XL-Dennis]

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