Look up a value as close as possible to ....
 

The lookup functions in Excel only look for values equal to the
specified value or the largest value below the specified value. I want
my function to find the value as close as possible to the specified
value.

Example: 1.6 ; 1.7; 1.9 ; 2.05 ; 2.1
Lookup functions for finding the value 2 will return 1.9 instead of
2.05.


--
Mayo
------------------------------------------------------------------------
Mayo's Profile: http://www.excelforum.com/member.php...o&userid=26371
View this thread: http://www.excelforum.com/showthread...hreadid=396420

You can write your own functions in VBA...hang on I'll give you this one
as an example.


--
Dnereb
------------------------------------------------------------------------
Dnereb's Profile: http://www.excelforum.com/member.php...o&userid=26182
View this thread: http://www.excelforum.com/showthread...hreadid=396420

There's got to be a better way to handle it than this, but until one of the
MVPs weigh in with an alternative, try this. It assumes you range of values
is in cells B1:F1 and the value you're testing for is in A1.

It's an array formula, meaning you have to enter it by pressing
Ctrl-Shift-Enter.

Also, if the test value equals the mid-point between 2 numbers in the array,
it defaults to the lower number

=IF(ISERROR(HLOOKUP(A1-MIN(ABS(A1-B1:F1)),B1:F1,1,0)),HLOOKUP(A1+MIN(ABS(A1-B1:F1)),B1:F1,1,0),HLOOKUP(A1-MIN(ABS(A1-B1:F1)),B1:F1,1,0))

"Mayo" wrote:


The lookup functions in Excel only look for values equal to the
specified value or the largest value below the specified value. I want
my function to find the value as close as possible to the specified
value.

Example: 1.6 ; 1.7; 1.9 ; 2.05 ; 2.1
Lookup functions for finding the value 2 will return 1.9 instead of
2.05.


--
Mayo
------------------------------------------------------------------------
Mayo's Profile: http://www.excelforum.com/member.php...o&userid=26371
View this thread: http://www.excelforum.com/showthread...hreadid=396420

Try...

=INDEX(A1:A5,MATCH(MIN(ABS(A1:A5-2)),ABS(A1:A5-2),0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

Mayo Wrote:
The lookup functions in Excel only look for values equal to the
specified value or the largest value below the specified value. I want
my function to find the value as close as possible to the specified
value.

Example: 1.6 ; 1.7; 1.9 ; 2.05 ; 2.1
Lookup functions for finding the value 2 will return 1.9 instead of
2.05.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=396420

Code:
--------------------
Function FindNearestValue(StartColumn As Long, StartRow As Long, EndColumn As Long, EndRow As Long, SearchValue As Double) As Double

Dim ClosestValue As Double
Dim Diffrence As Double

Dim I As Long, J As Long

' for smart dicks filling in lower end as start values
If StartColumn < 1 Or StartRow < 1 Or EndColumn < 1 Or EndRow < 1 Then
MsgBox "De rij en kolom nummers moeten 1 of hoger zijn." ' <- omdat je ook nederlands bent
End If

If StartColumn EndColumn Then
I = EndColumn 'switch values
StartColumn = EndColumn
EndColumn = I
End If

If StartRow EndRow Then
I = EndRow 'switch values
StartRow = EndRow
EndRow = I
End If
' set the startvalue as far as possible from the search value
If SearchValue <= 0 Then
FindNearestValue = SearchValue + 1.79769313486231E+308
Else
FindNearestValue = SearchValue - 1.79769313486231E+308
End If

Diffrence = Abs(SearchValue - FindNearestValue) ' Abs makes the value positive.

For I = StartColumn To EndColumn 'this wil create a counter I through all columns
For J = StartRow To EndRow ' the same with rows
If Len(ActiveSheet.Cells(I, J).Value) 0 Then 'this will detect for values only and ignore empty or text fields
' convert everything to positive to avoid sign hazards
If Abs(Abs(ActiveSheet.Cells(I, J).Value) - Abs(SearchValue)) < Diffrence Then
Diffrence = (Abs(ActiveSheet.Cells(I, J).Value) - Abs(SearchValue)) ' store this diffrence
FindNearestValue = ActiveSheet.Cells(I, J).Value ' store the value in the return variable
End If
End If
Next
Next

End Function
--------------------


paste it in a vba module (left ALT F11) and insert-module
save the file and close the editor
you will find it in your userdefined functions


--
Dnereb
------------------------------------------------------------------------
Dnereb's Profile: http://www.excelforum.com/member.php...o&userid=26182
View this thread: http://www.excelforum.com/showthread...hreadid=396420

Clever!

"Domenic" wrote:


Try...

=INDEX(A1:A5,MATCH(MIN(ABS(A1:A5-2)),ABS(A1:A5-2),0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

Mayo Wrote:
The lookup functions in Excel only look for values equal to the
specified value or the largest value below the specified value. I want
my function to find the value as close as possible to the specified
value.

Example: 1.6 ; 1.7; 1.9 ; 2.05 ; 2.1
Lookup functions for finding the value 2 will return 1.9 instead of
2.05.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=396420