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

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 are:
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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

On Sat, 5 Apr 2008 19:02:37 +0100, hankach
<(E-Mail Removed)> 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(Weights;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);Diff)/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(Weights;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);Diff)/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

On Sun, 06 Apr 2008 00:54:51 GMT, Lars-Åke Aspelin
<(E-Mail Removed)> wrote:

>On Sat, 5 Apr 2008 19:02:37 +0100, hankach
><(E-Mail Removed)> 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(Weights;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);Diff)/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(Weights;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);Diff)/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(Weights;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);Diff)/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

On Sat, 5 Apr 2008 19:02:37 +0100, hankach
<(E-Mail Removed)> 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

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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 are:
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>
>> 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:u$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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 are:
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>>
>>> 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
>>
>>
>>
>
>
>
>

On Sun, 06 Apr 2008 01:18:47 GMT, Lars-Åke Aspelin
<(E-Mail Removed)> wrote:

>On Sun, 06 Apr 2008 00:54:51 GMT, Lars-Åke Aspelin
><(E-Mail Removed)> wrote:
>
>>On Sat, 5 Apr 2008 19:02:37 +0100, hankach
>><(E-Mail Removed)> 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(Weights;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);Diff)/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(Weights;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);Diff)/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(Weights;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);Diff)/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;CEILING(-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

On Sat, 05 Apr 2008 21:52:35 -0400, Ron Rosenfeld
<(E-Mail Removed)> wrote:

>On Sat, 5 Apr 2008 19:02:37 +0100, hankach
><(E-Mail Removed)> 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

On Sun, 06 Apr 2008 11:26:16 GMT, Lars-Åke Aspelin <(E-Mail Removed)>
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