Show the last # in a column

  • Thread starter Alaskan in a Cubicle
  • Start date
A

Alaskan in a Cubicle

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.
 
V

Vito

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.
 
R

Ron Coderre

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
 
A

Alaskan in a Cubicle

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?
 
R

Ron Coderre

can you explain to me what the 10^99 is
actually saying?
Click to expand...

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
 
A

Alaskan in a Cubicle

Ahhh - now I see. Very clever - why didn't I think of that? Thanks again.
 
Top