On Fri, 23 Mar 2007 17:18:00 -0700, Eric
wrote:

Re-post the question

Does anyone have any idea on how to determine the values under following
conditions?

Given a list of numbers sorted by ascending order from top to bottom under
column A, I would like to determine the minimum difference on the range
within this list covering 50% of total numbers, and return the smallest
number within the range in cell B1 and the largest number within the range in
cell B2.

For example, if the list contains 10 numbers, then I need to select 5 numbers
covering the minimum range, such as following example with 10 numbers, and I
need to select 50% of 10 = 5 numbers covering minimum ranges.

1,3,21,40,41,42,43,44,88,99

In this case, I should select 40,41,42,43,44
41 returns in cell B1
44 returns in cell B2

Does anyone have any suggestions?
Thank you in advance
Eric


I assume that you want cell B1 to contain 40 rather than 41.

In the below I have generalized the problem to have any number of
numbers (but nothing else) in the column A.
If you always have 10 numbers you can replace COUNT(A:A) with 10 and
A:A with A1:A10 everywhere.

Introduce a help column, C, where you put the following in cell C1
and copy it down to cell Cn where n = 6 in the special case and
COUNT(A:A)/2+1 in the general case.

=INDEX(A:A;ROW()+COUNT(A:A)/2-1)-A1

In cell B1 put the following:
=INDEX(A:A;MATCH(SMALL(OFFSET(C1;0;0;COUNT(A:A)/2+1;1);OFFSET(C1;0;0;COUNT(A:A)/2+1);FALSE))

In cell B2 put the following:
=INDEX(A:A;MATCH(SMALL(OFFSET(C1;0;0;COUNT(A:A)/2+1;1);OFFSET(C1;0;0;COUNT(A:A)/2+1);FALSE)+COUNT(A:A)/2-1)

Hope this helps

larske