Formula Needed for Identifying Higher Number AFTER Lower Number Identified

[matthewlouis]

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!!

 

[Aladin Akyurek]

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.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr][tr][td]

1​

[/td][td] SPL[/td][td] SPL High[/td][td] High[/td][td] Close[/td][td] Closed Under SPL[/td][td] Closed Over High[/td][/tr]
[tr][td]

2​

[/td][td]

262.71​

[/td][td]

263.73​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

3​

[/td][td] [/td][td] [/td][td]

264.43​

[/td][td]

264.07​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

4​

[/td][td] [/td][td] [/td][td]

265.52​

[/td][td]

265.51​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

5​

[/td][td] [/td][td] [/td][td]

266.38​

[/td][td]

266.31​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

6​

[/td][td] [/td][td] [/td][td]

267.32​

[/td][td]

266.78​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

7​

[/td][td] [/td][td] [/td][td]

267.56​

[/td][td]

266.75​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

8​

[/td][td] [/td][td] [/td][td]

267.22​

[/td][td]

265.66​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

9​

[/td][td] [/td][td] [/td][td]

267.04​

[/td][td]

266.51​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

10​

[/td][td] [/td][td] [/td][td]

268.6​

[/td][td]

268.2​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

11​

[/td][td] [/td][td] [/td][td]

268.53​

[/td][td]

267.17​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

12​

[/td][td] [/td][td] [/td][td]

268.33​

[/td][td]

267.03​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

13​

[/td][td] [/td][td] [/td][td]

268.39​

[/td][td]

267.58​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

14​

[/td][td] [/td][td] [/td][td]

267.64​

[/td][td]

267.51​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

15​

[/td][td] [/td][td] [/td][td]

267.64​

[/td][td]

267.51​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

16​

[/td][td] [/td][td] [/td][td]

267.44​

[/td][td]

267.19​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

17​

[/td][td] [/td][td] [/td][td]

267.73​

[/td][td]

267.32​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

18​

[/td][td] [/td][td] [/td][td]

267.92​

[/td][td]

267.87​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

19​

[/td][td] [/td][td] [/td][td]

268.55​

[/td][td]

266.86​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

20​

[/td][td] [/td][td] [/td][td]

270.64​

[/td][td]

270.47​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

21​

[/td][td] [/td][td] [/td][td]

272.16​

[/td][td]

271.61​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

22​

[/td][td] [/td][td] [/td][td]

273.56​

[/td][td]

273.42​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

23​

[/td][td] [/td][td] [/td][td]

274.1​

[/td][td]

273.92​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

24​

[/td][td] [/td][td] [/td][td]

275.25​

[/td][td]

274.54​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

25​

[/td][td] [/td][td] [/td][td]

274.42​

[/td][td]

274.12​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

26​

[/td][td] [/td][td] [/td][td]

276.12​

[/td][td]

276.12​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

27​

[/td][td] [/td][td] [/td][td]

278.11​

[/td][td]

277.92​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

28​

[/td][td] [/td][td] [/td][td]

280.05​

[/td][td]

279.61​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

29​

[/td][td] [/td][td] [/td][td]

279.96​

[/td][td]

279.14​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

30​

[/td][td] [/td][td] [/td][td]

280.41​

[/td][td]

280.41​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

31​

[/td][td] [/td][td] [/td][td]

282.69​

[/td][td]

282.69​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

32​

[/td][td] [/td][td] [/td][td]

283.62​

[/td][td]

283.29​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

33​

[/td][td] [/td][td] [/td][td]

284.7​

[/td][td]

283.18​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

34​

[/td][td] [/td][td] [/td][td]

284.27​

[/td][td]

283.3​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

35​

[/td][td] [/td][td] [/td][td]

286.63​

[/td][td]

286.58​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

36​

[/td][td] [/td][td] [/td][td]

286.43​

[/td][td]

284.68​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

37​

[/td][td] [/td][td] [/td][td]

284.74​

[/td][td]

281.76​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

38​

[/td][td] [/td][td] [/td][td]

283.3​

[/td][td]

281.9​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

39​

[/td][td] [/td][td] [/td][td]

283.06​

[/td][td]

281.58​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

40​

[/td][td] [/td][td] [/td][td]

280.23​

[/td][td]

275.45​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

41​

[/td][td] [/td][td] [/td][td]

275.85​

[/td][td]

263.93​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

42​

[/td][td] [/td][td] [/td][td]

269.7​

[/td][td]

269.13​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

43​

[/td][td] [/td][td] [/td][td]

272.36​

[/td][td]

267.67​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

44​

[/td][td] [/td][td] [/td][td]

268.17​

[/td][td]

257.63​

[/td][td] 257.63[/td][td]

​

[/td][/tr]
[tr][td]

45​

[/td][td] [/td][td] [/td][td]

263.61​

[/td][td]

261.5​

[/td][td] [/td][td]

265.34​

[/td][/tr]
[tr][td]

46​

[/td][td] [/td][td] [/td][td]

267.01​

[/td][td]

265.34​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

47​

[/td][td] [/td][td] [/td][td]

266.62​

[/td][td]

266​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

48​

[/td][td] [/td][td] [/td][td]

270​

[/td][td]

269.59​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

49​

[/td][td] [/td][td] [/td][td]

273.04​

[/td][td]

273.03​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

50​

[/td][td] [/td][td] [/td][td]

275.32​

[/td][td]

273.11​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

51​

[/td][td] [/td][td] [/td][td]

274.72​

[/td][td]

270.05​

[/td][td] [/td][td]

​

[/td][/tr]
[tr][td]

52​

[/td][td] [/td][td] [/td][td]

273.05​

[/td][td]

270.4​

[/td][td] [/td][td]

​

