Search Range Return which value is hit first

[williampdx]

Hi there.
I have written some complex formulas over the years, but this simple little formula eludes me. I need to have a formula that searches a range of numbers looking for two values, and basically tell me which value is reached first. This needs to be done in a formula and not a macro.

Take a look at the quick sample file I have attached. I have provided additional notes in a comment field. I would really appreciate any feedback you may have that help in properly returning the values I am looking for.

Thanks in advance!.

William

[protonLeah]

The formula below does not quite do what you wanted because of the decending order of your data. However the MATCHes return the rows of the data in C4 & D4 and the MIN picks the uppermost to concatenate for the address

Please Login or Register  to view this content.

[brokenbiscuits]

Hi

Not sure if this will suit your needs, but the only way I can see to do this would be to have a helper column (say in Col H) with the following formula dragged down:

=IF(G4<VALUE(RIGHT($C$4,7)),"x",IF(G4>VALUE(RIGHT($D$4,7)),"o",""))

Then use this column to bring back the result in A4:

=IF(MATCH("x",H:H,0)<MATCH("o",H:H,0),C4,D4)

[DonkeyOte]

It's not clear if the operators in C4:D4 are to be variable...

If we assume not (ie we can use operator constants in the formula) then with C4 & D4 holding the numeric constants only:

Please Login or Register  to view this content.

[williampdx]

I am going to clarify what I need a bit, and have reattached the .xlsx file with comment fields that are more clear.

The formula needs to tell me which value, either the value in C4 or the value in C5 is reached first in the range. The range starts with a value that is always in between values in C4 and D4. So as you go down the range and the values change, I just need to know which happens first, the value in C4 or the value in C5.

The values of C4 and D4 are variable.

Thanks for your help!

[DonkeyOte]

Can you outline what the result should be given the sample and why ?

As I see it G4 is < C4 ... C4 is now greater than D4 (the reverse of your original sample)

edit:

to be clear - using your first sample the formula I posted would return D4 given that test is met in row G116 whereas the C4 test is not met until G675
note the array entry requirement

[williampdx]

Thanks everyone for the help. The array above only seems to work for finding the value out of column C. Ultimately what this array is going to do, is search a range of prices in column G and then tell me whether the value in C ( a Stop Loss Level) or the value in D ( a take profit level) is hit first. So, the array really isn't looking for a match, but seeing when the price in column G hits or exceeds the Stop Loss on the low side or matches or exceeds the Take Profit level first. That is all it really needs to do. I really appreciate all of your help in this.

[DonkeyOte]

@williampdx.

As already mentioned - in your 2nd sample you reversed the ordering of the values - ie C > D rather than D > C (first sample)

The logic of your 2nd sample would appear to be AND based - ie G > C and G < D

The Array applied to your first example where C < D (ie G < C or G > D) - the logic I would expect - does exactly as you requested.

I would ask you revisit your 2nd sample - and confirm the logic given that set up.
Obviously we presume your references to C5 are meant to be D4

[DonkeyOte]

To be clear, to convert the first array in line with your 2nd sample you would reverse the > & < operators to reflect the fact that C is now > D

Please Login or Register  to view this content.

Using the 2nd sample file the above would still return C4 - this is simply because:

first value > C4 = G106

first value < D4 = G514


If you were to change C4 to say 1.31 then D4 would be the result given

first value > C4 = G1951

first value < D4 = G514


The confusion really stems from your first example implying an OR based test and your 2nd example implying an AND based test - see quote below (comment in A4)

...the range in column G would need to be equal to or greater than D, or less than or equal to C, and tell which one, C or D is reached first.

I have assumed with the above formula that in reality you meant to persist with an OR based test (>C or <D) but simply reversed the positions of large & small.

[williampdx]

Wow, I am so sorry for the confusion.
All I need is the formula to tell me which occurs first in the range:

G4:G3784 <= C4 or G4:G3784 >=D4

In addition, if neither of these occurs within the specified range, I need it to return a string. Ie "neither".

I changed the array we have to this,

=INDEX(C4:D4,MOD(MIN(MATCH(TRUE,G4:G4036<C4,0)+0.1,MATCH(TRUE,G4:G4036>D4,0)+0.2),1)*10)

and it works fine to return the value I am looking for, but if there is a value in D4 that is never reached, it returns an N/A#. I need the formula to tell me which value is hit first, but there will be times the range will only contain one or none of the values. If neither the SL or the TP columns are reached, I would just need it to return a string ("neither"). If the value of one column is reached and the other is never reached, it shouldn't matter since I only care about which one is reached first.

Make sense?

Thanks again for your help on this! This will be saving me an amazing amount of time in the long run.

[DonkeyOte]

Perhaps then:

Please Login or Register  to view this content.

use of IFERROR above presumes XL2007 or above

edit:
replaced "x" with a value that will meet MIN but generate error where neither found

[williampdx]

That did it! Thanks so much for your help DonkeyOte. And so I understand the formula, can you tell me what the significance of the +.3, and +.1 that I see in the formula?

[DonkeyOte]

The values generated within the MIN can be seen in terms of Row.Column - ie the decimal represents the column number of the item being searched for.

By pulling the MIN of the two returns we return the item found first given integer (Row) will take precedence over decimal.

We then take the decimal remainder of the MIN * 10 to generate the column number (integer)
The column number is in turn applied to the INDEX so as to determine the value being returned.

The significance of 0.3 is that we have no column 3 in our INDEX range and thus where the MIN value result is #.3 the INDEX will fail.
(# determined by COUNT of items in range - in hindsight safer to revert COUNT to ROWS)

The #.3 INDEX error is desired as it allows us to trap instance where both MATCHes have failed - at which point we replace the INDEX error with "neither" c/o outer IFERROR wrapper.

I opted to test MIN & MAX within the MIN to reduce workload of Array where unnecessary (rather than add further IFERRORs)

On a final note - please do not quote prior posts in their entirety - this clutters your thread and the board in general - quote only where necessary.

[williampdx]

Alright guys, I have finally got everything completed, but just ran into one more issue and I could really benefit from some of your expertise! I am attaching a new file for your reference - this file has much of the content deleted and just pertinent formulas left in tact. This issue is going to effect columns O, Q, and R for now. All I really need is some info to go about getting around this problem and I can build it out. Here is the issue see file)

As you can see in column O, beginning in O421, I am searching between 2 values to see which is hit first. This works perfectly. But as you can see this is going to be copied and pasted down the sheet many many times. In doing so, it ruins the formula. The reason is that in O422, I need the formula to reference 1570 rather than 139, what is what happens when you copy and paste down. 1570 is the row on the m1data sheet that corresponds to the same time but one day later. The second value $C$6266 in a perfect world would stay the same for each week, and then jump to the end of the week. Also, I am aware that the data in m1datatest! is only complete for the week. I left the rest out to cut down on file size! Does everyone see what I am getting at?

I am sure there are multiple workarounds here, so I would appreciate everyone putting on your thinking caps. Obviously manually changing these one by one defeats the entire purpose of what I am doing.

Any suggestions???

[williampdx]

Some more information for you as well. One of the first thoughts to come to mind would be to create a new sheet, copy the formulas down, and then run a macro to delete 1439 rows, offset 1 and then recopy them over. What this requires is that I have 1440 rows per day. This isn't always the case, and there is also less data available on Fridays. So, this may not be the most elegant solution.

If there were a way within the formula in O, to reference the day in B and then find that value in the 1mdata sheet, and return the value of the price to the formula. I understand how this would work, but not sure how you would write the formula in O.

Anyone?????