Formula Question... LookUP

[G]

I am using the follwing formula to calculate the last entry in a range..

=LOOKUP(1,1/(1-ISBLANK('04&05 Details'!C416:C428)),'04&05 Details'!C416:C428)

Is there a simple way to calculate the last but one ???

Any help greatly appreciated..

Thanks

[Oliver Ferns via OfficeKB.com]

G,
rephrase your question. I'm not sure what you mean by this. Are you trying
to calculate its position on the worksheet?

O

--
Message posted via http://www.officekb.com

[Biff]

Hi!

>Is there a simple way to calculate the last but one ???

Does that mean if your formula returns the value in C420
you now want a formula that returns the value in C419?

=INDEX(A1:A15,MATCH(LOOKUP(1,1/(1-ISBLANK
(A1:A15)),A1:A15),A1:A15,0)-1)

That will work as long as you don't have duplicate values
in the range. Change the references to suit.

Biff

>-----Original Message-----
>I am using the follwing formula to calculate the last
entry in a range..
>
>=LOOKUP(1,1/(1-ISBLANK('04&05 Details'!C416:C428)),'04&05
Details'!C416:C428)
>
>Is there a simple way to calculate the last but one ???
>
>Any help greatly appreciated..
>
>Thanks
>
>.
>

[G]

Hi Biff,

Thanks for the reply ..

You are correct in your assumption howvere there will be duplicate antries
in this column ..

Thanks


"Biff" wrote:

> Hi!
>
> >Is there a simple way to calculate the last but one ???
>
> Does that mean if your formula returns the value in C420
> you now want a formula that returns the value in C419?
>
> =INDEX(A1:A15,MATCH(LOOKUP(1,1/(1-ISBLANK
> (A1:A15)),A1:A15),A1:A15,0)-1)
>
> That will work as long as you don't have duplicate values
> in the range. Change the references to suit.
>
> Biff
>
> >-----Original Message-----
> >I am using the follwing formula to calculate the last
> entry in a range..
> >
> >=LOOKUP(1,1/(1-ISBLANK('04&05 Details'!C416:C428)),'04&05
> Details'!C416:C428)
> >
> >Is there a simple way to calculate the last but one ???
> >
> >Any help greatly appreciated..
> >
> >Thanks
> >
> >.
> >
>

[Ragdyer]

Try this if you might have blanks and duplicates within the range:

=INDEX(A1:A100,MAX(MATCH({"zzzzzzzzzz",9.9999999E+307},A1:A100)-1))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"G" <[email protected]> wrote in message
news:[email protected]...
> Hi Biff,
>
> Thanks for the reply ..
>
> You are correct in your assumption howvere there will be duplicate antries
> in this column ..
>
> Thanks
>
>
> "Biff" wrote:
>
> > Hi!
> >
> > >Is there a simple way to calculate the last but one ???
> >
> > Does that mean if your formula returns the value in C420
> > you now want a formula that returns the value in C419?
> >
> > =INDEX(A1:A15,MATCH(LOOKUP(1,1/(1-ISBLANK
> > (A1:A15)),A1:A15),A1:A15,0)-1)
> >
> > That will work as long as you don't have duplicate values
> > in the range. Change the references to suit.
> >
> > Biff
> >
> > >-----Original Message-----
> > >I am using the follwing formula to calculate the last
> > entry in a range..
> > >
> > >=LOOKUP(1,1/(1-ISBLANK('04&05 Details'!C416:C428)),'04&05
> > Details'!C416:C428)
> > >
> > >Is there a simple way to calculate the last but one ???
> > >
> > >Any help greatly appreciated..
> > >
> > >Thanks
> > >
> > >.
> > >
> >