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

M

Mayo

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

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

Dnereb

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

Duke Carey

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))
 
D

Domenic

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

Dnereb

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


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 function
 
D

Duke Carey

Clever!

Domenic said:
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!
Click to expand...
 
Top