Thanks Thanks:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: Formula Needed for Identifying Higher Number AFTER Lower Number Identified

  1. #1
    Board Regular
    Join Date
    Mar 2014
    Posts
    173
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Formula Needed for Identifying Higher Number AFTER Lower Number Identified

    I have a number in $A$2. I have another number in $B$2

    In Column C, I have 60 rows of numbers.

    In Column D, I have another 60 rows of numbers

    In Column E, I have a formula that identifies when a number in Column C is less than $A$2. Let's say C30 is the cell that contains that number that is lower than $A$2.

    Once cell C30 has been identified, I then need a formula that will look at Column D and identify the number that is HIGHER than $B$2. This identification must occur ONLY after Cell C30 has been identified, not before.

    I need the first occurrence only, none after that.

    Does anyone have an idea how to do a formula for that? I don't do VBA, I need a formula.

    Thanks in advance!!

  2. #2
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,769
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Needed for Identifying Higher Number AFTER Lower Number Identified

    Do you mean to say that you wish to identify the value of the first cell in ColumnD that is higher than B2 AFTER row 30?
    Last edited by DRSteele; Feb 22nd, 2018 at 06:19 PM.
    Windows10, Excel 2016 (365 Insider)
    My formulas are always in
    Green
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

  3. #3
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,311
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Needed for Identifying Higher Number AFTER Lower Number Identified

    Quote Originally Posted by matthewlouis View Post
    Once cell C30 has been identified, I then need a formula that will look at Column D and identify the number that is HIGHER than $B$2. This identification must occur ONLY after Cell C30 has been identified, not before.
    What is the connection between:

    1. The identification of cell C30, and

    2. The formula that will look at Column D and identify the number that is HIGHER than $B$2.

    Based on your description, these look to be independent?

  4. #4
    Board Regular
    Join Date
    Mar 2014
    Posts
    173
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Needed for Identifying Higher Number AFTER Lower Number Identified

    Yes, I just used row C30 as an example. That number could be in row C12, C44, etc.... So yes, I need the FIRST number from Column D that is higher than $B$2 ONLY AFTER the number from Column C has been identified, not before. It's conditional. Once the number that is below $A$2 has been identified, now look to find the number in Column D that is higher than $B$2. I just need the first occurrence for each one (the number that is lower than $A$2 looking in Column C -- and once that occurs -- the next number that is higher than $B$ looking in Column D.

    Thanks for responding! Hope this helps. what do you think?

  5. #5
    Board Regular
    Join Date
    Mar 2014
    Posts
    173
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Needed for Identifying Higher Number AFTER Lower Number Identified

    Thanks for responding StephenCrump. The numbers are not independent. The ID of C30 must occur first. Only then, do you move the Column D formula that identifies the number HIGHER than $B$2.

    In other words, ONLY when you have identified the FIRST number in Column C that is below $A$2 do you look in Column D to identify the first number that is HIGHER than $B$2.

    Thanks, I hope this response helps in addition to the posted response above.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,463
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Formula Needed for Identifying Higher Number AFTER Lower Number Identified

    Given this, what is the expected result?

    15 30
    20 5
    12 7
    30 9
    2
    1 32
    80
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    Board Regular
    Join Date
    Mar 2014
    Posts
    173
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Needed for Identifying Higher Number AFTER Lower Number Identified

    There needs to be a column (Column D) between where the 20 (Column C) and the 5 is . . . if you go down where the 2, that is the first cell that is below 15 so it would print a 2 in Column D. That satisfies the first condition . . . now it's time look for the 2nd condition and print that satisfied condition in Column 6 -- now that the 2 has printed, what is the next cell in the column that begins with 5 have a value greater than 30?

    That would be 32 since 32 is greater than 30 (at the top). A 32 would print in the column right next to the 32.

    So you have two column. Once column has a 2 as the only cell and the other column has a 32. There would be no other cells filled in either column.

    Hope this helps. Can you do a formula for these?
    Last edited by matthewlouis; Feb 23rd, 2018 at 01:25 PM.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,463
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Formula Needed for Identifying Higher Number AFTER Lower Number Identified

    Once more...

    Row\Col
    A
    B
    C
    D
    E
    1
    2
    15
    30
    3
    4
    5
    20
    5
    6
    12
    7
    7
    30
    9
    8
    2
    9
    1
    32
    10
    80


    What is the expected value given A2 = 15 and B2 = 30?
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    Board Regular
    Join Date
    Mar 2014
    Posts
    173
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Needed for Identifying Higher Number AFTER Lower Number Identified

    In e8, 2 would print since 2 is < A2

    In f9, 32 would print since 2 printed in E8 AND 32 > b2.

    There are 2 expected values, not just one. Two formulas for Columns e and f
    Last edited by matthewlouis; Feb 23rd, 2018 at 02:07 PM.

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,463
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Formula Needed for Identifying Higher Number AFTER Lower Number Identified

    Quote Originally Posted by matthewlouis View Post
    In e8, 2 would print since 2 is < A2

    In f9, 32 would print since 2 printed in E8 AND 32 > b2.

    There are 2 expected values, not just one. Two formulas for Columns e and f
    12 in column A is < A2. Why do you skip that?
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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