How to determine the values from a list?
 

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.
Does anyone have any suggestions?
Thank you in advance
Eric

How to determine the values from a list?
 

Hi there Eric,

Which 50% of the numbers do you want to look at?

--
Regards,
Zack Barresse, aka firefytr
MsgBox "fire" & "fytr" & Chr(64) & "vba" & "express" & Chr(46) & "com"



"Eric" wrote in message
...
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.
Does anyone have any suggestions?
Thank you in advance
Eric

How to determine the values from a list?
 

Hi Zack Barresse:
If the list contains 100 numbers, then I need to select 50 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

Thank you for any suggestions
Eric


"Zack Barresse" wrote:

Hi there Eric,

Which 50% of the numbers do you want to look at?

--
Regards,
Zack Barresse, aka firefytr
MsgBox "fire" & "fytr" & Chr(64) & "vba" & "express" & Chr(46) & "com"



"Eric" wrote in message
...
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.
Does anyone have any suggestions?
Thank you in advance
Eric

How to determine the values from a list?
 

Hi,

I can recommend that you copy all the columns to a single excle sheet. This
is basicall to run an advance query which will fetch you all the information
for you to compare. I shall explain you how to do it.

Assuming that you have copied all the columns to a single spread sheet, you
know the column heading now, save the excel sheet.

Now I wiil be using the Advance filtering techniques so that all the column
information are retrieved even if the typed cells are misspelled, which
normally is not the case when you do a simple filtering technique. The
filtered values may not be correct.

To retieve the values;

1. Select all records and the column headings and define a name for the
range List. This is the List range name.
2. Select all column headings and paste in a seperate row anywhere in the
spreadsheet.
2. Select the pasted column heading along with an empty row and define a
name. This is the criteria range name.
3. Define a criteria on the second row for all columns, if necessary. For
example S* will display all details specific to "S'.
4. Click Data- Filter- Advanced Filter
5. Type the List name
6. Type the Criteria Name
7. Click copy to another location option - Mandatory
8. Click Copy to
9. Click the cell below the criteria range

All data will be displayed specific to the query you requested. This is very
usefull if the spreadsheet data are mistyped or mis-spelled. Now you have
done a database funtion on your excle spread sheet.

Challa Prabhu



"Eric" wrote:

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.
Does anyone have any suggestions?
Thank you in advance
Eric

How to determine the values from a list?
 

Are you sure you've answered Eric's question, Challa?
--
David Biddulph

"challa prabhu" wrote in message
...

Hi,

I can recommend that you copy all the columns to a single excle sheet.
This
is basicall to run an advance query which will fetch you all the
information
for you to compare. I shall explain you how to do it.

Assuming that you have copied all the columns to a single spread sheet,
you
know the column heading now, save the excel sheet.

Now I wiil be using the Advance filtering techniques so that all the
column
information are retrieved even if the typed cells are misspelled, which
normally is not the case when you do a simple filtering technique. The
filtered values may not be correct.

To retieve the values;

1. Select all records and the column headings and define a name for the
range List. This is the List range name.
2. Select all column headings and paste in a seperate row anywhere in the
spreadsheet.
2. Select the pasted column heading along with an empty row and define a
name. This is the criteria range name.
3. Define a criteria on the second row for all columns, if necessary. For
example S* will display all details specific to "S'.
4. Click Data- Filter- Advanced Filter
5. Type the List name
6. Type the Criteria Name
7. Click copy to another location option - Mandatory
8. Click Copy to
9. Click the cell below the criteria range

All data will be displayed specific to the query you requested. This is
very
usefull if the spreadsheet data are mistyped or mis-spelled. Now you have
done a database funtion on your excle spread sheet.

Challa Prabhu



"Eric" wrote:

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.
Does anyone have any suggestions?
Thank you in advance
Eric

How to determine the values from a list?
 

Hi David Biddulph,

Thankyou for correcting. I misunderstood the question. I apologize for that.

Challa Prabhu

"David Biddulph" wrote:

Are you sure you've answered Eric's question, Challa?
--
David Biddulph

"challa prabhu" wrote in message
...

Hi,

I can recommend that you copy all the columns to a single excle sheet.
This
is basicall to run an advance query which will fetch you all the
information
for you to compare. I shall explain you how to do it.

Assuming that you have copied all the columns to a single spread sheet,
you
know the column heading now, save the excel sheet.

Now I wiil be using the Advance filtering techniques so that all the
column
information are retrieved even if the typed cells are misspelled, which
normally is not the case when you do a simple filtering technique. The
filtered values may not be correct.

To retieve the values;

1. Select all records and the column headings and define a name for the
range List. This is the List range name.
2. Select all column headings and paste in a seperate row anywhere in the
spreadsheet.
2. Select the pasted column heading along with an empty row and define a
name. This is the criteria range name.
3. Define a criteria on the second row for all columns, if necessary. For
example S* will display all details specific to "S'.
4. Click Data- Filter- Advanced Filter
5. Type the List name
6. Type the Criteria Name
7. Click copy to another location option - Mandatory
8. Click Copy to
9. Click the cell below the criteria range

