| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Any formula to return the most frequent bin range?
Hi all , In a row i have : A1= 450 B1=560 C1=500 D1=510 E1=445 F1=430 G1= 420 Those values represent several weights of a product prepared in this case 7 times, now that i want to decide what is the most relative weight to use on my list, i need a formula to tell me what is the most frequent bin scored given a difference of 50 grs maximum . The formula should return the following answer : 400-450 which means that the most frequent weight bin is between 400 and 450 grs , now the 50 should be variable so if i change it somehow in the formula to 100 it should return 400-500 which means that the most frequent weight bin falls between 400 and 500 grs. Any smart frequency formula can do that ? i appreciate anyone's help, thank you much. -- hankach |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any formula to return the most frequent bin range?
With your sample data:
A1:=450 B1=560 C1=500 D1=510 E1=445 F1=430 G1= 420 Try this: A3: (the bottom of the first bin...eg 400) A4: (the increment....eg 50) This formula (in sections) returns the lower limit of the bin with the most occurrences: C3: =MODE(LOOKUP(A1:INDEX(1:1,COUNTA(1:1)),A3+ (ROW(INDEX(A:A,1):INDEX(A:A,1+CEILING(MAX(1:1)- A3,A4)/A4))-1)*A4)) This formula returns the upper limit of that bin: D3: =" to "&(C3+A4) Using the sample data... The bins a 400 to 450 450 to 500 500 to 550 550 to 600 and... C3 returns: 400 D3 returns: to 450 Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "hankach" wrote in message ... Hi all , In a row i have : A1= 450 B1=560 C1=500 D1=510 E1=445 F1=430 G1= 420 Those values represent several weights of a product prepared in this case 7 times, now that i want to decide what is the most relative weight to use on my list, i need a formula to tell me what is the most frequent bin scored given a difference of 50 grs maximum . The formula should return the following answer : 400-450 which means that the most frequent weight bin is between 400 and 450 grs , now the 50 should be variable so if i change it somehow in the formula to 100 it should return 400-500 which means that the most frequent weight bin falls between 400 and 500 grs. Any smart frequency formula can do that ? i appreciate anyone's help, thank you much. -- hankach |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any formula to return the most frequent bin range?
On Sat, 5 Apr 2008 19:02:37 +0100, hankach
wrote: Hi all , In a row i have : A1= 450 B1=560 C1=500 D1=510 E1=445 F1=430 G1= 420 Those values represent several weights of a product prepared in this case 7 times, now that i want to decide what is the most relative weight to use on my list, i need a formula to tell me what is the most frequent bin scored given a difference of 50 grs maximum . The formula should return the following answer : 400-450 which means that the most frequent weight bin is between 400 and 450 grs , now the 50 should be variable so if i change it somehow in the formula to 100 it should return 400-500 which means that the most frequent weight bin falls between 400 and 500 grs. Any smart frequency formula can do that ? i appreciate anyone's help, thank you much. You can try the following formula if you don't mind something rather complicated. The output of the formula is a string, e.g. "450-500". Weights is the range where you have your numbers, e.g. 450, 560, etc Diff is the cell where you have your difference, e.g. 50 =INDEX(Diff*(FLOOR(SMALL(Weights;1);Diff)/Diff-1+ROW(OFFSET( A1;0;0;CEILING(LARGE(Weights;1);Diff)/Diff-FLOOR(SMALL(Weights;1);Diff)/Diff+1;1))); MATCH(MAX(FREQUENCY(Weights;Diff*(FLOOR(SMALL(Weig hts;1);Diff)/Diff+ ROW(OFFSET(A1;0;0;CEILING(LARGE(Weights;1);Diff)/Diff-FLOOR(SMALL( Weights;1);Diff)/Diff;1))));0);FREQUENCY(Weights;Diff*(FLOOR(SMALL( Weights;1);Diff)/Diff+ROW(OFFSET(A1;0;0;CEILING(LARGE(Weights;1);Di ff)/Diff- FLOOR(SMALL(Weights;1);Diff)/Diff;1))));0))&"-"&INDEX(Diff*(FLOOR(SMALL(Weights;1);Diff)/Diff-1+ ROW(OFFSET(A1;0;0;CEILING(LARGE(Weights;1);Diff)/Diff-FLOOR(SMALL(Weights;1);Diff)/Diff+1;1)));1+ MATCH(MAX(FREQUENCY(Weights;Diff*(FLOOR(SMALL(Weig hts;1);Diff)/Diff+ROW( OFFSET(A1;0;0;CEILING(LARGE(Weights;1);Diff)/Diff-FLOOR(SMALL(Weights;1);Diff)/Diff;1))))); FREQUENCY(Weights;Diff*(FLOOR(SMALL(Weights;1);Dif f)/Diff+ROW(OFFSET(A1;0;0;E13;1))));0)) Replace all semicolons with comma, if you have comma as parameter separator. Hope this helps Lars-Åke |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any formula to return the most frequent bin range?
On Sun, 06 Apr 2008 00:54:51 GMT, Lars-Åke Aspelin
wrote: On Sat, 5 Apr 2008 19:02:37 +0100, hankach wrote: Hi all , In a row i have : A1= 450 B1=560 C1=500 D1=510 E1=445 F1=430 G1= 420 Those values represent several weights of a product prepared in this case 7 times, now that i want to decide what is the most relative weight to use on my list, i need a formula to tell me what is the most frequent bin scored given a difference of 50 grs maximum . The formula should return the following answer : 400-450 which means that the most frequent weight bin is between 400 and 450 grs , now the 50 should be variable so if i change it somehow in the formula to 100 it should return 400-500 which means that the most frequent weight bin falls between 400 and 500 grs. Any smart frequency formula can do that ? i appreciate anyone's help, thank you much. You can try the following formula if you don't mind something rather complicated. The output of the formula is a string, e.g. "450-500". Weights is the range where you have your numbers, e.g. 450, 560, etc Diff is the cell where you have your difference, e.g. 50 =INDEX(Diff*(FLOOR(SMALL(Weights;1);Diff)/Diff-1+ROW(OFFSET( A1;0;0;CEILING(LARGE(Weights;1);Diff)/Diff-FLOOR(SMALL(Weights;1);Diff)/Diff+1;1))); MATCH(MAX(FREQUENCY(Weights;Diff*(FLOOR(SMALL(Wei ghts;1);Diff)/Diff+ ROW(OFFSET(A1;0;0;CEILING(LARGE(Weights;1);Diff )/Diff-FLOOR(SMALL( Weights;1);Diff)/Diff;1))));0);FREQUENCY(Weights;Diff*(FLOOR(SMALL( Weights;1);Diff)/Diff+ROW(OFFSET(A1;0;0;CEILING(LARGE(Weights;1);Di ff)/Diff- FLOOR(SMALL(Weights;1);Diff)/Diff;1))));0))&"-"&INDEX(Diff*(FLOOR(SMALL(Weights;1);Diff)/Diff-1+ ROW(OFFSET(A1;0;0;CEILING(LARGE(Weights;1);Diff )/Diff-FLOOR(SMALL(Weights;1);Diff)/Diff+1;1)));1+ MATCH(MAX(FREQUENCY(Weights;Diff*(FLOOR(SMALL(Wei ghts;1);Diff)/Diff+ROW( OFFSET(A1;0;0;CEILING(LARGE(Weights;1);Diff)/Diff-FLOOR(SMALL(Weights;1);Diff)/Diff;1))))); FREQUENCY(Weights;Diff*(FLOOR(SMALL(Weights;1);Di ff)/Diff+ROW(OFFSET(A1;0;0;E13;1))));0)) Replace all semicolons with comma, if you have comma as parameter separator. Hope this helps Lars-Åke And if you can accept that the formula just gives the lower limit of the interval, e.g. 450 this is a shorter formula: =INDEX(Diff*(FLOOR(SMALL(Weights;1);Diff)/Diff-1+ROW(OFFSET (A1;0;0;CEILING(LARGE(Weights;1);Diff)/Diff-FLOOR(SMALL(Weights;1);Diff)/Diff+1;1))); MATCH(MAX(FREQUENCY(Weights;Diff*(FLOOR(SMALL(Weig hts;1);Diff)/Diff+ ROW(OFFSET(A1;0;0;CEILING(LARGE(Weights;1);Diff)/Diff-FLOOR(SMALL( Weights;1);Diff)/Diff;1))));0);FREQUENCY(Weights;Diff*(FLOOR(SMALL( Weights;1);Diff)/Diff+ROW(OFFSET(A1;0;0;CEILING(LARGE(Weights;1);Di ff)/Diff- FLOOR(SMALL(Weights;1);Diff)/Diff;1))));0)) If you put this in a cell named LowLimit you can obtain the string you want, e.g. "450-500" by the formula =LowLimit&"-"&LowLimit+Diff Lars-Åke |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any formula to return the most frequent bin range?
On Sat, 5 Apr 2008 19:02:37 +0100, hankach
wrote: Hi all , In a row i have : A1= 450 B1=560 C1=500 D1=510 E1=445 F1=430 G1= 420 Those values represent several weights of a product prepared in this case 7 times, now that i want to decide what is the most relative weight to use on my list, i need a formula to tell me what is the most frequent bin scored given a difference of 50 grs maximum . The formula should return the following answer : 400-450 which means that the most frequent weight bin is between 400 and 450 grs , now the 50 should be variable so if i change it somehow in the formula to 100 it should return 400-500 which means that the most frequent weight bin falls between 400 and 500 grs. Any smart frequency formula can do that ? i appreciate anyone's help, thank you much. Given the following named ranges: rng refers to: your data range BinSize refers to: a cell containing the bin size Then the lower bound of your first most frequent weight bin is: =INDEX(ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize&":" &CEILING(MAX(rng),BinSize)/BinSize))*BinSize,-1+MATCH(MAX( FREQUENCY(rng,ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize& ":"&CEILING(MAX(rng),BinSize)/BinSize))*BinSize)),FREQUENCY( rng,ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize&":" &CEILING(MAX(rng),BinSize)/BinSize))*BinSize),0)) and the upper bound is: =INDEX(ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize&":" &CEILING(MAX(rng),BinSize)/BinSize))*BinSize,MATCH(MAX( FREQUENCY(rng,ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize& ":"&CEILING(MAX(rng),BinSize)/BinSize))*BinSize)),FREQUENCY( rng,ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize&":" &CEILING(MAX(rng),BinSize)/BinSize))*BinSize),0)) If you need it all in one cell, you can concatenate the two formulas: =INDEX(ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize&":" &CEILING(MAX(rng),BinSize)/BinSize))*BinSize,-1+MATCH(MAX( FREQUENCY(rng,ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize& ":"&CEILING(MAX(rng),BinSize)/BinSize))*BinSize)),FREQUENCY( rng,ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize&":" &CEILING(MAX(rng),BinSize)/BinSize))*BinSize),0))&"-"& INDEX(ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize&":" &CEILING(MAX(rng),BinSize)/BinSize))*BinSize,MATCH(MAX( FREQUENCY(rng,ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize& ":"&CEILING(MAX(rng),BinSize)/BinSize))*BinSize)),FREQUENCY( rng,ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize&":" &CEILING(MAX(rng),BinSize)/BinSize))*BinSize),0)) Note that if there is more than one bin that is equally "most frequent", this will return the "first" bin (the one with the lowest value). --ron |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any formula to return the most frequent bin range?
With a contiguous row of values beginning in cell A1
and A3: (the bottom of the first bin...eg 400) A4: (the bin increment....eg 50) Try this all-in-one, non-array formula: B3: =LOOKUP(MODE(LOOKUP(A1:INDEX(1:1,COUNTA(1:1)), A3+(ROW(INDEX(A:A,1):INDEX(A:A,1+CEILING(MAX(1:1)-A3, A4)/A4))-1)*A4))&"Z",A3+(ROW(INDEX(A:A,1):INDEX(A:A, 1+CEILING(MAX(1:1)-A3,A4)/A4))-1)*A4&" to "&(A3+(ROW(INDEX( A:A,1):INDEX(A:A,1+CEILING(MAX(1:1)-A3,A4)/A4))-1)*A4+A4)) Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Ron Coderre" wrote in message ... With your sample data: A1:=450 B1=560 C1=500 D1=510 E1=445 F1=430 G1= 420 Try this: A3: (the bottom of the first bin...eg 400) A4: (the increment....eg 50) This formula (in sections) returns the lower limit of the bin with the most occurrences: C3: =MODE(LOOKUP(A1:INDEX(1:1,COUNTA(1:1)),A3+ (ROW(INDEX(A:A,1):INDEX(A:A,1+CEILING(MAX(1:1)- A3,A4)/A4))-1)*A4)) This formula returns the upper limit of that bin: D3: =" to "&(C3+A4) Using the sample data... The bins a 400 to 450 450 to 500 500 to 550 550 to 600 and... C3 returns: 400 D3 returns: to 450 Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "hankach" wrote in message ... Hi all , In a row i have : A1= 450 B1=560 C1=500 D1=510 E1=445 F1=430 G1= 420 Those values represent several weights of a product prepared in this case 7 times, now that i want to decide what is the most relative weight to use on my list, i need a formula to tell me what is the most frequent bin scored given a difference of 50 grs maximum . The formula should return the following answer : 400-450 which means that the most frequent weight bin is between 400 and 450 grs , now the 50 should be variable so if i change it somehow in the formula to 100 it should return 400-500 which means that the most frequent weight bin falls between 400 and 500 grs. Any smart frequency formula can do that ? i appreciate anyone's help, thank you much. -- hankach |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any formula to return the most frequent bin range?
I removed some extraneous calculations.
This formula is a bit shorter (by 5 characters) B3: =LOOKUP(MODE(LOOKUP(A1:INDEX(1:1,COUNTA(1:1)), A3+(ROW(INDEX(A:A,1):INDEX(A:A,1+CEILING(MAX(1:1)-A3, A4)/A4))-1)*A4))&"Z",A3+(ROW(INDEX(A:A,1):INDEX(A:A, 1+CEILING(MAX(1:1)-A3,A4)/A4))-1)*A4&" to "&(A3+(ROW(INDEX( A:A,1):INDEX(A:A,1+CEILING(MAX(1:1)-A3,A4)/A4)))*A4)) -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Ron Coderre" wrote in message ... With a contiguous row of values beginning in cell A1 and A3: (the bottom of the first bin...eg 400) A4: (the bin increment....eg 50) Try this all-in-one, non-array formula: B3: =LOOKUP(MODE(LOOKUP(A1:INDEX(1:1,COUNTA(1:1)), A3+(ROW(INDEX(A:A,1):INDEX(A:A,1+CEILING(MAX(1:1)-A3, A4)/A4))-1)*A4))&"Z",A3+(ROW(INDEX(A:A,1):INDEX(A:A, 1+CEILING(MAX(1:1)-A3,A4)/A4))-1)*A4&" to "&(A3+(ROW(INDEX( A:A,1):INDEX(A:A,1+CEILING(MAX(1:1)-A3,A4)/A4))-1)*A4+A4)) Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Ron Coderre" wrote in message ... With your sample data: A1:=450 B1=560 C1=500 D1=510 E1=445 F1=430 G1= 420 Try this: A3: (the bottom of the first bin...eg 400) A4: (the increment....eg 50) This formula (in sections) returns the lower limit of the bin with the most occurrences: C3: =MODE(LOOKUP(A1:INDEX(1:1,COUNTA(1:1)),A3+ (ROW(INDEX(A:A,1):INDEX(A:A,1+CEILING(MAX(1:1)- A3,A4)/A4))-1)*A4)) This formula returns the upper limit of that bin: D3: =" to "&(C3+A4) Using the sample data... The bins a 400 to 450 450 to 500 500 to 550 550 to 600 and... C3 returns: 400 D3 returns: to 450 Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "hankach" wrote in message ... Hi all , In a row i have : A1= 450 B1=560 C1=500 D1=510 E1=445 F1=430 G1= 420 Those values represent several weights of a product prepared in this case 7 times, now that i want to decide what is the most relative weight to use on my list, i need a formula to tell me what is the most frequent bin scored given a difference of 50 grs maximum . The formula should return the following answer : 400-450 which means that the most frequent weight bin is between 400 and 450 grs , now the 50 should be variable so if i change it somehow in the formula to 100 it should return 400-500 which means that the most frequent weight bin falls between 400 and 500 grs. Any smart frequency formula can do that ? i appreciate anyone's help, thank you much. -- hankach |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any formula to return the most frequent bin range?
On Sun, 06 Apr 2008 01:18:47 GMT, Lars-Åke Aspelin
wrote: On Sun, 06 Apr 2008 00:54:51 GMT, Lars-Åke Aspelin wrote: On Sat, 5 Apr 2008 19:02:37 +0100, hankach wrote: Hi all , In a row i have : A1= 450 B1=560 C1=500 D1=510 E1=445 F1=430 G1= 420 Those values represent several weights of a product prepared in this case 7 times, now that i want to decide what is the most relative weight to use on my list, i need a formula to tell me what is the most frequent bin scored given a difference of 50 grs maximum . The formula should return the following answer : 400-450 which means that the most frequent weight bin is between 400 and 450 grs , now the 50 should be variable so if i change it somehow in the formula to 100 it should return 400-500 which means that the most frequent weight bin falls between 400 and 500 grs. Any smart frequency formula can do that ? i appreciate anyone's help, thank you much. You can try the following formula if you don't mind something rather complicated. The output of the formula is a string, e.g. "450-500". Weights is the range where you have your numbers, e.g. 450, 560, etc Diff is the cell where you have your difference, e.g. 50 =INDEX(Diff*(FLOOR(SMALL(Weights;1);Diff)/Diff-1+ROW(OFFSET( A1;0;0;CEILING(LARGE(Weights;1);Diff)/Diff-FLOOR(SMALL(Weights;1);Diff)/Diff+1;1))); MATCH(MAX(FREQUENCY(Weights;Diff*(FLOOR(SMALL(We ights;1);Diff)/Diff+ ROW(OFFSET(A1;0;0;CEILING(LARGE(Weights;1);Dif f)/Diff-FLOOR(SMALL( Weights;1);Diff)/Diff;1))));0);FREQUENCY(Weights;Diff*(FLOOR(SMALL( Weights;1);Diff)/Diff+ROW(OFFSET(A1;0;0;CEILING(LARGE(Weights;1);Di ff)/Diff- FLOOR(SMALL(Weights;1);Diff)/Diff;1))));0))&"-"&INDEX(Diff*(FLOOR(SMALL(Weights;1);Diff)/Diff-1+ ROW(OFFSET(A1;0;0;CEILING(LARGE(Weights;1);Dif f)/Diff-FLOOR(SMALL(Weights;1);Diff)/Diff+1;1)));1+ MATCH(MAX(FREQUENCY(Weights;Diff*(FLOOR(SMALL(We ights;1);Diff)/Diff+ROW( OFFSET(A1;0;0;CEILING(LARGE(Weights;1);Diff)/Diff-FLOOR(SMALL(Weights;1);Diff)/Diff;1))))); FREQUENCY(Weights;Diff*(FLOOR(SMALL(Weights;1);D iff)/Diff+ROW(OFFSET(A1;0;0;E13;1))));0)) Replace all semicolons with comma, if you have comma as parameter separator. Hope this helps Lars-Åke And if you can accept that the formula just gives the lower limit of the interval, e.g. 450 this is a shorter formula: =INDEX(Diff*(FLOOR(SMALL(Weights;1);Diff)/Diff-1+ROW(OFFSET (A1;0;0;CEILING(LARGE(Weights;1);Diff)/Diff-FLOOR(SMALL(Weights;1);Diff)/Diff+1;1))); MATCH(MAX(FREQUENCY(Weights;Diff*(FLOOR(SMALL(Wei ghts;1);Diff)/Diff+ ROW(OFFSET(A1;0;0;CEILING(LARGE(Weights;1);Diff )/Diff-FLOOR(SMALL( Weights;1);Diff)/Diff;1))));0);FREQUENCY(Weights;Diff*(FLOOR(SMALL( Weights;1);Diff)/Diff+ROW(OFFSET(A1;0;0;CEILING(LARGE(Weights;1);Di ff)/Diff- FLOOR(SMALL(Weights;1);Diff)/Diff;1))));0)) If you put this in a cell named LowLimit you can obtain the string you want, e.g. "450-500" by the formula =LowLimit&"-"&LowLimit+Diff Lars-Åke The above formula does only work for positive numbers. If you have negative numbers the following formula for the Lower limit may help: =INDEX(Diff*(FLOOR(SMALL(IF(MIN(Weights)0;0;CEILI NG(-MIN(Weights);Diff))+Weights;1);Diff)/Diff-1+ ROW(OFFSET($A$1;0;0;CEILING(LARGE(IF(MIN(Weights) 0;0;CEILING(-MIN(Weights);Diff))+Weights;1);Diff)/Diff- FLOOR(SMALL(IF(MIN(Weights)0;0;CEILING(-MIN(Weights);Diff))+Weights;1);Diff)/Diff+1;1))); MATCH(MAX(FREQUENCY(IF(MIN(Weights)0;0;CEILING(-MIN(Weights);Diff))+Weights;Diff* (FLOOR(SMALL(IF(MIN(Weights)0;0;CEILING(-MIN(Weights);Diff))+Weights;1);Diff)/Diff+ ROW(OFFSET($A$1;0;0;CEILING(LARGE(IF(MIN(Weights) 0;0;CEILING(-MIN(Weights);Diff))+Weights;1);Diff)/Diff- FLOOR(SMALL(IF(MIN(Weights)0;0;CEILING(-MIN(Weights);Diff))+Weights;1);Diff)/Diff;1))));0); FREQUENCY(IF(MIN(Weights)0;0;CEILING(-MIN(Weights);Diff))+Weights;Diff*(FLOOR(SMALL( IF(MIN(Weights)0;0;CEILING(-MIN(Weights);Diff))+Weights;1);Diff)/Diff+ROW(OFFSET($A$1;0;0;CEILING( LARGE(IF(MIN(Weights)0;0;CEILING(-MIN(Weights);Diff))+Weights;1);Diff)/Diff-FLOOR(SMALL( IF(MIN(Weights)0;0;CEILING(-MIN(Weights);Diff))+Weights;1);Diff)/Diff;1))));0))-IF(MIN(Weights)0;0;CEILING(-MIN(Weights);Diff)) Lars-Åke |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any formula to return the most frequent bin range?
On Sat, 05 Apr 2008 21:52:35 -0400, Ron Rosenfeld
wrote: On Sat, 5 Apr 2008 19:02:37 +0100, hankach wrote: Hi all , In a row i have : A1= 450 B1=560 C1=500 D1=510 E1=445 F1=430 G1= 420 Those values represent several weights of a product prepared in this case 7 times, now that i want to decide what is the most relative weight to use on my list, i need a formula to tell me what is the most frequent bin scored given a difference of 50 grs maximum . The formula should return the following answer : 400-450 which means that the most frequent weight bin is between 400 and 450 grs , now the 50 should be variable so if i change it somehow in the formula to 100 it should return 400-500 which means that the most frequent weight bin falls between 400 and 500 grs. Any smart frequency formula can do that ? i appreciate anyone's help, thank you much. Given the following named ranges: rng refers to: your data range BinSize refers to: a cell containing the bin size Then the lower bound of your first most frequent weight bin is: =INDEX(ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize&":" &CEILING(MAX(rng),BinSize)/BinSize))*BinSize,-1+MATCH(MAX( FREQUENCY(rng,ROW(INDIRECT(FLOOR(MIN(rng),BinSize )/BinSize& ":"&CEILING(MAX(rng),BinSize)/BinSize))*BinSize)),FREQUENCY( rng,ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize&":" &CEILING(MAX(rng),BinSize)/BinSize))*BinSize),0)) and the upper bound is: =INDEX(ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize&":" &CEILING(MAX(rng),BinSize)/BinSize))*BinSize,MATCH(MAX( FREQUENCY(rng,ROW(INDIRECT(FLOOR(MIN(rng),BinSize )/BinSize& ":"&CEILING(MAX(rng),BinSize)/BinSize))*BinSize)),FREQUENCY( rng,ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize&":" &CEILING(MAX(rng),BinSize)/BinSize))*BinSize),0)) If you need it all in one cell, you can concatenate the two formulas: =INDEX(ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize&":" &CEILING(MAX(rng),BinSize)/BinSize))*BinSize,-1+MATCH(MAX( FREQUENCY(rng,ROW(INDIRECT(FLOOR(MIN(rng),BinSize )/BinSize& ":"&CEILING(MAX(rng),BinSize)/BinSize))*BinSize)),FREQUENCY( rng,ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize&":" &CEILING(MAX(rng),BinSize)/BinSize))*BinSize),0))&"-"& INDEX(ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize&":" &CEILING(MAX(rng),BinSize)/BinSize))*BinSize,MATCH(MAX( FREQUENCY(rng,ROW(INDIRECT(FLOOR(MIN(rng),BinSize )/BinSize& ":"&CEILING(MAX(rng),BinSize)/BinSize))*BinSize)),FREQUENCY( rng,ROW(INDIRECT(FLOOR(MIN(rng),BinSize)/BinSize&":" &CEILING(MAX(rng),BinSize)/BinSize))*BinSize),0)) Note that if there is more than one bin that is equally "most frequent", this will return the "first" bin (the one with the lowest value). --ron I think there will be a problem with your formula Ron, if the minimum value in the range is less than BinSize. The FLOOR function then yields 0 and that will annoy the INDIRECT function. Lars-Åke |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any formula to return the most frequent bin range?
On Sun, 06 Apr 2008 11:26:16 GMT, Lars-Åke Aspelin
wrote: I think there will be a problem with your formula Ron, if the minimum value in the range is less than BinSize. The FLOOR function then yields 0 and that will annoy the INDIRECT function. Lars-Åke You're correct. I had not considered that possibility. --ron |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any formula to return the most frequent bin range?
On Sun, 06 Apr 2008 09:04:08 -0400, Ron Rosenfeld
wrote: On Sun, 06 Apr 2008 11:26:16 GMT, Lars-Åke Aspelin wrote: I think there will be a problem with your formula Ron, if the minimum value in the range is less than BinSize. The FLOOR function then yields 0 and that will annoy the INDIRECT function. Lars-Åke You're correct. I had not considered that possibility. --ron Here's a UDF that, I believe will also do the job (and easier for me to follow :-)) As written, it also returns a count of the entries in the bin: ======================================== Option Explicit Function FreqBinRange(Data As Range, BinSize As Long, Optional sFreq As Long = 1) As String Dim BinRange() Dim Freq() Dim I As Long With Application.WorksheetFunction ReDim BinRange(1 To .Ceiling(.Max(Data), BinSize) / BinSize + 1) For I = 1 To UBound(BinRange) BinRange(I) = (I) * BinSize Next I Freq = .Frequency(Data, BinRange) FreqBinRange = BinRange(.Match(.Large(Freq, sFreq), Freq, 0)) - BinSize & _ " - " & BinRange(.Match(.Large(Freq, sFreq), Freq, 0)) FreqBinRange = "(" & .Large(Freq, sFreq) & ") " & FreqBinRange End With End Function ============================ In addition, depending on the OP's requirements, it would be trivial to add information clarifying the bin bounds the way the FREQUENCY function works. (e.g. =0 - <=100; 100 - <=200; etc). --ron |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any formula to return the most frequent bin range?
On Sun, 06 Apr 2008 09:25:48 -0400, Ron Rosenfeld
wrote: On Sun, 06 Apr 2008 09:04:08 -0400, Ron Rosenfeld wrote: On Sun, 06 Apr 2008 11:26:16 GMT, Lars-Åke Aspelin wrote: I think there will be a problem with your formula Ron, if the minimum value in the range is less than BinSize. The FLOOR function then yields 0 and that will annoy the INDIRECT function. Lars-Åke You're correct. I had not considered that possibility. --ron Here's a UDF that, I believe will also do the job (and easier for me to follow :-)) As written, it also returns a count of the entries in the bin: ======================================== Option Explicit Function FreqBinRange(Data As Range, BinSize As Long, Optional sFreq As Long = 1) As String Dim BinRange() Dim Freq() Dim I As Long With Application.WorksheetFunction ReDim BinRange(1 To .Ceiling(.Max(Data), BinSize) / BinSize + 1) For I = 1 To UBound(BinRange) BinRange(I) = (I) * BinSize Next I Freq = .Frequency(Data, BinRange) FreqBinRange = BinRange(.Match(.Large(Freq, sFreq), Freq, 0)) - BinSize & _ " - " & BinRange(.Match(.Large(Freq, sFreq), Freq, 0)) FreqBinRange = "(" & .Large(Freq, sFreq) & ") " & FreqBinRange End With End Function ============================ In addition, depending on the OP's requirements, it would be trivial to add information clarifying the bin bounds the way the FREQUENCY function works. (e.g. =0 - <=100; 100 - <=200; etc). --ron There is a Word Wrap problem in the Function call above. ========================= Option Explicit Function FreqBinRange(Data As Range, BinSize As Long, _ Optional sFreq As Long = 1) As String Dim BinRange() Dim Freq() Dim I As Long With Application.WorksheetFunction ReDim BinRange(1 To .Ceiling(.Max(Data), BinSize) / BinSize + 1) For I = 1 To UBound(BinRange) BinRange(I) = (I) * BinSize Next I Freq = .Frequency(Data, BinRange) FreqBinRange = BinRange(.Match(.Large(Freq, sFreq), Freq, 0)) - BinSize & _ " - " & BinRange(.Match(.Large(Freq, sFreq), Freq, 0)) FreqBinRange = "(" & .Large(Freq, sFreq) & ") " & FreqBinRange End With End Function ===================================== --ron |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any formula to return the most frequent bin range?
i may be missing something but how about for the lower bound:
=MODE(FLOOR(A1:G1,50)) and adding 50 for the upper bound. Obviously replace 50 by a cell reference to make it a variable bin size. However it's not clear from the question whether 450 should be in 450-500 or 400-450, if the latter use CEILING instead of FLOOR to obtain the upper bound and subtract 50 for the lower bound. |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any formula to return the most frequent bin range?
Hi, Lori....Always good to hear from you.
There are several ways to go with the OP's request. We still don't know what the exact requirements are and we haven't received any feedback on any of the proposed approaches. I went with options for the user to define the starting point of the bins and the increment value. (eg Start at: 375....increment by 50's). I suppose we'll have to wait and see what's really needed. -------------------------- Best Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Lori" wrote in message ... i may be missing something but how about for the lower bound: =MODE(FLOOR(A1:G1,50)) and adding 50 for the upper bound. Obviously replace 50 by a cell reference to make it a variable bin size. However it's not clear from the question whether 450 should be in 450-500 or 400-450, if the latter use CEILING instead of FLOOR to obtain the upper bound and subtract 50 for the lower bound. |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any formula to return the most frequent bin range?
On Sun, 6 Apr 2008 10:05:00 -0700, Lori wrote:
i may be missing something but how about for the lower bound: =MODE(FLOOR(A1:G1,50)) and adding 50 for the upper bound. Obviously replace 50 by a cell reference to make it a variable bin size. However it's not clear from the question whether 450 should be in 450-500 or 400-450, if the latter use CEILING instead of FLOOR to obtain the upper bound and subtract 50 for the lower bound. Much simpler! --ron |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any formula to return the most frequent bin range?
Thank you all guys for your contribution, it took me good time to follow up each one's formula and to adjust it , i am no expert but here is what i got : 1) Mr Ron coderre formula worked good but i think it couldnt be applied to many rows with different data each at least i didnt know how to ! 2) Mr.Lars 1st formula returned #Name? the 2nd and the 3rd worked good but i found out that it returns the 1st bin even though if the 2nd bin includes more nbrs than the 1st !, while the 3rd formula was too long and couldnt be edited in the cell . 3) Mr. Ron Rosenfeld worked good so far until i read the comment , i think the weight in my case wouldnt be less than the bin but generally speaking it could be a problem . Milion thanks to all of you . -- hankach |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Link to a named range in another worksheet that is sorted frequent | Excel Worksheet Functions | |||
| Formula to return the reference of a range of cells | Excel Discussion (Misc queries) | |||
| formula to return a $ amount for a range of % | New Users to Excel | |||
| Formula - Analyse range, return unique value | Excel Worksheet Functions | |||
| return most frequent objects/numbers in large worksheet? | Excel Worksheet Functions | |||
Linear Mode
