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