Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show the last # in a column
Seems simple, but I'm not figuring it out. I have a one-page sheet on which
others will periodically add information to some columns. At the bottom, I want a row to display the last valid number in that column - whether it's on the 5th row or the 40th row. So, if the cell is blank or has anything other than a number (like an invalid formula) it should go up to the last valid number. Finally, remind me how I get a cell to be blank if the result of the formula is either 0 or invalid because some source-cells in the formula have not yet been filled in - just so the worksheet is cleaner when printed. Thank you. -- DR |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show the last # in a column
For last value in a range: =LOOKUP(9.99999999999999E+307,B1:B100) adjust range to suit For returning a blank: e.g. =If(Isna(Vlookup.....),"",Vlookup(.....)) or =If(Vlookup(....)=0,"",Vlookup(....)) Where Vlookup(....) could be any condition. -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=558625 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show the last # in a column
Try this:
The ADDRESS of the last numeric value in Col_A: =CELL("address",INDEX(A:A,MATCH(10^99,A:A))) The VALUE of the last numeric value in Col_A: =LOOKUP(10^99,A:A) To hide the result if no numbers are in col_A: =IF(COUNT(A:A),LOOKUP(10^99,A:A),"") Does that help? *********** Regards, Ron XL2002, WinXP "Alaskan in a Cubicle" wrote: Seems simple, but I'm not figuring it out. I have a one-page sheet on which others will periodically add information to some columns. At the bottom, I want a row to display the last valid number in that column - whether it's on the 5th row or the 40th row. So, if the cell is blank or has anything other than a number (like an invalid formula) it should go up to the last valid number. Finally, remind me how I get a cell to be blank if the result of the formula is either 0 or invalid because some source-cells in the formula have not yet been filled in - just so the worksheet is cleaner when printed. Thank you. -- DR |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show the last # in a column
I needed the value & it worked - thank you. But because I'm of those people
who must know why - not just how - can you explain to me what the 10^99 is actually saying? -- DR "Ron Coderre" wrote: Try this: The ADDRESS of the last numeric value in Col_A: =CELL("address",INDEX(A:A,MATCH(10^99,A:A))) The VALUE of the last numeric value in Col_A: =LOOKUP(10^99,A:A) To hide the result if no numbers are in col_A: =IF(COUNT(A:A),LOOKUP(10^99,A:A),"") Does that help? *********** Regards, Ron XL2002, WinXP "Alaskan in a Cubicle" wrote: Seems simple, but I'm not figuring it out. I have a one-page sheet on which others will periodically add information to some columns. At the bottom, I want a row to display the last valid number in that column - whether it's on the 5th row or the 40th row. So, if the cell is blank or has anything other than a number (like an invalid formula) it should go up to the last valid number. Finally, remind me how I get a cell to be blank if the result of the formula is either 0 or invalid because some source-cells in the formula have not yet been filled in - just so the worksheet is cleaner when printed. Thank you. -- DR |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show the last # in a column
can you explain to me what the 10^99 is
actually saying? The 10^99 is just a much larger number than I ever anticipate finding in a worksheet. When that number is not found, the last numeric cell is returned. Note: Technically, 9.99999999999999E+307 is the largest nuimber Excel can handle, but for all of my purposes the 10^99 works just fine. Does that help? *********** Regards, Ron XL2002, WinXP "Alaskan in a Cubicle" wrote: I needed the value & it worked - thank you. But because I'm of those people who must know why - not just how - can you explain to me what the 10^99 is actually saying? -- DR "Ron Coderre" wrote: Try this: The ADDRESS of the last numeric value in Col_A: =CELL("address",INDEX(A:A,MATCH(10^99,A:A))) The VALUE of the last numeric value in Col_A: =LOOKUP(10^99,A:A) To hide the result if no numbers are in col_A: =IF(COUNT(A:A),LOOKUP(10^99,A:A),"") Does that help? *********** Regards, Ron XL2002, WinXP "Alaskan in a Cubicle" wrote: Seems simple, but I'm not figuring it out. I have a one-page sheet on which others will periodically add information to some columns. At the bottom, I want a row to display the last valid number in that column - whether it's on the 5th row or the 40th row. So, if the cell is blank or has anything other than a number (like an invalid formula) it should go up to the last valid number. Finally, remind me how I get a cell to be blank if the result of the formula is either 0 or invalid because some source-cells in the formula have not yet been filled in - just so the worksheet is cleaner when printed. Thank you. -- DR |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show the last # in a column
Ahhh - now I see. Very clever - why didn't I think of that? Thanks again.
-- DR "Ron Coderre" wrote: can you explain to me what the 10^99 is actually saying? The 10^99 is just a much larger number than I ever anticipate finding in a worksheet. When that number is not found, the last numeric cell is returned. Note: Technically, 9.99999999999999E+307 is the largest nuimber Excel can handle, but for all of my purposes the 10^99 works just fine. Does that help? *********** Regards, Ron XL2002, WinXP "Alaskan in a Cubicle" wrote: I needed the value & it worked - thank you. But because I'm of those people who must know why - not just how - can you explain to me what the 10^99 is actually saying? -- DR "Ron Coderre" wrote: Try this: The ADDRESS of the last numeric value in Col_A: =CELL("address",INDEX(A:A,MATCH(10^99,A:A))) The VALUE of the last numeric value in Col_A: =LOOKUP(10^99,A:A) To hide the result if no numbers are in col_A: =IF(COUNT(A:A),LOOKUP(10^99,A:A),"") Does that help? *********** Regards, Ron XL2002, WinXP "Alaskan in a Cubicle" wrote: Seems simple, but I'm not figuring it out. I have a one-page sheet on which others will periodically add information to some columns. At the bottom, I want a row to display the last valid number in that column - whether it's on the 5th row or the 40th row. So, if the cell is blank or has anything other than a number (like an invalid formula) it should go up to the last valid number. Finally, remind me how I get a cell to be blank if the result of the formula is either 0 or invalid because some source-cells in the formula have not yet been filled in - just so the worksheet is cleaner when printed. Thank you. -- DR |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i multiply two columns | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
2 Columns - Show extra items in Column B | Excel Discussion (Misc queries) | |||
match and count words | Excel Worksheet Functions | |||
hide column but show chart | Charts and Charting in Excel |