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

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

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

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

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

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