Finding the closet two numbers

[jamier1985uk]

Hi, i have a list of numbers in excel, i want to find the two that are the closet to each other.

For example if the list was 2, 4, 9, 152, 153, 234, 324, 521

the closet numbers would be 152 and 153 and the difference between them would be 1 (smallest difference of any combinantion of two of the numbers).


Is there a function to do this? if not is there any other way to do this?

Hope this is clear.

Thanks you in advance.

[NBVC]

Assuming the numbers are listed in ascending order and are in range A2:A9, then try:

For 1st Number:

=INDEX($A$2:$A$9,MATCH(MIN($A$3:$A$9-$A$2:$A$8),$A$3:$A$9-$A$2:$A$8,0))

and

for next number

=INDEX($A$2:$A$9,MATCH(MIN($A$3:$A$9-$A$2:$A$8),$A$3:$A$9-$A$2:$A$8,0)+1)

both formulas must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear.

After any adjustments to the formula you must reconfirm with CSE keys

See attachment for sample.

[oldchippy]

If the numbers are in column A in sequential number, then you could use

=MAX(A2)-MIN(A1) copy down the column

[jamier1985uk]

The numbers are not in sequential order and cannot be put into, any other ideas?

[NBVC]

Perhaps you can use a helper column that relists your numbers in sequential order, then you can apply my previous formulas to the new range.

Use formula: =Small($A$2:$A$1000,Row(A1)) copied down to get the reordered list... adjust range to suit.

[oldchippy]

Or this in column B

=A1-SMALL(A:A,1) copied down

[Ron Coderre]

I'm not sure if you're looking for the 2 closest values or the smallest difference between the 2 closest values.

If your data is in A1:A10

NOTE: Array formulas are committed with CTRL+SHIFT+ENTER, instead of just ENTER.

This ARRAY FORMULA (broken into segments) returns 
the smallest difference between the two closest values:
C1: 
=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)

If you want the 2 values:
This ARRAY FORMULA 
-Sorts the range in ascending order
-Calculates the differences between those values
-Returns the smallest value of the pair with the smallest difference
C2: 
=INDEX(SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT(A1:A10)-1))),
MATCH(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),
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))),0))

and this REGULAR FORMULA returns the second value;
C3: =LARGE(A1:A10,RANK(A4,A1:A10)-1)

Using your posted data:
C1 returns 1
C2 returns 152
C3 returns 153

Is that something you can work with?