How to find the value?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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))
 
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
 
Back
Top