Formula Needed for Identifying Higher Number AFTER Lower Number Identified

matthewlouis

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!!
 
Sort by date Sort by votes
DUH! My bad, you're right, sorry. Didn't mean to skip that.

So the 12 prints and then it looks for the next number in D greater than 30.

Sorry about that.
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
matthewlouis said:
DUH! My bad, you're right, sorry. Didn't mean to skip that.

So the 12 prints and then it looks for the next number in D greater than 30.

Sorry about that.
Click to expand...

[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][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
2​
[/td][td]
15​
[/td][td]
30​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
3​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
4​
[/td][td][/td][td][/td][td]field-1[/td][td]field-2[/td][td]field-3[/td][td]field-4[/td][/tr]
[tr][td]
5​
[/td][td][/td][td][/td][td]
20​
[/td][td]
5​
[/td][td][/td][td][/td][/tr]
[tr][td]
6​
[/td][td][/td][td][/td][td]
12​
[/td][td]
7​
[/td][td]
12​
[/td][td][/td][/tr]
[tr][td]
7​
[/td][td][/td][td][/td][td]
30​
[/td][td]
9​
[/td][td][/td][td][/td][/tr]
[tr][td]
8​
[/td][td][/td][td][/td][td]
2​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
9​
[/td][td][/td][td][/td][td]
1​
[/td][td]
32​
[/td][td][/td][td]
32​
[/td][/tr]
[tr][td]
10​
[/td][td][/td][td][/td][td][/td][td]
80​
[/td][td][/td][td][/td][/tr]
[/table]

In E5 enter and copy down:

=IF(1-ISNUMBER(LOOKUP(9.99999999999999E+307,$E$4:E4)),IF(C5<=$A$2,$C5,""),"")

In F5 enter and copy down:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,$E$5:$E$10)),IF(1-ISNUMBER(LOOKUP(9.99999999999999E+307,$F$4:F4)),IF($D5>=$B$2,$D5,""),""),"")
 
Upvote 0
Your first formula worked GREAT, perfect. But the 2nd one did not (?) Here is my actual table, hope this helps explain it better.


<tbody>
[TD="class: xl65"]SPL[/TD]
[TD="class: xl65, width: 80"]SPL High[/TD]
[TD="class: xl66, width: 80"]High[/TD]
[TD="class: xl66, width: 80"]Close[/TD]
[TD="class: xl66, width: 80"]Closed Under SPL[/TD]
[TD="class: xl66, width: 80"]Closed Over High[/TD]
[TD="class: xl67, width: 512"][/TD]

[TD="class: xl68, width: 80"]262.71[/TD]
[TD="class: xl65, width: 80"]263.73[/TD]
[TD="class: xl66, width: 80"][/TD]
[TD="class: xl66, width: 80"][/TD]
[TD="class: xl66, width: 80"][/TD]
[TD="class: xl66, width: 80"][/TD]
[TD="class: xl67, width: 512"][/TD]

