Any formula to return the most frequent bin range?

H

hankach

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.
 
R

Ron Coderre

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)
 
L

Lars-Åke Aspelin

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.
Click to expand...

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
 
L

Lars-Åke Aspelin

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
Click to expand...

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
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
R

Ron Coderre

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)
 
R

Ron Coderre

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)
 
L

Lars-Åke Aspelin

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
Click to expand...


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
 
L

Lars-Åke Aspelin

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
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

You're correct. I had not considered that possibility.

--ron
 
R

Ron Rosenfeld

You're correct. I had not considered that possibility.

--ron
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
L

Lori

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.
 
R

Ron Coderre

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)
 
R

Ron Rosenfeld

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.
Click to expand...

Much simpler!
--ron
 
H

hankach

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 .
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please help me find a formula to show the 2nd most frequent 3
Formula to Count and Return Most common Value in a Dynamic Named Range 1
Compare and sum 1
Finding the corresponding value in a range? 4
Locating values in a range and bringing up the corresponding price 3
Possible? 1
Formula to Count and Return Most common Value in a Dynamic Named Range 1
I need to look for a number and return the column heading. 6

Top