Prev: combo box
Next: How to generate a cell reference from multiple cells
From: Jen S on 22 Mar 2010 19:44
I am looking for a formula that would have the last number listed in a column
listed in the total or summary row of the spreadsheet.
Column A lists the month of the year Column B lists # of files pending
Jan 10
Feb 12
Mar 7


Summary -- I want this to show the last number in Column B -- in this
example 7. Thank you.
From: FSt1 on 22 Mar 2010 20:05
hi
in a cell of your choosing, enter..
=OFFSET(B1,COUNTIF(B1:B100,">0")-1,0)

regards
FSt1

"Jen S" wrote:

> I am looking for a formula that would have the last number listed in a column
> listed in the total or summary row of the spreadsheet.
> Column A lists the month of the year Column B lists # of files pending
> Jan 10
> Feb 12
> Mar 7
>
>
> Summary -- I want this to show the last number in Column B -- in this
> example 7. Thank you.
From: T. Valko on 22 Mar 2010 22:11
Another one...

=LOOKUP(1E100,B:B)

--
Biff
Microsoft Excel MVP


"Jen S" <JenS(a)discussions.microsoft.com> wrote in message
news:53135FAA-B01B-4FA3-B713-977BF34EDDFF(a)microsoft.com...
>I am looking for a formula that would have the last number listed in a
>column
> listed in the total or summary row of the spreadsheet.
> Column A lists the month of the year Column B lists # of files
> pending
> Jan
> 10
> Feb
> 12
> Mar
> 7
>
>
> Summary -- I want this to show the last number in Column B -- in this
> example 7. Thank you.


From: Ziggy on 25 Mar 2010 15:13
On Mar 22, 8:11 pm, "T. Valko" <biffinp...(a)comcast.net> wrote:
> Another one...
>
> =LOOKUP(1E100,B:B)
>
> --
> Biff
> Microsoft Excel MVP
>
> "Jen S" <J...(a)discussions.microsoft.com> wrote in message
>
> news:53135FAA-B01B-4FA3-B713-977BF34EDDFF(a)microsoft.com...
>
>
>
> >I am looking for a formula that would have the last number listed in a
> >column
> > listed in the total or summary row of the spreadsheet.
> > Column A lists the month of the year       Column B lists # of files
> > pending
> > Jan
> > 10
> > Feb
> > 12
> > Mar
> > 7
>
> > Summary -- I want this to show the last number in Column B -- in this
> > example 7. Thank you.- Hide quoted text -
>
> - Show quoted text -

=INDIRECT("C"&MAX(IF($C$35:$C$52<>0,ROW($C$35:$C$52))))

This is an Array formula

Cntr-Alt-Enter to implement
 | 
Pages: 1
Prev: combo box
Next: How to generate a cell reference from multiple cells