Formula Question... LookUP
 

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

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

.

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

.

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" wrote in message
...
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

.