[/td][/tr]
[/table]

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))),""),"")

 

[matthewlouis]

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.

 

[Aladin Akyurek]

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.

You are welcome.

 

[matthewlouis]

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.

<tbody>
[TD="class: xl65"]Row\Col[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66, width: 80"]E[/TD]
[TD="class: xl66, width: 80"]F[/TD]

[TD="class: xl66, width: 80"]1[/TD]
[TD="class: xl69, width: 80"]SPH High[/TD]
[TD="class: xl69, width: 80"]SPH Low[/TD]
[TD="class: xl69, width: 80"]Low[/TD]
[TD="class: xl70, width: 80"]Close[/TD]
[TD="class: xl74, width: 80"]Closed Over SPH[/TD]
[TD="class: xl71, width: 80"]Closed Under SPH Low[/TD]

[TD="class: xl66, width: 80"]2[/TD]
[TD="class: xl72, width: 80"]2,694.97[/TD]
[TD="class: xl72, width: 80"]2,685.92[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]3[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,680.74[/TD]
[TD="class: xl72, width: 80"]2,681.47[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]4[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,676.11[/TD]
[TD="class: xl72, width: 80"]2,679.25[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]5[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,682.40[/TD]
[TD="class: xl72, width: 80"]2,684.57[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]6[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,678.13[/TD]
[TD="class: xl72, width: 80"]2,683.34[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]7[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,678.13[/TD]
[TD="class: xl72, width: 80"]2,683.34[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]8[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,677.96[/TD]
[TD="class: xl72, width: 80"]2,680.50[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]9[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,678.91[/TD]
[TD="class: xl72, width: 80"]2,682.62[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]10[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,682.69[/TD]
[TD="class: xl72, width: 80"]2,687.54[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]11[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,673.61[/TD]
[TD="class: xl72, width: 80"]2,673.61[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]12[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,697.77[/TD]
[TD="class: xl72, width: 80"]2,713.06[/TD]
[TD="class: xl65, width: 80, align: right"]2713.06[/TD]
[TD="class: xl73, width: 80"][/TD]

[TD="class: xl66, width: 80"]13[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,719.07[/TD]
[TD="class: xl72, width: 80"]2,723.99[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]14[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,727.92[/TD]
[TD="class: xl72, width: 80"]2,743.15[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]15[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,737.60[/TD]
[TD="class: xl72, width: 80"]2,747.71[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]16[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,747.86[/TD]
[TD="class: xl72, width: 80"]2,751.29[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]17[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,736.06[/TD]
[TD="class: xl72, width: 80"]2,748.23[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]18[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,752.78[/TD]
[TD="class: xl72, width: 80"]2,767.56[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]19[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,769.64[/TD]
[TD="class: xl72, width: 80"]2,786.24[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]20[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,778.38[/TD]
[TD="class: xl72, width: 80"]2,802.56[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]21[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,792.56[/TD]
[TD="class: xl72, width: 80"]2,798.03[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]22[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,798.08[/TD]
[TD="class: xl72, width: 80"]2,810.30[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]23[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,808.12[/TD]
[TD="class: xl72, width: 80"]2,832.97[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]24[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,830.59[/TD]
[TD="class: xl72, width: 80"]2,839.13[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]25[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,824.81[/TD]
[TD="class: xl72, width: 80"]2,837.54[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]26[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,830.94[/TD]
[TD="class: xl72, width: 80"]2,839.25[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]27[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,846.18[/TD]
[TD="class: xl72, width: 80"]2,872.87[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]28[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,851.48[/TD]
[TD="class: xl72, width: 80"]2,853.53[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]29[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,818.27[/TD]
[TD="class: xl72, width: 80"]2,822.43[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]30[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,813.04[/TD]
[TD="class: xl72, width: 80"]2,823.81[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]31[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,812.70[/TD]
[TD="class: xl72, width: 80"]2,821.98[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]32[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,759.97[/TD]
[TD="class: xl72, width: 80"]2,762.13[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]33[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,638.17[/TD]
[TD="class: xl72, width: 80"]2,648.94[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80, align: right"]2648.94[/TD]

[TD="class: xl66, width: 80"]34[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,593.07[/TD]
[TD="class: xl72, width: 80"]2,695.14[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]35[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,681.33[/TD]
[TD="class: xl72, width: 80"]2,681.66[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]36[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,580.56[/TD]
[TD="class: xl72, width: 80"]2,581.00[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]37[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,532.69[/TD]
[TD="class: xl72, width: 80"]2,619.55[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]38[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,622.45[/TD]
[TD="class: xl72, width: 80"]2,656.00[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]39[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,637.08[/TD]
[TD="class: xl72, width: 80"]2,662.94[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]40[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,648.87[/TD]
[TD="class: xl72, width: 80"]2,698.63[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]41[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,689.82[/TD]
[TD="class: xl72, width: 80"]2,731.20[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]42[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,725.11[/TD]
[TD="class: xl72, width: 80"]2,732.22[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]43[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,701.29[/TD]
[TD="class: xl72, width: 80"]2,701.33[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]44[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,697.77[/TD]
[TD="class: xl72, width: 80"]2,703.96[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]

[TD="class: xl66, width: 80"]45[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl72, width: 80"]2,713.74[/TD]
[TD="class: xl72, width: 80"]2,747.30[/TD]
[TD="class: xl65, width: 80"][/TD]
[TD="class: xl68, width: 80"][/TD]

</tbody>

 

[matthewlouis]

Bump

 

[Aladin Akyurek]

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?

 

[matthewlouis]

Yes!!! Perfect, thanks for your response and ALL your help.

 

[Aladin Akyurek]

Yes!!! Perfect, thanks for your response and ALL your help.

You are welcome.