| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Max Offset
Hi, I've got a range of numbers, J5:J28. I can get the maximum value using: =max(j5:j28) However, I want to find that max number and find the offset value 1 column to the left. Is this possible within a formula? I've tried all sorts of combos with MAX and OFFSET but can't get this one. Any help would be appreciated. Thanks, Dan. -- Voodoodan ------------------------------------------------------------------------ Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#2
|
|||
|
|||
|
=OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,1)
or =INDEX(K5:K28,MATCH(MAX(J5:J28),J5:J28,0)) -- HTH Bob Phillips "Voodoodan" wrote in message ... Hi, I've got a range of numbers, J5:J28. I can get the maximum value using: =max(j5:j28) However, I want to find that max number and find the offset value 1 column to the left. Is this possible within a formula? I've tried all sorts of combos with MAX and OFFSET but can't get this one. Any help would be appreciated. Thanks, Dan. -- Voodoodan ------------------------------------------------------------------------ Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#3
|
|||
|
|||
|
Use: =OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,-1) Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#4
|
|||
|
|||
|
Blimey, any quicker guys and you'll be inventing time travel! Thanks very much, that's worked perfectly. Dan. -- Voodoodan ------------------------------------------------------------------------ Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#5
|
|||
|
|||
|
:) Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#6
|
|||
|
|||
|
I would recommend using
=OFFSET(J5,MATCH(MAX(J5:J28,0),J5:J28,0)-1,1) or =INDEX(K5:K28,MATCH(MAX(J5:J28,0),J5:J28,0)) While it is not needed specifically in this case, I would use the ,0 in a match whenever I do not definately know if the data is in ascending order. Is there any concern that there might be several cells with the max value? "Bob Phillips" wrote: =OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,1) or =INDEX(K5:K28,MATCH(MAX(J5:J28),J5:J28,0)) -- HTH Bob Phillips "Voodoodan" wrote in message ... Hi, I've got a range of numbers, J5:J28. I can get the maximum value using: =max(j5:j28) However, I want to find that max number and find the offset value 1 column to the left. Is this possible within a formula? I've tried all sorts of combos with MAX and OFFSET but can't get this one. Any help would be appreciated. Thanks, Dan. -- Voodoodan ------------------------------------------------------------------------ Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#7
|
|||
|
|||
|
Why the "-1" in the row argument?
When I from the formula bar highlite the MATCH(MAX(J5:J28),J5:J28,0) only and F9 it return the correct position WITHOUT the need for the -1. (*&%$^%$??? TIA, Jim May "mangesh_yadav" wrote in message news:mangesh_yadav.1pl7cy_1117023660.9539@excelfor um-nospam.com... Use: =OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,-1) Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#8
|
|||
|
|||
|
I think you got confused here bj, I did put a ,0 for an exact match in the
formulae. You have added it to the max, which does absolutely nothing :-) -- HTH Bob Phillips "bj" wrote in message ... I would recommend using =OFFSET(J5,MATCH(MAX(J5:J28,0),J5:J28,0)-1,1) or =INDEX(K5:K28,MATCH(MAX(J5:J28,0),J5:J28,0)) While it is not needed specifically in this case, I would use the ,0 in a match whenever I do not definately know if the data is in ascending order. Is there any concern that there might be several cells with the max value? "Bob Phillips" wrote: =OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,1) or =INDEX(K5:K28,MATCH(MAX(J5:J28),J5:J28,0)) -- HTH Bob Phillips "Voodoodan" wrote in message ... Hi, I've got a range of numbers, J5:J28. I can get the maximum value using: =max(j5:j28) However, I want to find that max number and find the offset value 1 column to the left. Is this possible within a formula? I've tried all sorts of combos with MAX and OFFSET but can't get this one. Any help would be appreciated. Thanks, Dan. -- Voodoodan ------------------------------------------------------------------------ Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#9
|
|||
|
|||
|
Jim,
It applies to the OFFSET column, not to the MATCH. Mangesh is looking left for the offset value. I (wrongly) looked right, so I used 1 (+1). -- HTH Bob Phillips "Jim May" wrote in message news:Yf_ke.18456$Fv.10788@lakeread01... Why the "-1" in the row argument? When I from the formula bar highlite the MATCH(MAX(J5:J28),J5:J28,0) only and F9 it return the correct position WITHOUT the need for the -1. (*&%$^%$??? TIA, Jim May "mangesh_yadav" wrote in message news:mangesh_yadav.1pl7cy_1117023660.9539@excelfor um-nospam.com... Use: =OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,-1) Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#10
|
|||
|
|||
|
the "-1" is needed for the offset function.
the reference cell is "0" for offset so to get to the third cell below the reference, this would result in a match of 4, but need a row of three input to the offset. in lookup fumctions (v and h and just lookup) the first cell is 1 so you do not need to compensate. (I get bit by this occasionally) "Jim May" wrote: Why the "-1" in the row argument? When I from the formula bar highlite the MATCH(MAX(J5:J28),J5:J28,0) only and F9 it return the correct position WITHOUT the need for the -1. (*&%$^%$??? TIA, Jim May "mangesh_yadav" wrote in message news:mangesh_yadav.1pl7cy_1117023660.9539@excelfor um-nospam.com... Use: =OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,-1) Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#11
|
|||
|
|||
|
oops you wanted to go to the right
=OFFSET(J5,MATCH(MAX(J5:J28,0),J5:J28,0)-1,-1) or =INDEX(I5:I28,MATCH(MAX(J5:J28,0),J5:J28,0)) "bj" wrote: I would recommend using =OFFSET(J5,MATCH(MAX(J5:J28,0),J5:J28,0)-1,1) or =INDEX(K5:K28,MATCH(MAX(J5:J28,0),J5:J28,0)) While it is not needed specifically in this case, I would use the ,0 in a match whenever I do not definately know if the data is in ascending order. Is there any concern that there might be several cells with the max value? "Bob Phillips" wrote: =OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,1) or =INDEX(K5:K28,MATCH(MAX(J5:J28),J5:J28,0)) -- HTH Bob Phillips "Voodoodan" wrote in message ... Hi, I've got a range of numbers, J5:J28. I can get the maximum value using: =max(j5:j28) However, I want to find that max number and find the offset value 1 column to the left. Is this possible within a formula? I've tried all sorts of combos with MAX and OFFSET but can't get this one. Any help would be appreciated. Thanks, Dan. -- Voodoodan ------------------------------------------------------------------------ Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#12
|
|||
|
|||
|
Opps I meant to say you wanted to go to the left
"bj" wrote: oops you wanted to go to the right =OFFSET(J5,MATCH(MAX(J5:J28,0),J5:J28,0)-1,-1) or =INDEX(I5:I28,MATCH(MAX(J5:J28,0),J5:J28,0)) "bj" wrote: I would recommend using =OFFSET(J5,MATCH(MAX(J5:J28,0),J5:J28,0)-1,1) or =INDEX(K5:K28,MATCH(MAX(J5:J28,0),J5:J28,0)) While it is not needed specifically in this case, I would use the ,0 in a match whenever I do not definately know if the data is in ascending order. Is there any concern that there might be several cells with the max value? "Bob Phillips" wrote: =OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,1) or =INDEX(K5:K28,MATCH(MAX(J5:J28),J5:J28,0)) -- HTH Bob Phillips "Voodoodan" wrote in message ... Hi, I've got a range of numbers, J5:J28. I can get the maximum value using: =max(j5:j28) However, I want to find that max number and find the offset value 1 column to the left. Is this possible within a formula? I've tried all sorts of combos with MAX and OFFSET but can't get this one. Any help would be appreciated. Thanks, Dan. -- Voodoodan ------------------------------------------------------------------------ Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#13
|
|||
|
|||
|
=OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,-1)
Bob: My Q does not have to do with the LAST "-1" << which is the Column argument (I understand that one; My Q has to do with the "-1" at the end of the Row argument, that is ... J5:J28,0)-1, <<< WHY - See more extensive results below (in my orig write-in).. Tks, Jim "Bob Phillips" wrote in message ... Jim, It applies to the OFFSET column, not to the MATCH. Mangesh is looking left for the offset value. I (wrongly) looked right, so I used 1 (+1). -- HTH Bob Phillips "Jim May" wrote in message news:Yf_ke.18456$Fv.10788@lakeread01... Why the "-1" in the row argument? When I from the formula bar highlite the MATCH(MAX(J5:J28),J5:J28,0) only and F9 it return the correct position WITHOUT the need for the -1. (*&%$^%$??? TIA, Jim May "mangesh_yadav" wrote in message news:mangesh_yadav.1pl7cy_1117023660.9539@excelfor um-nospam.com... Use: =OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,-1) Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#14
|
|||
|
|||
|
Jim,
That is because OFFSET is zero based, MATCH is 1 based, so you have to subtract 1 from the matched row to get the number of rows to OFFSET. -- HTH Bob Phillips "Jim May" wrote in message news:HN6le.18545$Fv.7033@lakeread01... =OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,-1) Bob: My Q does not have to do with the LAST "-1" << which is the Column argument (I understand that one; My Q has to do with the "-1" at the end of the Row argument, that is ... J5:J28,0)-1, <<< WHY - See more extensive results below (in my orig write-in).. Tks, Jim "Bob Phillips" wrote in message ... Jim, It applies to the OFFSET column, not to the MATCH. Mangesh is looking left for the offset value. I (wrongly) looked right, so I used 1 (+1). -- HTH Bob Phillips "Jim May" wrote in message news:Yf_ke.18456$Fv.10788@lakeread01... Why the "-1" in the row argument? When I from the formula bar highlite the MATCH(MAX(J5:J28),J5:J28,0) only and F9 it return the correct position WITHOUT the need for the -1. (*&%$^%$??? TIA, Jim May "mangesh_yadav" wrote in message news:mangesh_yadav.1pl7cy_1117023660.9539@excelfor um-nospam.com... Use: =OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,-1) Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#15
|
|||
|
|||
|
Bob,
I got it !!! Your explanation - well phrased!! a bit "thick" here at times, appreciate your patience. Jim "Bob Phillips" wrote in message ... Jim, That is because OFFSET is zero based, MATCH is 1 based, so you have to subtract 1 from the matched row to get the number of rows to OFFSET. -- HTH Bob Phillips "Jim May" wrote in message news:HN6le.18545$Fv.7033@lakeread01... =OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,-1) Bob: My Q does not have to do with the LAST "-1" << which is the Column argument (I understand that one; My Q has to do with the "-1" at the end of the Row argument, that is .... J5:J28,0)-1, <<< WHY - See more extensive results below (in my orig write-in).. Tks, Jim "Bob Phillips" wrote in message ... Jim, It applies to the OFFSET column, not to the MATCH. Mangesh is looking left for the offset value. I (wrongly) looked right, so I used 1 (+1). -- HTH Bob Phillips "Jim May" wrote in message news:Yf_ke.18456$Fv.10788@lakeread01... Why the "-1" in the row argument? When I from the formula bar highlite the MATCH(MAX(J5:J28),J5:J28,0) only and F9 it return the correct position WITHOUT the need for the -1. (*&%$^%$??? TIA, Jim May "mangesh_yadav" wrote in message news:mangesh_yadav.1pl7cy_1117023660.9539@excelfor um-nospam.com... Use: =OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,-1) Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#16
|
|||
|
|||
|
No problem Jim.
I know that when you ask a question, you have thought about it, so it helps me to get the right level of explanation. It's symbiotic :-) Regards Bob "Jim May" wrote in message news:HY7le.18565$Fv.13207@lakeread01... Bob, I got it !!! Your explanation - well phrased!! a bit "thick" here at times, appreciate your patience. Jim "Bob Phillips" wrote in message ... Jim, That is because OFFSET is zero based, MATCH is 1 based, so you have to subtract 1 from the matched row to get the number of rows to OFFSET. -- HTH Bob Phillips "Jim May" wrote in message news:HN6le.18545$Fv.7033@lakeread01... =OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,-1) Bob: My Q does not have to do with the LAST "-1" << which is the Column argument (I understand that one; My Q has to do with the "-1" at the end of the Row argument, that is ... J5:J28,0)-1, <<< WHY - See more extensive results below (in my orig write-in).. Tks, Jim "Bob Phillips" wrote in message ... Jim, It applies to the OFFSET column, not to the MATCH. Mangesh is looking left for the offset value. I (wrongly) looked right, so I used 1 (+1). -- HTH Bob Phillips "Jim May" wrote in message news:Yf_ke.18456$Fv.10788@lakeread01... Why the "-1" in the row argument? When I from the formula bar highlite the MATCH(MAX(J5:J28),J5:J28,0) only and F9 it return the correct position WITHOUT the need for the -1. (*&%$^%$??? TIA, Jim May "mangesh_yadav" wrote in message news:mangesh_yadav.1pl7cy_1117023660.9539@excelfor um-nospam.com... Use: =OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,-1) Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#17
|
|||
|
|||
|
This thread spawned one big discussion..! Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#18
|
|||
|
|||
|
Hi, I seemed to have stirred up a right hornets' nest! But I have one more question to add to the original post. Using the formula you gave me *"=OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,-1)"* is there any way to return duplicates, i.e., if two or more numbers equal the maximum figure? Thanks, Dan. -- Voodoodan ------------------------------------------------------------------------ Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#19
|
|||
|
|||
|
This is bit difficult, but you can try the following: In column K, starting row 5, enter the following formula and copy down. =IF(J5=MAX($J$5:$J$28),I5,"") In column L, enter the following formula and copy down: =IF(ISNUMBER(SMALL($K$5:$K$28,ROW()-ROW($K$5)+1)),SMALL($K$5:$K$28,ROW()-ROW($K$5)+1),"") Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
|
#20
|
|||
|
|||
|
That's excellent, thanks very much! Dan. -- Voodoodan ------------------------------------------------------------------------ Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597 View this thread: http://www.excelforum.com/showthread...hreadid=373856 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| need help using the offset function differently | Excel Worksheet Functions | |||
| Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
| dynamic offset | Excel Discussion (Misc queries) | |||
| Passing Cell Address to Offset | Excel Worksheet Functions | |||
| Offset? | Excel Discussion (Misc queries) | |||
Linear Mode
