Finding minimum difference of all elements in one row or column?

Hello,

I need to find the minimum difference between any two elements in a row or a column. While it's easy to do for a 3-4 elements by doing subtractions for all elements in the array, doing it for more elements leads to a very long formula.
For example, I need to find the difference between any two elements between C5 and C9:

=MIN(ABS(C5-C6), ABS(C5-C7), ABS(C5-C8), ABS(C5-C9), ABS(C6-C7), ABS(C6-C8), ABS(C6-C9), ABS(C7-C8), ABS(C7-C9), ABS(C8-C9))

Is there a better way of doing it?
MIN(ABS(C5:C8-C6:C9)) finds the minimum difference of two consecutive elements, so I can't use that (OK, I can reduce a few operations, but that's not the point).


Thank You in advance.

If you see that the Minimum diffrence within a range is also the difference beween the smallest one and the second one, it will be easy. Try this:

=SMALL(C5:C9,2)-MIN(C5:C9)

Sorry for that, J10. Your request could be solve with an UDF:

PHP Code: 

Option Base 1
Function MinofDiff(Rng)
Dim ArrRng
Dim TempDiff
Set ArrRng = Rng
MinofDiff = Application.Max(ArrRng)
For i = 1 To ArrRng.Count - 1
For j = i + 1 To ArrRng.Count
    TempDiff = Abs(ArrRng(i) - ArrRng(j))
    MinofDiff = Application.Min(TempDiff, MinofDiff)
Next: Next
End Function 


Note: This Function is used for single row or single column only.

Thank You. Would user-defined function work with older versions of Excel? E.g. Excel 2000.

I have just attached the file, please try. I think it is no problem using UDF in Excel 2000.

J10, please don't quote whole posts -- it just clutters the forum.

Thanks.

I believe this array formula works if all values are unique:

=MIN(IF( (C5:C9 - TRANSPOSE(C5:C9)) < 0, ABS(C5:C9 - TRANSPOSE(C5:C9) ) ) )

Array formulas MUST be confirmed with Ctrl+Shift+Enter, not Enter. You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.

If the values are not unique, then the minimum difference is zero.