All data will be displayed specific to the query you requested. This is
very
usefull if the spreadsheet data are mistyped or mis-spelled. Now you have
done a database funtion on your excle spread sheet.

Challa Prabhu



"Eric" wrote:

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.
Does anyone have any suggestions?
Thank you in advance
Eric

How to determine the values from a list?
 

The 'minimum' range? I don't understand. If you're looking at the top 50%
of numbers and you want to return the min/max from that range...

In B1 enter:
=SUMPRODUCT(MIN(SMALL(A1:A10,INT(COUNT(A1:A10)/2)+1)))

In B2 enter:
=SUMPRODUCT(MAX(SMALL(A1:A10,INT(COUNT(A1:A10)))))

Hope I understood you alright. These would return 42 and 99 from your
sorted list (placed in A1:A10). The items you said to look at were neither
the minimum range nor the maximum range, but a range from the middle of the
sorted list. This is why I'm hoping I understood what your requirements
were. If not, please post back.

HTH

--
Regards,
Zack Barresse, aka firefytr
MsgBox "fire" & "fytr" & Chr(64) & "vba" & "express" & Chr(46) & "com"



"Eric" wrote in message
...
Hi Zack Barresse:
If the list contains 100 numbers, then I need to select 50 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

Thank you for any suggestions
Eric


"Zack Barresse" wrote:

Hi there Eric,

Which 50% of the numbers do you want to look at?

--
Regards,
Zack Barresse, aka firefytr
MsgBox "fire" & "fytr" & Chr(64) & "vba" & "express" & Chr(46) & "com"



"Eric" wrote in message
...
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.
Does anyone have any suggestions?
Thank you in advance
Eric

How to determine the values from a list?
 

No, I think he was looking for the 5 consecutive numbers which give the
minimum difference between first and last, and to return the extremes of
that minimum range.
--
David Biddulph

"Zack Barresse" wrote in message
...
The 'minimum' range? I don't understand. If you're looking at the top
50% of numbers and you want to return the min/max from that range...

In B1 enter:
=SUMPRODUCT(MIN(SMALL(A1:A10,INT(COUNT(A1:A10)/2)+1)))

In B2 enter:
=SUMPRODUCT(MAX(SMALL(A1:A10,INT(COUNT(A1:A10)))))

Hope I understood you alright. These would return 42 and 99 from your
sorted list (placed in A1:A10). The items you said to look at were
neither the minimum range nor the maximum range, but a range from the
middle of the sorted list. This is why I'm hoping I understood what your
requirements were. If not, please post back.

HTH

--
Regards,
Zack Barresse, aka firefytr
MsgBox "fire" & "fytr" & Chr(64) & "vba" & "express" & Chr(46) & "com"



"Eric" wrote in message
...
Hi Zack Barresse:
If the list contains 100 numbers, then I need to select 50 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

Thank you for any suggestions
Eric


"Zack Barresse" wrote:

Hi there Eric,

Which 50% of the numbers do you want to look at?

--
Regards,
Zack Barresse, aka firefytr
MsgBox "fire" & "fytr" & Chr(64) & "vba" & "express" & Chr(46) & "com"



"Eric" wrote in message
...
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.
Does anyone have any suggestions?
Thank you in advance
Eric

How to determine the values from a list?
 

Yes, it is what I am looking for the minimum difference between first and last.
Thank everyone for suggestions
Eric

"David Biddulph" wrote:

No, I think he was looking for the 5 consecutive numbers which give the
minimum difference between first and last, and to return the extremes of
that minimum range.
--
David Biddulph

"Zack Barresse" wrote in message
...
The 'minimum' range? I don't understand. If you're looking at the top
50% of numbers and you want to return the min/max from that range...

In B1 enter:
=SUMPRODUCT(MIN(SMALL(A1:A10,INT(COUNT(A1:A10)/2)+1)))

In B2 enter:
=SUMPRODUCT(MAX(SMALL(A1:A10,INT(COUNT(A1:A10)))))

Hope I understood you alright. These would return 42 and 99 from your
sorted list (placed in A1:A10). The items you said to look at were
neither the minimum range nor the maximum range, but a range from the
middle of the sorted list. This is why I'm hoping I understood what your
requirements were. If not, please post back.

HTH

--
Regards,
Zack Barresse, aka firefytr
MsgBox "fire" & "fytr" & Chr(64) & "vba" & "express" & Chr(46) & "com"



"Eric" wrote in message
...
Hi Zack Barresse:
If the list contains 100 numbers, then I need to select 50 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

Thank you for any suggestions
Eric


"Zack Barresse" wrote:

Hi there Eric,

Which 50% of the numbers do you want to look at?

--
Regards,
Zack Barresse, aka firefytr
MsgBox "fire" & "fytr" & Chr(64) & "vba" & "express" & Chr(46) & "com"



"Eric" wrote in message
...
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.
Does anyone have any suggestions?
Thank you in advance
Eric

Thank everyone for suggestions
 

I find the answer under the worksheet functions
Thank everyone very much for suggestions
Eric