Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine the values from a list?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine the values from a list?
By 50% I have interpretted that as 50% in the middle of the range i.e ignore
top/bottom (lowest/highest) 25%, based on your example. With data in column A starting row 1: B1: =MIN(OFFSET($A$1,INT(COUNTA(A:A)/4)+1,0,INT(COUNTA(A:A)/2))) B2: =MAX(OFFSET($A$1,INT(COUNTA(A:A)/4)+1,0,INT(COUNTA(A:A)/2))) You might want to adjust for list of even/odd number length. B1=40 in your example, not 41 HTH "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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine the values from a list?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine the values from a list?
On Sat, 24 Mar 2007 00:20:03 -0700, Toppers
wrote: By 50% I have interpretted that as 50% in the middle of the range i.e ignore top/bottom (lowest/highest) 25%, based on your example. This is not how I interprete the problem: "minimum difference on the range within this list covering 50% of total numbers" My interpretation is that the range can be anywhere in the list. Maybe the example could have been better choosen. The following 10 numbers 10,20,30,40,50,55,60,65,70,90 should give 50 and 70 in B1 and B2 respectively if you ask me. (Not 40 and 65) larske |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine the values from a list?
Hello,
That's what I think, too. Select B1:B2 and array-enter =TRANSPOSE(minmaxdensity50(A1:A10)) The UDF minmaxdensity50 has be inserted into a module (press ALT + F11, insert module, then copy text shown below) Regards, Bernd Function minmaxdensity50(r As Range) As Variant Dim i As Long, j As Long, k As Long Dim mi As Double, t As Double Dim vR(0 To 1) As Variant j = Int((r.Count - 2) / 2) mi = Application.WorksheetFunction.Max(r) - _ Application.WorksheetFunction.Min(r) For i = 1 To r.Count - j t = r.Value2(i + j, 1) - r.Value2(i, 1) If t < mi Then mi = t vR(0) = r.Value2(i, 1) vR(1) = r.Value2(i + j, 1) End If Next i minmaxdensity50 = vR End Function |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Thank everyone very much for suggestions
Thank everyone very much for suggestions
That is correct, I miss type the number, the cell B1 should be contained 40 rather than 41. I am very appreciated everyone's helps Thank everyone very much Eric "Lars-Ã…ke Aspelin" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine the values from a list?
Hi Toppers:
I find a bug on your code, could you please give me any suggestion on how to fix it? If the list of numbers is 1 3 6 7 8 9 10 44 88 99 =MIN(OFFSET(A$1,INT(COUNT(A:A)/4)+1,0,INT(COUNT(A:A)/2))) 7 but the minimum should return 6 =MAX(OFFSET(A$1,INT(COUNT(A:A)/4)+1,0,INT(COUNT(A:A)/2))) 44 but the maximum should return 10 Does you have any suggestions? I look forward to your reply Thank you Eric |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine the values from a list?
Hi Bernd:
Could you please tell me how to locate this function under excel - minmaxdensity50? Thank you very much Eric "Bernd" wrote: Hello, That's what I think, too. Select B1:B2 and array-enter =TRANSPOSE(minmaxdensity50(A1:A10)) The UDF minmaxdensity50 has be inserted into a module (press ALT + F11, insert module, then copy text shown below) Regards, Bernd Function minmaxdensity50(r As Range) As Variant Dim i As Long, j As Long, k As Long Dim mi As Double, t As Double Dim vR(0 To 1) As Variant j = Int((r.Count - 2) / 2) mi = Application.WorksheetFunction.Max(r) - _ Application.WorksheetFunction.Min(r) For i = 1 To r.Count - j t = r.Value2(i + j, 1) - r.Value2(i, 1) If t < mi Then mi = t vR(0) = r.Value2(i, 1) vR(1) = r.Value2(i + j, 1) End If Next i minmaxdensity50 = vR End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to determine the values from a list? | Excel Discussion (Misc queries) | |||
How to determine the values from a list? | Excel Worksheet Functions | |||
I need to determine how frequently two values appear together | Excel Discussion (Misc queries) | |||
How to determine geometric mean of 152 values? | Excel Worksheet Functions | |||
List ? - How do I make information in one cell determine list to u | Excel Worksheet Functions |