How to find the value?

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

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.
--
Gary's Student
gsnu200705


"Eric" wrote:

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

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)),OFF SET(INDEX(A1:A6,MATCH(MIN(B1:B6),B1:B6,0)),-1,0))


"Eric" wrote:

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

No need for Offset:

=MEDIAN(INDEX(A1:A6,MATCH(MIN(B1:B6),B1:B6,0)),IND EX(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

"Teethless mama" wrote in message
...
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)),OFF SET(INDEX(A1:A6,MATCH(MIN(B1:B6),B1:B6,0)),-1,0))


"Eric" wrote:

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