Comparing 2 ranges of different size and dimensions

[Ride The Lightning]

Hi

I have 2 ranges. Range 1 has fixed values and contains the following information:

A B C D
100 0 1.86 1.12
100 0 1.86 1.12
100 0 1.86 1.12
100 0 1.86 1.12
100 0 1.86 1.12

Range 2 is in flux and contains:
E F
25 1.1
30 1.5
15 1.5
200 1.5
200 1.5
15 1.5

What I would am doing is checking if the data in cell E3 (25) in Range 2 fits the criteria in cells A2 (100) and B2 (0) in Range 1. So I have the formula =IF(E3<=$A$2,IF(E3>=$B$2,1,0),0). The following line is IF(E4<=$A$3,IF(E4>=$B$3,1,0),0). Etc. The problem is when I get to cell E7 (15) I want to loop back to start comparing E7 to cells A2 and B2. Of course I can use a variant of the previous formula, but I am using a large amount of data stretching across many columns and want avoid having to enter in alot of formulas manually.

If anyone could suggest an alternative I would be much obliged.

Regards

RTL

 

[AMAS]

Hi,

Woud this be a shorter formula and dragged down

=IF(AND(E3<=$A$2,E3>=$B$2),1,0)

and for the bottom row copied across?

=IF(AND(E7<=$A$1,E7>=$B$1),1,0)

If you have a lot of columns/ rows then you could code this in vba to input these formulas for you but we would need to better understand your sheet structure.

Hope this helps.

AMAS

 

[Ride The Lightning]

Yeah, I could do that, but it would still mean having to enter manually at certain points, just with the way the data is set out. It would take me a while to explain the sheet structure, but I will look into the VBA suggestion. I've read a decent bit of the Walkenbach book on VBA but haven't dabbled in it yet.

 

[Ride The Lightning]

Any other suggestions besides VBA?

 

[AlphaFrog]

I'm trying to understand your data structure but running into a conflict.

You say E3 is value 25 and E7 is value 15. Cant get that to fit.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1.86</td><td style="text-align: right;;">1.12</td><td style="text-align: right;background-color: #FFFF99;;">25</td><td style="text-align: right;;">1.1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1.86</td><td style="text-align: right;;">1.12</td><td style="text-align: right;;">30</td><td style="text-align: right;;">1.5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1.86</td><td style="text-align: right;;">1.12</td><td style="text-align: right;;">15</td><td style="text-align: right;;">1.5</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1.86</td><td style="text-align: right;;">1.12</td><td style="text-align: right;;">200</td><td style="text-align: right;;">1.5</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1.86</td><td style="text-align: right;;">1.12</td><td style="text-align: right;;">200</td><td style="text-align: right;;">1.5</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF99;;">15</td><td style="text-align: right;;">1.5</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p>

Is your reference to E3 suppose to be E2?

Are you trying to cycle through the five max\min criteria in columns A and B and repeat that criteria cycle for every 5 cells in column E?

 

[Ride The Lightning]

I'm trying to understand your data structure but running into a conflict.

You say E3 is value 25 and E7 is value 15. Cant get that to fit.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1.86</td><td style="text-align: right;;">1.12</td><td style="text-align: right;background-color: #FFFF99;;">25</td><td style="text-align: right;;">1.1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1.86</td><td style="text-align: right;;">1.12</td><td style="text-align: right;;">30</td><td style="text-align: right;;">1.5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1.86</td><td style="text-align: right;;">1.12</td><td style="text-align: right;;">15</td><td style="text-align: right;;">1.5</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1.86</td><td style="text-align: right;;">1.12</td><td style="text-align: right;;">200</td><td style="text-align: right;;">1.5</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1.86</td><td style="text-align: right;;">1.12</td><td style="text-align: right;;">200</td><td style="text-align: right;;">1.5</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF99;;">15</td><td style="text-align: right;;">1.5</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p>

Is your reference to E3 suppose to be E2?

Are you trying to cycle through the five max\min criteria in columns A and B and repeat that criteria cycle for every 5 cells in column E?

Hi

Yes, sorry, should be E2. I had headings under each column but omitted them when posting.

Yes, I am trying to cycle through the 5 max/min criteria in Columns A and B then repeating the cycle for every 5 cells in column E thereafter.

 

[AlphaFrog]

Try something like this...

=(E2<=INDEX($A$2:$A$6,ROW(6:6)-INT(ROW(5:5)/5)*5))*(E2>=INDEX($B$2:$B$6,ROW(6:6)-INT(ROW(5:5)/5)*5))

This will test if E2 is between A2 and B2 and return 1 for true and 0 for false. Drag this formula down and it will cycle through the five criteria in A2:B6 for every five cells in column E.

 

[Ride The Lightning]

Thanks alpha frog, that works fine.

 

[Ride The Lightning]

Hi

Sorry, should have noticed this immediately. The suggested formula:
=(K56<=INDEX($A$56:$A$60,ROW(6:6)-INT(ROW(5:5)/5)*5))*(K56>=INDEX($B$56:$B$60,ROW(6:6)-INT(ROW(5:5)/5)*5))
only works on the top row of the data being analysed. I was previously using
=IF(K56<=$A$56,IF(K56>=$B$56,1,0),0)

The formula should check if K56 has values between A56 and B56 then check K57 against A57 and B57 etc until it goes down to the 6th row of data before reversing to check K61 against A56 and B56 and so on, but the suggested formula does not work in this way. Is there a way to modify the suggested formula to incorporate this?

 

[Ride The Lightning]

.