Finding the first greater value in a range

[djzabala]

Hello everyone.
I'm new here, and I have a little problem

I'm working in an excel workbook, that has 2 columns, the X column ar
time values (continous) and the Y column has some values (that are NO
sorted. and should remain like this, since they are time-dependent)

What I need to do is search for a value within the (Y) Column; thi
value must be the FIRST value (in the Up/Down direction) that i
Greater than the Required Value (the Input value that a user types in
cell, in this case).

i.e. if a have a set of data that ranges from 20 to 100 (but there ar
peaks, with values that raise and fall..) I want the value "30", bu
there's a "32" that appears first, then the value that interests is 32
because it's the first value that is greater than 30.

If the value "30" is actually the first occurence, then that valu
should be retrieved.

i have tried with Vlookup and Match and all those functions, bu
sometimes it works correctly and sometimes not. this should be becaus
the data to look in is not sorted.

Any ideas with worksheet function or VBA, it is greatly welcomed..

 

[Aladin Akyurek]

=INDEX(B2:B16,MIN(IF(B2:B16>=D2,ROW(B2:B16)))-CELL("Row",B2)+1)

which must be confirmed with control+shift+enter instead of just with enter.

 

[Frank Kabel]

Hi
for getting this value try the following array formula (entered with
CTRL+SHIFT+ENTER):
=INDEX(B1:B100,MATCH(TRUE,B1:B100>=30,0))

 

[Aladin Akyurek]

A shorter formula, as proposed in another reply, is better though.

=INDEX(B2:B16,MIN(IF(B2:B16>=D2,ROW(B2:B16)))-CELL("Row",B2)+1)

which must be confirmed with control+shift+enter instead of just with enter.

 

[djzabala]

thanks you guys!!!!, that worked fine for the objective I was aimin
for!!!!

Just another quick question...

how do I arrange my formulas so they can fit to the size of the rang
they for the calculation?. I mean, the ranges that I use to do tha
search may vary in size (just more or less rows, the columns remain a
is.

 

[Frank Kabel]

Hi
just make the ranges larger enough.e.g. use
A1:A10000

should not affect the calculation time for MATCH

 

[Aladin Akyurek]

=OFFSET(B2,MATCH(TRUE,B2:INDEX(B:B,MATCH(9.99999999999999E+307,B:B))>=D2,0)-
1,0,1)

which must be confirmed with control+shift+enter instead of just with enter.

D2 houses a criterion value.

 

[Aladin Akyurek]

Hi
just make the ranges larger enough.e.g. use
A1:A10000

should not affect the calculation time for MATCH

MATCH with match-type set to 0 runs a linear search, so with bigger ranges
it will take, on the average, longer time to calculate.

 

[hgrove]

Aladin Akyurek wrote...

MATCH with match-type set to 0 runs a linear search, so with
bigger ranges it will take, on the average, longer time to
calculate.

Not if it returns immediately upon finding a match. In other words, if
it works like

For Each c In Rng.Columns(1).Cells
If c.Value Like LookupValue Then
result = c.Row - Rng.Row + 1
Exit Function
End If
Next c

Then there should be no execution time difference whether you feed it a
100 cell range or a 10000 cell range if the first match is in row 73. If
the first match would be in row 591, calling MATCH with the 100 cell
range will return more quickly, but with #N/A.

Only if there's no match at all would the size of its 2nd argument
matter. Only if the range in question could vary in size over an order
of magnitude would this really matter. Besides, the additional function
calls necessary to limit the range aren't free.

You both have Fast Excel, IIRC, so both of you could test this.

 

[Frank Kabel]

[....]

Not if it returns immediately upon finding a match. In other words, if
it works like

For Each c In Rng.Columns(1).Cells
If c.Value Like LookupValue Then
result = c.Row - Rng.Row + 1
Exit Function
End If
Next c

Then there should be no execution time difference whether you feed it a
100 cell range or a 10000 cell range if the first match is in row 73. If
the first match would be in row 591, calling MATCH with the 100 cell
range will return more quickly, but with #N/A.

Only if there's no match at all would the size of its 2nd argument
matter. Only if the range in question could vary in size over an order
of magnitude would this really matter. Besides, the additional function
calls necessary to limit the range aren't free.

You both have Fast Excel, IIRC, so both of you could test this.

test this last night and yes if MATCH finds a match the execution stays
the same no matter what range you use. Only if no match exist the
execution speed changes

Frank

 

[Aladin Akyurek]

[...]

Then there should be no execution time difference whether you feed it a
100 cell range or a 10000 cell range if the first match is in row 73. If
the first match would be in row 591, calling MATCH with the 100 cell
range will return more quickly, but with #N/A.

Only if there's no match at all would the size of its 2nd argument
matter. Only if the range in question could vary in size over an order
of magnitude would this really matter. Besides, the additional function
calls necessary to limit the range aren't free.

Agreed about the effect of the size of the range fed to MATCH(). I just
picked out the wrong matter.
The real issue is of course the conditional:

B2:B16>=D2

If the "sufficiently" longe range and the real data range differ
considerably in size, we will be increasing the costs unnecessarily.
However, the solution

{=OFFSET(B2,MATCH(TRUE,B2:INDEX(B:B,MATCH(9.99999999999999E+307,B:B))>=D2,0)
-
1,0,1)}

I suggested worsens performance because of the volatile OFFSET(). I'd
suggest a 2-cell approach:

D2: Criterion value.

D3:

{=MATCH(TRUE,B2:INDEX(B:B,MATCH(9.99999999999999E+307,B:B))>=D2,0)}

D4:

=INDEX(B:B,D3+CELL("Row",B2)-1)