Formula Needed for Identifying Higher Number AFTER Lower Number Identified

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
374
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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!!
 
OK, messed up again here.

The E calc result = 257.63 comes from D AND the F calc also comes from D . . . they BOTH come from D. The first one is LOWER than $A$2 and the 2nd one is HIGHER than $B$2

I am so sorry I am all over the place but my table columns don't match your example and that's why I've been so off.

Row\Col
A​
B​
C​
D​
E​
F​
1​
SPL SPL High High Close Closed Under SPL Closed Over High
2​
262.71
263.73
3​
264.43
264.07
4​
265.52
265.51
5​
266.38
266.31
6​
267.32
266.78
7​
267.56
266.75
8​
267.22
265.66
9​
267.04
266.51
10​
268.6
268.2
11​
268.53
267.17
12​
268.33
267.03
13​
268.39
267.58
14​
267.64
267.51
15​
267.64
267.51
16​
267.44
267.19
17​
267.73
267.32
18​
267.92
267.87
19​
268.55
266.86
20​
270.64
270.47
21​
272.16
271.61
22​
273.56
273.42
23​
274.1
273.92
24​
275.25
274.54
25​
274.42
274.12
26​
276.12
276.12
27​
278.11
277.92
28​
280.05
279.61
29​
279.96
279.14
30​
280.41
280.41
31​
282.69
282.69
32​
283.62
283.29
33​
284.7
283.18
34​
284.27
283.3
35​
286.63
286.58
36​
286.43
284.68
37​
284.74
281.76
38​
283.3
281.9
39​
283.06
281.58
40​
280.23
275.45
41​
275.85
263.93
42​
269.7
269.13
43​
272.36
267.67
44​
268.17
257.63
257.63
45​
263.61
261.5
265.34
46​
267.01
265.34
47​
266.62
266
48​
270
269.59
49​
273.04
273.03
50​
275.32
273.11
51​
274.72
270.05
52​
273.05
270.4

Define BigNum in the Name Manager as referring to:

=9.99999999999999E+307

In E3 enter and copy down:

=IF(1-ISNUMBER(LOOKUP(9.99999999999999E+307,$E$1:E2)),IF($D3<=$A$2,$D3,""),"")

In F3 control+shift+enter, not just enter, and copy down:

=IF(1-ISNUMBER(LOOKUP(BigNum,$F$1:F2)),IF(ROW()-ROW($F$3)+1>MATCH(BigNum,$E$3:$E$52),MIN(IF(ROW($F$3:$F$52)-ROW($F$3)+1>MATCH(BigNum,$E$3:$E$52),IF($D$3:$D$52>$B$2,$D$3:$D$52))),""),"")
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Whohoooo! Thanks so much Aladin, this works perfect!

Wow, thanks for solving this for me, you're amazing. Thanks for your patience with me, too. :)
 
Upvote 0
May I ask you to indulge me for one more calculation that is the opposite of the one you did? I tried reversing some of the formula you did -- but I can;t get it to work. Here is a new table where things are reversed. I typed in what should be the results. This is the formula you used to solve for the Swing Point LOW:

In F3 control+shift+enter, not just enter, and copy down:

=IF(1-ISNUMBER(LOOKUP(BigNum,$F$1:F2)),IF(ROW()-ROW($F$3)+1>MATCH(BigNum,$E$3:$E$52),MIN(IF(ROW($F$3:$F$52)-ROW($F$3)+1>MATCH(BigNum,$E$3:$E$52),IF($D$3:$D$52>$B$2,$D$3:$D$52))),""),"")

This table below is about the Swing Point Highs -- just the opposite. How would the formula change? The E calc result = 2713.06 comes from Column D AND the Column F calc also comes from D is 2648.94.

Column E calculation is the number from Column D must be HIGHER than $A$2.

Column F calculation is the number from Column D must LOWER than $B$. Here's the table.


Row\ColABCDEF
1SPH HighSPH LowLowCloseClosed Over SPHClosed Under SPH Low
22,694.972,685.92
32,680.742,681.47
42,676.112,679.25
52,682.402,684.57
62,678.132,683.34
72,678.132,683.34
82,677.962,680.50
92,678.912,682.62
102,682.692,687.54
112,673.612,673.61
122,697.772,713.062713.06
132,719.072,723.99
142,727.922,743.15
152,737.602,747.71
162,747.862,751.29
172,736.062,748.23
182,752.782,767.56
192,769.642,786.24
202,778.382,802.56
212,792.562,798.03
222,798.082,810.30
232,808.122,832.97
242,830.592,839.13
252,824.812,837.54
262,830.942,839.25
272,846.182,872.87
282,851.482,853.53
292,818.272,822.43
302,813.042,823.81
312,812.702,821.98
322,759.972,762.13
332,638.172,648.942648.94
342,593.072,695.14
352,681.332,681.66
362,580.562,581.00
372,532.692,619.55
382,622.452,656.00
392,637.082,662.94
402,648.872,698.63
412,689.822,731.20
422,725.112,732.22
432,701.292,701.33
442,697.772,703.96
452,713.742,747.30

<tbody>
</tbody>
 
Last edited:
Upvote 0
May I ask you to indulge me for one more calculation that is the opposite of the one you did? I tried reversing some of the formula you did -- but I can;t get it to work. Here is a new table where things are reversed. I typed in what should be the results. This is the formula you used to solve for the Swing Point LOW:

In F3 control+shift+enter, not just enter, and copy down:

=IF(1-ISNUMBER(LOOKUP(BigNum,$F$1:F2)),IF(ROW()-ROW($F$3)+1>MATCH(BigNum,$E$3:$E$52),MIN(IF(ROW($F$3:$F$52)-ROW($F$3)+1>MATCH(BigNum,$E$3:$E$52),IF($D$3:$D$52>$B$2,$D$3:$D$52))),""),"")

This table below is about the Swing Point Highs -- just the opposite. How would the formula change? The E calc result = 2713.06 comes from Column D AND the Column F calc also comes from D is 2648.94.

Column E calculation is the number from Column D must be HIGHER than $A$2.

Column F calculation is the number from Column D must LOWER than $B$. Here's the table.

[...]

In E3 enter and copy down:

=IF(1-ISNUMBER(LOOKUP(BigNum,$E$1:E2)),IF($D3>=$A$2,$D3,""),"")

In F3 enter and copy down:

=IF(1-ISNUMBER(LOOKUP(BigNum,$F$1:F2)),IF(ROW()-ROW($F$3)+1>MATCH(BigNum,$E$3:$E$45),IF($D3<=$B$2,$D3,""),""),"")

Is this what we are after?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top