How to Determine Histogram Bin Intervals

Histograms Are a Valuable Tool But They Can be Hard to Create


If you are creating a histogram manually or using Excel,
you will need to calculate:
the number of bins bin width bin intervals.

frustrated man
The numerous steps are described below.

...or you can use QI Macros add-in for Excel.
Download a 30 day trial and
you'll have a histogram drawn in seconds.

histogram example


Here's How to Calculate the Number of Bins and the Bin Width for a Histogram

  1. Count the number of data points.
  2. Calculate the number of bins by taking the square root of the number of data points and round up.
  3. Calculate the bin width by dividing the specification tolerance or range (USL-LSL or Max-Min value) by the # of bins.

Let's Use an Example to Better Understand Bin and Bin Width Calculations

Description
Example of Calculation In Excel

Step 1: Count the Number of Data Points

Let's say we have 50 data points.

If your data is in Excel, use Excel's count function to determine the number of data points.

count function example in Excel

Step 2: Calculate the # of Bins, then round up

# of bins = square root of the # of data points

the square root of 50 = 7.071, round up to 8

In Excel, add the SQRT function to your calculation then add the Roundup function.

square root function in Excel

roundup function

Step 3: Calculate the Bin Width

Bin width = Spec Tolerance or Range ÷ number of bins

  • Spec Tolerance = USL-LSL
  • Range = Max - Min

If we use the range for this example:

Bin width = (Max-Min) ÷ Number of bins

Bin width = (346 - 187) ÷ 8 = 19.875

 

maximum function in Excel

minimum-function in Excel

bin width calculation in Excel

How to Determine Bin Intervals to Create a Histogram in Excel

Bin intervals will need to span enough distance to include the upper and lower spec limits and the min and max values.

Using the data in the previous example:

  • select a beginning point that is lower than or equal to both the lower spec limit and the min value
  • calculate bin intervals in Excel by taking the beginning value + the bin width, + the bin width, etc.
  • round the calculated values if desired
  • select an ending point that is higher than or equal to both the upper spec limit and the max value

histogram bin intervals calculation

 

Note: Upper and lower specification limits are determined by the customer of the process. If you don't have spec limits, use the maximum and minimum values.

 

Juran's Guidelines for Number of Bars

Juran's Quality Control Handbook provides these guidelines for the number of bars and states that they are not "rigid" and should be adjusted when necessary.

Number of Data Points Number of Bars   Number of Data Points Number of Bars
20-50 6   201-500 9
51-100 7   501-1000 10
101-200 8   1000+ 11-20

Why Choose QI Macros to Perform Capability Analysis and Draw Histograms?

affordable

Affordable

  • Only $249 USD - less with quantity discounts
  • No annual fees
  • Free Technical Support

easy to use

Easy to Use

  • Works Right in Excel
  • Calculates Cp Cpk Pp Ppk
  • Accurate Results Without Worry

proven and trusted

Proven and Trusted

  • 100,000 Users in 80 Countries
  • Celebrating 20th Anniversary
  • Five Star CNET Rating - Virus Free