Home |
Search |
Today's Posts |
#1
|
|||
|
|||
MAX formula with Associated DATE
Hi there
I have organised data into 2 Columns named DATE (A1:A10) and DATA (B1:B10) What I am looking at doing is creating a formula where it sources the highest and Lowest number in the DATA col. with the corresponding DATE as well. The first part I have worked out = MAX(B1:B10) and LOW(B1:B10) however I am unsure of how to get it to lookup the corresponding date. Thanks for any help. Patrick |
#2
|
|||
|
|||
=OFFSET(B1,MATCH(MAX(B1:B10),B1:B10,0)-1,-1) and =OFFSET(B1,MATCH(MIN(B1:B10),B1:B10,0)-1,-1) Mangesh PS: if I double-posted, sorry. -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=376706 |
#3
|
|||
|
|||
=OFFSET(B1,MATCH(MAX(B1:B10),B1:B10,0)-1,-1)
and =OFFSET(B1,MATCH(MIN(B1:B10),B1:B10,0)-1,-1) Mangesh "Patrick White" wrote in message ... Hi there I have organised data into 2 Columns named DATE (A1:A10) and DATA (B1:B10) What I am looking at doing is creating a formula where it sources the highest and Lowest number in the DATA col. with the corresponding DATE as well. The first part I have worked out = MAX(B1:B10) and LOW(B1:B10) however I am unsure of how to get it to lookup the corresponding date. Thanks for any help. Patrick |
#4
|
|||
|
|||
Patrick White wrote:
Hi there I have organised data into 2 Columns named DATE (A1:A10) and DATA (B1:B10) What I am looking at doing is creating a formula where it sources the highest and Lowest number in the DATA col. with the corresponding DATE as well. The first part I have worked out = MAX(B1:B10) and LOW(B1:B10) however I am unsure of how to get it to lookup the corresponding date. Thanks for any help. Patrick Try either building a pivot table where you can make it show Top N (N=1) or see: http://www.excelforum.com/showthread.php?t=333697 for a formula system where you need to set the Top 3 to Top 1 in order to create a list of dates that are associated with the Max value instances. For a list of date instances corresponding to the Min value instances, the same setup with the rank formula modified to: =RANK(B3,$B$3:$B$9,1)+COUNTIF($B$3:B3,B3)-1 |
#5
|
|||
|
|||
Mangesh
Thanks for this. Works great. "mangesh_yadav" wrote in message news:mangesh_yadav.1q76am_1118048786.9146@excelfor um-nospam.com... =OFFSET(B1,MATCH(MAX(B1:B10),B1:B10,0)-1,-1) and =OFFSET(B1,MATCH(MIN(B1:B10),B1:B10,0)-1,-1) Mangesh PS: if I double-posted, sorry. -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=376706 |
#6
|
|||
|
|||
Thanks for the feedback.
Mangesh "Patrick White" wrote in message ... Mangesh Thanks for this. Works great. "mangesh_yadav" wrote in message news:mangesh_yadav.1q76am_1118048786.9146@excelfor um-nospam.com... =OFFSET(B1,MATCH(MAX(B1:B10),B1:B10,0)-1,-1) and =OFFSET(B1,MATCH(MIN(B1:B10),B1:B10,0)-1,-1) Mangesh PS: if I double-posted, sorry. -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=376706 |
#7
|
|||
|
|||
Are you sure? What happens if...
A1: 1-Jan-05 A2: 3-Jan-05 A3: 7-Feb-05 A4: 3-Mar-05 A5: 5-May-05 B1: 7 B2: 3 B3: 7 B4: 4 B5: 3 Patrick White wrote: Mangesh Thanks for this. Works great. "mangesh_yadav" wrote in message news:mangesh_yadav.1q76am_1118048786.9146@excelfor um-nospam.com... =OFFSET(B1,MATCH(MAX(B1:B10),B1:B10,0)-1,-1) and =OFFSET(B1,MATCH(MIN(B1:B10),B1:B10,0)-1,-1) Mangesh PS: if I double-posted, sorry. -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=376706 |
#8
|
|||
|
|||
Hi Aladin, The functions will obviously choose the first occurence of the max and min. One can't help with it. 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=376706 |
#9
|
|||
|
|||
mangesh_yadav wrote:
Hi Aladin, The functions will obviously choose the first occurence of the max and min. One can't help with it. Mangesh At least two approaches, as I proposed, can: Pivot Tables and the formula system I quoted. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
addition to my date formula...required | Excel Worksheet Functions | |||
Excel formula with date constraints | Excel Discussion (Misc queries) | |||
Formula with date constraints | Excel Worksheet Functions | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions |