Histograms with dynamic Bins

Hello,

I have scratched my head for last few days trying to figure this out but I can't. I think I need your help.

I want to create dynamic histograms with automatically calculated number of bins, bin ranges... for some inspection measurements.
I have problems because:

1. Measurements have different number of decimal places.
2. Some dimensions have more number of measurements than the others.

A user would click on the drop-down list and then choose the dimension that they wants to see its histogram. Then, the spreadsheet will calculate itself and put up a histogram.

I have searched on the 'Net and this forum. Please give me some help if you will. Any hint would be appreciated. No VBA please. Sample sheet attached. Feel free to change any formula if you need to.

Thank you all.

why don't you use excel analysis tool pack add-in for making histogram? it will save your tedious manual work.
as for your decimal places problem, can't you use the =trunc() function to remove it?
different size data sets are not a problem if you are using excel in-built add in for histograming.

Thanks for your suggestion. I'm aware of the analysis toolpak but I can not use it for my spreadsheet. This is just a part of a bigger automatic spreadsheet.

Thank you.

Originally Posted by MrShorty

I'm not sure I understand the problem. You are already calculating "dynamic" bins, and your FREQUENCY() function looks like it working just fine. The histogram in your sample looks correct, except for the N/A's for the top two bins. It is not clear to me if this is the error you are seeing or if there is something else. What exactly seems to be the problem with the sample spreadsheet?

MrShortly,

In my code, I hard code the increment between bins of .0001" The problem is not all data is fourth decimal places.

And I was wondering if there is any other has better idea to calculate bins besides using standard deviation.

Thanks for your reply

1.JPG