A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Last entry in a column/row function?



 
 
Thread Tools Display Modes
  #1  
Old October 30th 06, 08:46 PM posted to microsoft.public.excel.worksheet.functions
Victor Delta
external usenet poster
 
Posts: 2
Default Last entry in a column/row function?

I have several spreadsheets which I use for data gathering (usually in
columns, but could be in rows) and where I need to do calculations
based on the last (ie most recent, at the bottom) entry in the column.

Although Excel has functions designed to help me find the largest,
smallest, average etc etc of these numbers, I have yet to find one
which returns the last entry. Of course, it is not hard to add an extra
(subsequently hidden) column which does the calculations (using the if
and isblank functions) but I'm sure there must be a better way to do it
- with a formula in a single cell.

Any suggestions please?

Thanks

V

Ads
  #2  
Old October 30th 06, 09:05 PM posted to microsoft.public.excel.worksheet.functions
Ron Coderre
external usenet poster
 
Posts: 2,118
Default Last entry in a column/row function?

Depending on what you consider to be the "last" item, see if one of these
does what you want:

The value of the last non-blank cell in ROW range
=LOOKUP(2,1/(A1:M1<>""),A1:M1)

The value of the last non-blank cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<>""),A1:A100)

The VALUE of the last numeric value in Col_A:
=LOOKUP(10^99,A:A)

The VALUE of the last numeric value in Row_1:
=LOOKUP(10^99,1:1)

The VALUE of the last text cell in Col_A
=LOOKUP(REPT("z",255),A:A)

The VALUE of the last text cell in Row_1
=LOOKUP(REPT("z",255),1:1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Victor Delta" wrote:

> I have several spreadsheets which I use for data gathering (usually in
> columns, but could be in rows) and where I need to do calculations
> based on the last (ie most recent, at the bottom) entry in the column.
>
> Although Excel has functions designed to help me find the largest,
> smallest, average etc etc of these numbers, I have yet to find one
> which returns the last entry. Of course, it is not hard to add an extra
> (subsequently hidden) column which does the calculations (using the if
> and isblank functions) but I'm sure there must be a better way to do it
> - with a formula in a single cell.
>
> Any suggestions please?
>
> Thanks
>
> V
>
>

  #3  
Old October 30th 06, 09:32 PM posted to microsoft.public.excel.worksheet.functions
Victor Delta
external usenet poster
 
Posts: 2
Default Last entry in a column/row function?

Ron

That's brilliant. The third and fourth formulae are just what I needed.

Many thanks.

V

PS Still find it surprising that Excel does not have a specific
function for this (eg LAST)

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Date & Time mully New Users to Excel 4 May 23rd 05 11:56 AM
Automatically up date time in a cell Mark Excel Discussion (Misc queries) 5 May 12th 05 12:26 AM
Conversion SVC Excel Worksheet Functions 9 February 28th 05 03:29 PM
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. Robert AS Excel Worksheet Functions 4 December 2nd 04 11:49 AM
Function to automatically insert a new sheet as a result of data entry? Mark Mulik Excel Worksheet Functions 2 November 28th 04 03:21 AM


All times are GMT +1. The time now is 08:31 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.