| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XL2002 - OFFSET function and LARGE function
Hi All
I'm trying to return a value using the OFFSET funtion based on the LARGEst number found in the Offset reference... something like =OFFSET(LARGE(B1:B3,1),0,-1) should return "Item 3" as it has the largest value A B 1 Item 1 6 2 Item 2 2 3 Item 3 9 Is there a way to do it? Have I just got my syntax wrong? Thanks Trevor Williams |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
XL2002 - OFFSET function and LARGE function
=INDEX(A:A,MATCH(MAX(B:B),B:B,0))
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Trevor Williams" wrote in message ... Hi All I'm trying to return a value using the OFFSET funtion based on the LARGEst number found in the Offset reference... something like =OFFSET(LARGE(B1:B3,1),0,-1) should return "Item 3" as it has the largest value A B 1 Item 1 6 2 Item 2 2 3 Item 3 9 Is there a way to do it? Have I just got my syntax wrong? Thanks Trevor Williams |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
XL2002 - OFFSET function and LARGE function
Brilliant, thanks Bob.
Not just my syntax, but completely the wrong function! Regards Trevor "Bob Phillips" wrote: =INDEX(A:A,MATCH(MAX(B:B),B:B,0)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Trevor Williams" wrote in message ... Hi All I'm trying to return a value using the OFFSET funtion based on the LARGEst number found in the Offset reference... something like =OFFSET(LARGE(B1:B3,1),0,-1) should return "Item 3" as it has the largest value A B 1 Item 1 6 2 Item 2 2 3 Item 3 9 Is there a way to do it? Have I just got my syntax wrong? Thanks Trevor Williams |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
XL2002 - OFFSET function and LARGE function
You can do it with OFFSET
=OFFSET(A1,MATCH(LARGE(B:B,1),B:B,0)-1,0) but as OFFSET is volatile, INDEX is better IMO -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Trevor Williams" wrote in message ... Brilliant, thanks Bob. Not just my syntax, but completely the wrong function! Regards Trevor "Bob Phillips" wrote: =INDEX(A:A,MATCH(MAX(B:B),B:B,0)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Trevor Williams" wrote in message ... Hi All I'm trying to return a value using the OFFSET funtion based on the LARGEst number found in the Offset reference... something like =OFFSET(LARGE(B1:B3,1),0,-1) should return "Item 3" as it has the largest value A B 1 Item 1 6 2 Item 2 2 3 Item 3 9 Is there a way to do it? Have I just got my syntax wrong? Thanks Trevor Williams |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| offset and Sum function | Excel Worksheet Functions | |||
| Offset Function | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Using min and max function XL2002 | Excel Worksheet Functions | |||
| Using the Offset Function | Excel Worksheet Functions | |||
Linear Mode
