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?
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!!
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.
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?
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.
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.
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.
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.
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.
Like this thread? Share it with others