 # Return Most Frequent Bin Of Numbers

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

• Re: Return Most Frequent Bin Of Numbers

This is a bit crude but place this function in a standard module then in the cell you want the highest frequncey range displayed, enter this formula. The first argument is the range of numbers. The second argument is the Range Span

=freg(A1:G1,50)

The result will show the lower and upper boundries of the range seperated by a period(.). Its possible to have more than one range of the higst frequncy. This function only gives the first (lowest numbers) range found.

[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]This function could be easily converted to a sub procedure that when called by a button would display the upper and lower bounds of the most frequented range along with the number of elements in that range.

[FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
Tip: To avoid chasing code always use Option Explicit.

• Re: Return Most Frequent Bin Of Numbers

Thank you for the code.I am not so familiar with VBA but i just pasted the code in the sheet code page as well as in a new inserted module, applied the formula and returned in J1 420.45
i think it's not the right answer should it be 420.450 instead? or what's the right answer for an increment of 50 i guess 400.450 or it just read the lowest value available?

Thank you much !

• Re: Return Most Frequent Bin Of Numbers

Its dropping the trailing zero. Cahnge the format to a number with three decimal places

[FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
Tip: To avoid chasing code always use Option Explicit.

• Re: Return Most Frequent Bin Of Numbers

Thank you that was solved by changing the format.

many thanks !

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!