How to find the value?

[Eric]

Referring to the post in General Question

In cell A1 = 10
In cell A2 = 15
In cell A3 = 20
In cell A4 = 12
In cell A5 = 17
In cell A6 = 23

I would like to determine the minimum difference between any 2 numbers from
above lists. Let assume all number must be positive, in this case, 12-10 = 2
and 17-15 = 2, there are 2 pairs of value to meet the minimum difference
between 2 numbers. Then I would like to determine the minimum median value
from those pairs, in this case (12+10) = 11, then the 11 will display in cell
B1. Does anyone have any suggestion on how to do this calculation in excel?
Thank you in advance
Eric Choi

 

[Gary''s Student]

The first part is simple:

Just sort the values. This puts "close" values "near" to each other:

10
12
15
17
20
23

then in B1 enter:
=A2-A1 and copy down thru B5:

10 2
12 3
15 2
17 3
20 3
23

Then use MIN() on column B to find the minimum and then find all instances
of that minimum.

 

[Teethless mama]

Sort your data (A1:A6) in Ascending order

Leave B1: (blank)
In B2: =A2-A1
Drag Fill Handle to coppy from B2 to B6

In C1:
=MEDIAN(INDEX(A1:A6,MATCH(MIN(B1:B6),B1:B6,0)),OFFSET(INDEX(A1:A6,MATCH(MIN(B1:B6),B1:B6,0)),-1,0))

 

[T. Valko]

No need for Offset:

=MEDIAN(INDEX(A1:A6,MATCH(MIN(B1:B6),B1:B6,0)),INDEX(A1:A6,MATCH(MIN(B1:B6),B1:B6,0)-1))

But, if really want to use Offset:

=MEDIAN(OFFSET(A1,MATCH(MIN(B1:B6),B1:B6,0)-2,,2))

Biff