Last Non Blank

[George W. Barrowcliff]

I have a row of numbers (Jan, Feb ..... Total)
I would like the last column (Total) to reflect the last non blank value in
the row.

I would appreciate any help or direction.

Thanks

[Peo Sjoblom]

=LOOKUP(2,1/(A3:M3<>""),A3:M3)

adapt to fit your range

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"George W. Barrowcliff" <[email protected]> wrote in message
news:%[email protected]...
>I have a row of numbers (Jan, Feb ..... Total)
> I would like the last column (Total) to reflect the last non blank value
> in the row.
>
> I would appreciate any help or direction.
>
> Thanks
>

[Ron Coderre]

Try something like this:

With a list of values (or blanks) in A1:L1

The value of the last non-blank cell in that range is
M1: =INDEX(A1:L1,MAX(MATCH(10^99,A1:L1),MATCH(REPT("z",255),A1:L1)))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"George W. Barrowcliff" wrote:

> I have a row of numbers (Jan, Feb ..... Total)
> I would like the last column (Total) to reflect the last non blank value in
> the row.
>
> I would appreciate any help or direction.
>
> Thanks
>
>
>

[Ron Coderre]

Peo

Re: =LOOKUP(2,1/(A3:M3<>""),A3:M3)

That is much better than the bloated formula I came up with.
(sometimes it would be nice if we could retract a post so others won't waste
their time reading it)

***********
Regards,
Ron

XL2002, WinXP


"Peo Sjoblom" wrote:

> =LOOKUP(2,1/(A3:M3<>""),A3:M3)
>
> adapt to fit your range
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Excel 95 - Excel 2007
> Northwest Excel Solutions
> www.nwexcelsolutions.com
> "It is a good thing to follow the first law of holes;
> if you are in one stop digging." Lord Healey
>
>
> "George W. Barrowcliff" <[email protected]> wrote in message
> news:%[email protected]...
> >I have a row of numbers (Jan, Feb ..... Total)
> > I would like the last column (Total) to reflect the last non blank value
> > in the row.
> >
> > I would appreciate any help or direction.
> >
> > Thanks
> >
>
>
>

[Peo Sjoblom]

Hi Ron

"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
> Peo
>
> Re: =LOOKUP(2,1/(A3:M3<>""),A3:M3)
>
> That is much better than the bloated formula I came up with.
> (sometimes it would be nice if we could retract a post so others won't
> waste
> their time reading it)


Many are the times through the years when I have posted something and wanted
to cancel it as soon as I saw another answer (yours included), although
there is a cancellation option in most newsreaders I have never found one
that really worked, besides once Google has updated it, it's there for
"eternity" <bg>


Peo

[George W. Barrowcliff]

Thank you both for your efforts. Works exactly like I wanted.


"George W. Barrowcliff" <[email protected]> wrote in message
news:%[email protected]...
>I have a row of numbers (Jan, Feb ..... Total)
> I would like the last column (Total) to reflect the last non blank value
> in the row.
>
> I would appreciate any help or direction.
>
> Thanks
>

[Aladin Akyurek]

Faster...

=LOOKUP(9.99999999999999E+307,A3:M3)

if the last numeric value is what the OP wants.

Peo Sjoblom wrote:
> =LOOKUP(2,1/(A3:M3<>""),A3:M3)
>
> adapt to fit your range
>