Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 . |
#5
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula question | Excel Worksheet Functions | |||
Multiple Criteria Lookup Question | Excel Discussion (Misc queries) | |||
copying LOOKUP formula | Excel Worksheet Functions | |||
How do I have a formula lookup a sheetname? | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |