Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Function to automatically insert a new sheet as a result of data entry? | Excel Worksheet Functions |