[TD="class: xl69, width: 80"][/TD]
[TD="class: xl69, width: 80"][/TD]
[TD="class: xl70, width: 80"]264.43[/TD]
[TD="class: xl82, width: 80"]264.07[/TD]
[TD="class: xl71, width: 80"][/TD]
[TD="class: xl72, align: right"]264.07[/TD]
[TD="class: xl74, width: 512"]Your formula returned this, it isn't right[/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]265.52[/TD]
[TD="class: xl73, width: 80, align: right"]265.51[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]266.38[/TD]
[TD="class: xl73, width: 80, align: right"]266.31[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]267.32[/TD]
[TD="class: xl73, width: 80, align: right"]266.78[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]267.56[/TD]
[TD="class: xl73, width: 80, align: right"]266.75[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]267.22[/TD]
[TD="class: xl73, width: 80, align: right"]265.66[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]267.04[/TD]
[TD="class: xl73, width: 80, align: right"]266.51[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]268.60[/TD]
[TD="class: xl73, width: 80, align: right"]268.20[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]268.53[/TD]
[TD="class: xl73, width: 80, align: right"]267.17[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]268.33[/TD]
[TD="class: xl73, width: 80, align: right"]267.03[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]268.39[/TD]
[TD="class: xl73, width: 80, align: right"]267.58[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]267.64[/TD]
[TD="class: xl73, width: 80, align: right"]267.51[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]267.64[/TD]
[TD="class: xl73, width: 80, align: right"]267.51[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]267.44[/TD]
[TD="class: xl73, width: 80, align: right"]267.19[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]267.73[/TD]
[TD="class: xl73, width: 80, align: right"]267.32[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]267.92[/TD]
[TD="class: xl73, width: 80, align: right"]267.87[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]268.55[/TD]
[TD="class: xl73, width: 80, align: right"]266.86[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]270.64[/TD]
[TD="class: xl73, width: 80, align: right"]270.47[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]272.16[/TD]
[TD="class: xl73, width: 80, align: right"]271.61[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]273.56[/TD]
[TD="class: xl73, width: 80, align: right"]273.42[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]274.10[/TD]
[TD="class: xl73, width: 80, align: right"]273.92[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]275.25[/TD]
[TD="class: xl73, width: 80, align: right"]274.54[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]274.42[/TD]
[TD="class: xl73, width: 80, align: right"]274.12[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]276.12[/TD]
[TD="class: xl73, width: 80, align: right"]276.12[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]278.11[/TD]
[TD="class: xl73, width: 80, align: right"]277.92[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]280.05[/TD]
[TD="class: xl73, width: 80, align: right"]279.61[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]279.96[/TD]
[TD="class: xl73, width: 80, align: right"]279.14[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]280.41[/TD]
[TD="class: xl73, width: 80, align: right"]280.41[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]282.69[/TD]
[TD="class: xl73, width: 80, align: right"]282.69[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]283.62[/TD]
[TD="class: xl73, width: 80, align: right"]283.29[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]284.70[/TD]
[TD="class: xl73, width: 80, align: right"]283.18[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]284.27[/TD]
[TD="class: xl73, width: 80, align: right"]283.30[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]286.63[/TD]
[TD="class: xl73, width: 80, align: right"]286.58[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]286.43[/TD]
[TD="class: xl73, width: 80, align: right"]284.68[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]284.74[/TD]
[TD="class: xl73, width: 80, align: right"]281.76[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]283.30[/TD]
[TD="class: xl73, width: 80, align: right"]281.90[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]283.06[/TD]
[TD="class: xl73, width: 80, align: right"]281.58[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]280.23[/TD]
[TD="class: xl73, width: 80, align: right"]275.45[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]275.85[/TD]
[TD="class: xl73, width: 80, align: right"]263.93[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]269.70[/TD]
[TD="class: xl79, width: 80, align: right"]269.13[/TD]
[TD="class: xl67, width: 80, align: right"]257.63[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl77, width: 80, align: right"]272.36[/TD]
[TD="class: xl81, width: 80, align: right"]267.67[/TD]
[TD="class: xl78, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl76, width: 512"]should be 267.67 because it came AFTER the 257.63[/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]268.17[/TD]
[TD="class: xl80, width: 80, align: right"]257.63[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl75, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]263.61[/TD]
[TD="class: xl73, width: 80, align: right"]261.50[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]267.01[/TD]
[TD="class: xl73, width: 80, align: right"]265.34[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]266.62[/TD]
[TD="class: xl73, width: 80, align: right"]266.00[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]270.00[/TD]
[TD="class: xl73, width: 80, align: right"]269.59[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]273.04[/TD]
[TD="class: xl73, width: 80, align: right"]273.03[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]275.32[/TD]
[TD="class: xl73, width: 80, align: right"]273.11[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]274.72[/TD]
[TD="class: xl73, width: 80, align: right"]270.05[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80"][/TD]
[TD="class: xl73, width: 80, align: right"]273.05[/TD]
[TD="class: xl73, width: 80, align: right"]270.40[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl74, width: 512"][/TD]

</tbody>
 
Last edited:
Upvote 0
See the 257.63 down some n the Close column?
 
Last edited:
Upvote 0
matthewlouis said:
No, that 257.63 is the close under the low in A2, a calc I have somewhere else and is linked to this page.

I need to know when the Close is higher than the High AFTER that first calculation (your first formula) printed.
Click to expand...

Why don't you just put what must be returned in E and F for the sample you posted?
 
Upvote 0
Because the 2nd calculation can't be done until the first one confirms. The 2nd formula you did put a number in the first cell. It should have been 267.67 in the CLOSE column which is the first close HIGHER than the SPL's High ($B$2).

So the E calc = 257.63

The F calc = 267.67

Again, your first formula nailed it, it's the 2nd one that doesn't work.
 
Last edited:
Upvote 0
That table I sent is messed up. Let me resend
To answer your question . . . YES . . . the E calc result = 257.63 comes from D (not from C)

YES . . . the F calc must come from C -- the answer is 267.01 from column C

Thanks for all your help on this, by the way, you're the best.

I'll resend table
 
Last edited:
Upvote 0
matthewlouis said:
That table I sent is messed up. Let me resend
To answer your question . . . YES . . . the E calc result = 257.63 comes from D (not from C)

YES . . . the F calc must come from C -- the answer is 267.01 from column C

Thanks for all your help on this, by the way, you're the best.

I'll resend table
Click to expand...

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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Red over White
  • Solved
Identifying cells that have '+1' at the end of the formula
Replies
4
Views
67
Joe4
Joe4
L
  • Solved
Repeat formula through column X times
Replies
9
Views
251
Fluff
Fluff
Dan Wilson
  • Solved
Help with Identifying Row numbers
2
Replies
15
Views
719
Dan Wilson
Dan Wilson
N
  • Solved
V-Lookup Issues
Replies
4
Views
180
dreid1011
dreid1011
E
  • Question Question
How to: Multiply only numbers that contain values higher than 0
Replies
2
Views
448
eDarren
E

Forum statistics

Threads
1,223,918
Messages
6,175,365
Members
452,638
Latest member
Oluwabukunmi

Share this page

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