Identifying the last number in a row.
 

Hi,

The columns represent the days of the month, i,e, 1 to 31

In a row of cells, some of which contain numbers, and some which are
empty, I need to identify the last number in that row with a formula
in say column 32. i.e I'm only interested in the last reported number,
but it may be in a different column in each row.

e.g.
A B C D E.... Col32

100 80 40 40
20 30 50 50

Can anyone suggest a formula for column 32 which will result in 40 &
50 in column 32 of the above example.

Many thanks,



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Identifying the last number in a row.
 

Richard,

Are you wanting a worksheet formula? If so, then try

=INDEX(2:2,1,MAX(IF(2:2<"",COLUMN(2:2)))-COLUMN(2:2)+1)


it's an array formula, so commit with Ctrl-Shift-Enter



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Richard Buttrey" wrote in
message ...
Hi,

The columns represent the days of the month, i,e, 1 to 31

In a row of cells, some of which contain numbers, and some which are
empty, I need to identify the last number in that row with a formula
in say column 32. i.e I'm only interested in the last reported number,
but it may be in a different column in each row.

e.g.
A B C D E.... Col32

100 80 40 40
20 30 50 50

Can anyone suggest a formula for column 32 which will result in 40 &
50 in column 32 of the above example.

Many thanks,



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Identifying the last number in a row.
 

On Wed, 30 Mar 2005 16:38:49 +0100, "Bob Phillips"
wrote:

Richard,

Are you wanting a worksheet formula? If so, then try

=INDEX(2:2,1,MAX(IF(2:2<"",COLUMN(2:2)))-COLUMN(2:2)+1)


it's an array formula, so commit with Ctrl-Shift-Enter


Bob,

Thanks for this. An array formula would be fine.

What are the references to 2:2 in your example? I think I may need to
change them to fit my specific worksheet. If it helps the columns I'm
trying to include are E:AA - slightly less than 31 because I don't
have columns for weekends.

Regards,

Richard
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Identifying the last number in a row.
 

On Wed, 30 Mar 2005 16:38:49 +0100, "Bob Phillips"
wrote:

Richard,

Are you wanting a worksheet formula? If so, then try

=INDEX(2:2,1,MAX(IF(2:2<"",COLUMN(2:2)))-COLUMN(2:2)+1)


it's an array formula, so commit with Ctrl-Shift-Enter

Bob,

Forget my follow up. I see what you mean. The 2s referred to row 2. In
my particular example I clearly need to change the 2:2 to a specific
range e.g. E2:AA2

That's brilliant. Works perfectly. I just need to sit down and
understand it now!

Many thanks again,

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________