Show the last # in a column

[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.
--
DR

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

[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


"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

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

[Ron Coderre]

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

[Alaskan in a Cubicle]

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