Thanks:  0

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

1. 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.

2. 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?

3. Re: Formula Needed for Identifying Higher Number AFTER Lower Number Identified

Originally Posted by matthewlouis
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. 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. 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. 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

7. 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?

8. 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?

9. 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

10. Re: Formula Needed for Identifying Higher Number AFTER Lower Number Identified

Originally Posted by matthewlouis
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?