How to find the value?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eric - 13 Feb 2007 22:52 GMT

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

Reply to this Message

Gary''s Student - 13 Feb 2007 23:27 GMT

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.

Signature

Gary's Student
gsnu200705

> Referring to the post in General Question
>
[quoted text clipped - 13 lines]
> Thank you in advance
> Eric Choi

Reply to this Message

Teethless mama - 13 Feb 2007 23:50 GMT

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))

> Referring to the post in General Question
>
[quoted text clipped - 13 lines]
> Thank you in advance
> Eric Choi

Reply to this Message

T. Valko - 14 Feb 2007 00:14 GMT

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

> Sort your data (A1:A6) in Ascending order
>
[quoted text clipped - 27 lines]
>> Thank you in advance
>> Eric Choi

Reply to this Message