Excel find last value :: MrExcel Message Board
 Search   Memberlist   Usergroups   Favorites   Statistics   Register

 find last value

mcarter973
Board Regular

Joined: 25 Mar 2002
Posts: 64

Status: Offline

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

Thu Aug 29, 2002 4:00 pm

Cam
Board Master

Joined: 30 May 2002
Posts: 129
Flag:

Status: Offline

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

This will return the last numeric entry in column A

_________________
Cam B.

Thu Aug 29, 2002 4:07 pm

mcarter973
Board Regular

Joined: 25 Mar 2002
Posts: 64

Status: Offline

thanks cam - that did the trick.

Thu Aug 29, 2002 4:18 pm

Ekim
Board Master

Joined: 01 Jul 2002
Posts: 981
Location: Perth, Australia
Flag:

Status: Offline

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

Thu Aug 29, 2002 4:31 pm

.

Joined: 15 Feb 2002
Posts: 13645
Location: The Hague
Flag:

Status: Offline

quote:

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 ]

Thu Aug 29, 2002 4:40 pm

XL-Dennis
.

Joined: 28 Jul 2002
Posts: 1629
Location: Östersund, Sweden
Flag:

Status: Offline

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

_________________
Gone fishing

Thu Aug 29, 2002 6:39 pm
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

Forum Jump:
 Jump to: Select a forum MrExcel Forums----------------Excel QuestionsInternational forumMicrosoft AccessTechnical issues and Future development Holy Macro! Books----------------Holy Macro! Products The Lounge----------------Max Cells Lounge Announcements----------------About This BoardHall of Fame WinnersTest Here

Page 1 of 1

Forum Rules:
 You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forumYou cannot vote in polls in this forum