How to Calculate Class Width in Excel

To organize data into a frequency table, you must first calculate the data’s class width.

The class width is the difference between the upper and lower boundaries of intervals or bins in a frequency distribution. For instance, the frequency distribution below has a class width of 5:

The class width in cell A2 is 5 (6-1). The class width in cell A3 is 5 (12-7), and so on.

The formula for calculating class width is (Max-Min)/n where:

Max is a dataset’s maximum value.
Min is a dataset’s minimum value.
n is the number of intervals you want to divide the data into.

You can follow the example below to calculate the class width of your dataset and use it to create a frequency distribution table and a histogram.

Table of Contents

Calculating Class Width in Excel

Suppose you have the dataset below of the body weight of people who participated in a survey.

You can use the steps below to calculate the dataset’s class width:

Step #1: Find the Dataset’s Maximum and Minimum Values

To find the dataset’s maximum value, use the formula below:

=MAX(A2:A16)

In this case, the maximum value is 190.

To find the dataset’s minimum value, enter the formula below in a cell:

=MIN(A2:A16)

In this example, the minimum value is 132.

Step #2: Determine the Optimal Number of Intervals to Divide the Data into

In determining class width, it is important to remember that too many classes obscure trends and patterns, making it challenging to interpret the data, and too few classes oversimplify the data.

One method to determine an optimal number of classes to divide data into is Sturge’s Rule below:

n = 1 + 3.3 * log10(N)

N represents the number of data points.

Note: This rule is called Sturge’s Rule because Herbert A. Sturges formulated it.

You can wrap the Sturge’s formula in a CEILING.MATH function to round the result to the nearest integer.

The CEILING.MATH function is only available in Excel 365 and 2021, so if you have an older version of Excel, use the CEILING function.

You can use the formula below to calculate the optimal number of classes to divide the example dataset into:

= CEILING.MATH(1 + 3.3 *LOG10(COUNT(A2:A16)),1)

CEILING MATH function to get class width classes

According to Sturge’s Rule, the optimal number of classes to divide the example data into is 5.

From Step #1 and Step #2, you have the necessary values to compute the class width of the dataset.

Step #3: Compute the Class Width

You can use the formula below to compute the class width:

=CEILING.MATH((D2-D3)/D4,1)

Note: You wrap the class width formula in the CEILING.MATH function to round the result to the nearest integer.

CEILING MATH function to get class width

The class width for the example dataset is 12.

After calculating the class width, you can create a frequency distribution table and a histogram as explained in the section below.

Also read: Class Width Calculator

How to Generate a Frequency Distribution Table and a Histogram

Suppose you want to use the dataset below to create a frequency distribution table with a class width of 12 that was arrived at in the previous section.

Here’s how to create the frequency distribution table:

Step #1: Set the Class Boundaries

The lowest value in our example dataset is 132 so when we add the class width of 12 to it we get 144 as the upper boundary of the first class. The boundaries for the second class are 145-157, and so on.

The five classes are as follows:

132 – 144 (Covering 132 to144 pounds)
145 – 157 (Covering 145 to 157 pounds)
158 – 170 (Covering 158 to 170 pounds)
171 – 183 (Covering 171 to 183 pounds)
184 – 196 (Covering 184 to 196 pounds)

Step #2: Calculate the Frequency for Each Class

You can use the FREQUENCY function to compute how often a value occurs within a range of values. Its syntax is:

FREQUENCY (data_array, bins_array)

The ‘data_array’ argument represents the range of your dataset and the ‘bins_array’ argument represents the range of the upper boundaries of the classes of the dataset.

Do the following to calculate the frequency for each class:

Enter the upper boundary values of the five classes in column B.

Enter the array formula below in cell C2 and press Enter:

=FREQUENCY(A2:A16,B2:B6)

The formula returns the frequencies of the classes. You can ignore the extra frequency of zero returned by the formula.

Step #3: Organize Your Data Into Two Columns

Organize your data in two columns, one column containing the classes and the other the frequencies, as shown below:

To insert a histogram, select the frequency table including the column headers, open the Insert tab, click the ‘Recommended Charts’ button on the Charts group, and select the ‘Clustered Column.’