Find which value comes first

[silviosilver]

I have two sets of data. The first set contains information about the highest and lowest values in the second set of data. The first set is weekly data; the second set daily data. I want to use the daily data to find out which value occurred first, the highest or the lowest.

To illustrate:

Weekly data

Highest Lowest
53 47


Daily data

Highest Lowest
51 50
49 48
52 47
50 48
53 50

In this example, the weekly data tells us that in the next of five sets of values from the daily data, the highest value will be 53 and the lowest value 47. In the daily data, 47 occurred on day 3, while 53 occurred on day 5.

I would like either "L" for lowest or "H" for highest, or the value itself (47, in the example), to be returned in a column next to the weekly data values.

Is there any way this could be done?

If it is possible, I would like to be able to copy and paste this formula all the way down the weekly rows, since there are some 1000 such rows.

TIA

 

[Dim Me as xlNoob]

Hi there,

With the data set out as so:

[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"]A[/TD]
[TD="width: 64, align: center"]B[/TD]
[TD="width: 64, align: center"]C[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]1[/TD]
[TD="width: 64, align: center"]Weekly[/TD]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Daily[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]51[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]49[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]52[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]50[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The formula in C2 is:

<iferror(match(b2,a5:a9,0),match(b2,b5:b9,0)),"h","l")
<iferror(match(b2,a5:a9,0),match(b2,b5:b9,0)),"h","l")
<iferror(match(b2,a5:a9,0),match(b2,b5:b9,0)),"h","l")
<iferror(match(b2,a5:a9,0),match(b2,b5:b9,0)),"h","l")
<iferror(match(b2,a5:a9,0),match(b2,b5:b9,0)),"h","l")"
<iferror(match(b2,a5:a9,0),match(b2,b5:b9,0)),"h","l")"
=IF(IFERROR(MATCH(A2,A5:A9,0),MATCH(A2,B5:B9)) < IFERROR(MATCH(B2,A5:A9,0),MATCH(B2,B5:B9,0)),"H","L")

You should be able to copy and paste this next to every weekly set of data as above and as long as all your data is set out in the same manner and spacing etc it should work.
</iferror(match(b2,a5:a9,0),match(b2,b5:b9,0)),"h","l")"
</iferror(match(b2,a5:a9,0),match(b2,b5:b9,0)),"h","l")"
</iferror(match(b2,a5:a9,0),match(b2,b5:b9,0)),"h","l")
</iferror(match(b2,a5:a9,0),match(b2,b5:b9,0)),"h","l")
</iferror(match(b2,a5:a9,0),match(b2,b5:b9,0)),"h","l")
</iferror(match(b2,a5:a9,0),match(b2,b5:b9,0)),"h","l")

 

[JoeMo]

Sheet2

	A	B	C
Weekly data
Highest	Lowest
Daily data
Highest	Lowest

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:117px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]47[/TD]
[TD="align: center"]L[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]48[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]47[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]48[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]50[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A3 =MAX($A8:$A12) B3 =MIN($B8:$B12) C3 =IF(MATCH(A3,$A8:$A12,0) < <match<span style=" color:008000; ">(B3,$B8:$B12,0),"H",IF(MATCH(A3,$A8:$A12,0)>MATCH(B3,$B8:$B12,0),"L","Both on same day"))</match<span> <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Dim Me as xlNoob]

I didn't even think about same day occurance, whoops. And I also realise now my formula has a bit of redundancy in it.

Thanks Joe.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=IF(MIN(IF(A8:B12=A2,ROW(A8:B12)-ROW(A8)+1))=MIN(IF(A8:B12=B2,ROW(A8:B12)-ROW(A8)+1)),"both on the same day",IF(MIN(IF(A8:B12=A2,ROW(A8:B12)-ROW(A8)+1))>MIN(IF(A8:B12=B2,ROW(A8:B12)-ROW(A8)+1)),"L","H"))

 

[silviosilver]

Thank you Dim, Joe, and Aladin. I am yet to try any of these, however I wanted to clarify that my data is arranged with all the weekly values in two adjacent columns, and the daily data in another set of columns. So the data for one year take up some 50 rows for the weekly values, but some 250 rows for the daily values.

Weekly Daily

H L H L



That is why I asked whether the formula could simply copied down the rows. If I copied down the rows the way it is set up, I think I will just get a "rolling" five day calculation - days 1-5, 2-6, 3-7 etc - but that is not what I want. I only want to identify the H/L for the specific 5-day periods: 1-5, 6-10, 11-15 etc. I apologize for not making this clear in my first post.

 

[silviosilver]

I solved my problem regarding copying down with the OFFSET function, perhaps inelegantly but it works. Thanks again to everyone.

 

[Aladin Akyurek]

I solved my problem regarding copying down with the OFFSET function, perhaps inelegantly but it works. Thanks again to everyone.

Great, but what did you implement?

 

[silviosilver]

I used the MATCH function to find whether the min/max occurred first.

If you're asking how I copied down, I used the OFFSET function, incrementing the number of rows offset by five each time. (I ran into problems with this because of varying week-lengths, which I asked about on another thread I recently made.)

I'm not sure if the formula you proposed would have helped me avoid this, but I was a bit intimidated by it, because I wasn't sure how "ROW" worked and reading up on when you'd want to Ctrl+Shft+Entr confused me.