Finding min difference between any of the values in a list values

[jlapping]

I have about 100 values in column some of which are the same and want to retrieve the smallest difference between any of the values in the list.

For the example of the list of numbers below I would like to retrieve the answer = 0.04

[TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]15.03[/TD]
[/TR]
[TR]
[TD="align: right"]15.11[/TD]
[/TR]
[TR]
[TD="align: right"]15.22[/TD]
[/TR]
[TR]
[TD="align: right"]15.35[/TD]
[/TR]
[TR]
[TD="align: right"]15.16[/TD]
[/TR]
[TR]
[TD="align: right"]15.48[/TD]
[/TR]
[TR]
[TD="align: right"]16.25[/TD]
[/TR]
[TR]
[TD="align: right"]16.08[/TD]
[/TR]
[TR]
[TD="align: right"]15.26[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for any suggestions or ideas...

 

[Special-K99]

This seems to work

=SMALL(SMALL($A$1:$A$9, ROW($A$1:INDEX($A:$A, COUNT(A1:A9)-1))+1)-SMALL($A$1:$A$9, ROW($A$1:INDEX($A:$A, COUNT(A1:A9)-1))), 1)

Source: Excel: Find the smallest difference between the two closest values | Get Digital Help - Microsoft Excel resource

 

[Comfy]

I think this works but haven't done any thorough testing:

Excel 2010
	A	B	C
1	15.03		0.04
2	15.11
3	15.22
4	15.35
5	15.16
6	15.48
7	16.25
8	16.08
9	15.26
Sheet1

Cell Formulas
Range	Formula
C1	=MinAll(A1:A9)

Option Explicit
Function MinAll(rng As Range)
Dim c As Range
Dim vCol As Collection
Dim i As Long
Dim MinTemp(1 To 3) As Variant
MinTemp(1) = 0
MinTemp(2) = 0
MinTemp(3) = 999999999
Set vCol = New Collection


On Error Resume Next
For Each c In rng
    vCol.Add c.Value, CStr(c.Value)
Next c
On Error GoTo 0


Call nRecursion(vCol, 1, MinTemp)


MinAll = MinTemp(3)


End Function
Function nRecursion(vCol As Collection, cIndex As Long, MinTemp() As Variant) As Variant
Dim i As Long
    If cIndex < vCol.Count - 1 Then
        Call nRecursion(vCol, cIndex + 1, MinTemp)
    End If
        Debug.Print "Loop for i = " & cIndex & " to " & vCol.Count
        For i = cIndex To vCol.Count
            If Abs(vCol(cIndex) - vCol(i)) <> 0 And Abs(vCol(cIndex) - vCol(i)) < MinTemp(3) Then
                MinTemp(1) = vCol(cIndex)
                MinTemp(2) = vCol(i)
                MinTemp(3) = Abs(vCol(cIndex) - vCol(i))
            End If
        Next i
End Function

 

[jlapping]

Thanks guys to both of you for taking the time to reply.

I ended up going with the VBA solution and it worked a treat.

Jason