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
|