+ Reply to Thread
Results 1 to 4 of 4

Return value if two or three matches meet the criteria

  1. #1
    Registered User
    Join Date
    09-19-2016
    Location
    Muscat, Oman
    MS-Off Ver
    365
    Posts
    56

    Return value if two or three matches meet the criteria

    Objective:
    • I have rows of numbers on the second sheet.
    • The array to search on the second sheet are the rows with date/times (A3:A?) between the date/time in B3 and C3 on first sheet
    • I want the formula to start looking in the first row that is >= B3 in first sheet, for numbers that are greater than 0.001 than the number in I3 on the first sheet. (Ex: number > (I3+0.001)
    • I want to enter a variable into the cell S1 on the first sheet of how many matches must be found (Example: 1, 2, 3, 4)
    • For those numbers that are greater than I3+0.001, it needs to find the number of occurrences set by the variable in cell S1 that are no more than +or- 0.0001 variance away from each other
    • Then when it finds the number of occurrences set by the S1 variable, it checks that number (this cell value to check against can be any one of the occurrences because they have low variance. Example is it could be the first occurrence) against that row's number found in the column L.
    • (this example assumes the S3 variable is 3) If on the first sheet, cell E3 says "Buy", then it checks when "L3>the number found that has 3 occurrences". If E3 says "Sell", then it checks if "L3 < the number found that has 3 occurrences".
    • If the above is true (L3 is higher or lower), then the formula returns the date/time (A column) from the second sheet.
    • If it is not true, it goes to the next row and checks that rows numbers.
    • It continues until it gets the last date in sheet one found in C3 (<=)

    I tried to start this using Index/Match, but then realized I was out of my depth. I attached an example workbook.

    The narrative, in case it helps to understand the context:
    I have past trades on the Trade Analysis sheet (first sheet). On the EURUSD sheet (second sheet), I have historical data for the EURUSD and then computed 'obstacles' (starting column N). I am wanting to see if I exited the trade when there are multiple obstacles at one price level.

    I know this might be complicated, but I really appreciate the help, because I am not able to figure it out. Thank you in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Return value if two or three matches meet the criteria

    I'd suggest providing a few expected results with your sample file; I confess I took a look earlier today and whilst I could follow some of what you were doing it wasn't altogether clear where the 'occurrences' data resided
    i.e. I assumed you meant relative to consecutive close prices (rows) from first valid row (relative to start) but, without some expected results, it's hard to be sure.

    note: above not a criticism, I can see you put a lot of effort into your post -- there's just a lot to it...

  3. #3
    Registered User
    Join Date
    09-19-2016
    Location
    Muscat, Oman
    MS-Off Ver
    365
    Posts
    56

    Re: Return value if two or three matches meet the criteria

    I appreciate you asking. You are correct, an example is good as I agree this is a lot, which is why its beyond my formula's abilities and need help.

    I have highlighted in yellow the cell references in the attached workbook.

    Trade Analysis Sheet:
    Okay, on the Trade Analysis sheet, we will use the first trade as the example. The trade opened on 6/1 at 3:48 and closed at 6/1 7:40. The opening price is 1.11166.

    We have the variable set in S1 to 2 'occurrences'. and the formula doing the below calculations will be located in S3, in this example.

    EURUSD Sheet:
    Our formula's range will be between the open and close times on the EURUSD sheet (highlighted yellow).

    We will begin looking for numbers that are at least 0.001 more than the open price above. Thus, 1.11166+0.001 = 1.11266. We will begin looking at numbers that are greater than or equal to 1.11266.

    Now we want to start on row 32 (the trade's open time and highlighted yellow). We will begin the search in columns N32:DS32.

    We are looking for 2 'occurrences' (variable set by S1 on the Trade Analysis worksheet) that are greater than 1.11266. An occurrence means there are at least two numbers in the row that meet our criteria (in this case, the two criteria are no more than 0.0001 a part and greater than 1.11266).

    For example: in the EURUSD worksheet, let us assume we are looking for no more variance than 0.0002 as the second criteria. Our formula would have found it at column X and V.
    These two 'occurrences' meet our criteria: they are greater than 1.11266, and they are less than or equal to 0.0002 a part from each other.
    So in this example, the formula would return the date/time of the row (A32) and the number from the first occurrence (V32).

    If it does not find at least 2 occurrences that meet our criteria in that row, then it would go to the next row (row 33). It would continue until it found a match to our criteria, or the formula would finish after it checked the last row (row 264) on this worksheet.

    Now the result of the formula. I stated above that I need two results: date/time and the number/value in the cell of the occurrence (doesn't have to be the first occurrence since they have such low variance, but just one of them). I understand if this needs to be two formulas. One to result the date/time and one to result the number. But I do need both pieces of data.

    Let me know if I have not explained anything properly, and I can go into more detail or more examples to continue to clear anything else up I left unclear.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-19-2016
    Location
    Muscat, Oman
    MS-Off Ver
    365
    Posts
    56

    Re: Return value if two or three matches meet the criteria

    I think the only way to do this would be through VBA. I will check with the VBA category on the forum.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. return values that meet two criteria
    By RubenExcel in forum Excel General
    Replies: 2
    Last Post: 06-18-2019, 11:11 PM
  2. Lookup return values if meet criteria?
    By lissamel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-31-2015, 10:21 AM
  3. [SOLVED] Lookup Values which meet two or more criteria and return multiple matches horizontally
    By josetmg in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-03-2015, 01:16 AM
  4. [SOLVED] How to return all corresponding values that meet 2 criteria in excel
    By sgw_73 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-18-2014, 10:49 AM
  5. Replies: 6
    Last Post: 05-18-2011, 02:03 PM
  6. Meet two criteria, return 4 possible strings
    By xaos1000 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-24-2011, 05:03 PM
  7. Return records that meet certain criteria
    By profector in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2008, 06:23 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1