Article updated on February 26, 2009

Problem: Find the smallest difference between the two closest values in a list?

Solution:

Let us break this formula to pieces: =SMALL(SMALL(\$A\$1:\$A\$10, ROW(\$A\$1:INDEX(\$A:\$A, COUNT(A1:A10)-1))+1)-SMALL(\$A\$1:\$A\$10, ROW(\$A\$1:INDEX(\$A:\$A, COUNT(A1:A10)-1))), 1) + Ctrl + Shift + Enter (Matrix formula)

SMALL(\$A\$1:\$A\$10, ROW(\$A\$1:INDEX(\$A:\$A, COUNT(A1:A10)-1))+1)-SMALL(\$A\$1:\$A\$10, ROW(\$A\$1:INDEX(\$A:\$A, COUNT(A1:A10)-1)))

COUNT(A1:A10) counts the number of cells in range A1:A10.

INDEX(\$A:\$A, COUNT(A1:A10)-1) returns the reference of the range \$A:\$A (row 9)

ROW(\$A\$1:INDEX(\$A:\$A, COUNT(A1:A10)-1)) creates a list of row numbers {2,3,4,5,6,7,8,9,10}

SMALL(\$A\$1:\$A\$10, ROW(\$A\$1:INDEX(\$A:\$A,COUNT(A1:A10)-1))+1) takes all the cell values, except the smallest, and sorts them smallest to largest.

SMALL(\$A\$1:\$A\$10, ROW(\$A\$1:INDEX(\$A:\$A, COUNT(A1:A10)-1))) takes all the cell values, except the largest, and sorts them smallest to largest.

15-14=1
18-15=3
22-18=4
35-22=13
49-35=14
55-49=6
77-55=22
80-77=3
88-80=8

=SMALL({1,3,4,13,14,6,22,3,8},1)

1 is the smallest difference between the two closest values.