How to Calculate Class Width in Excel

This guide will discuss how to calculate class width in Excel using the MAX function and the MIN function.

The rules for using the MAX function in Excel are the following:

• The MAX function ignores empty or blank cells, logical values such as TRUE or FALSE, and text values. However, the function will evaluate numbers as text and logical values supplied directly as arguments. 
• The function can also support up to 255 total number arguments. Additionally, we can input arguments as numbers, names, arrays, or cell references.
• If the selected or inputted arguments contain no numbers, the function will return 0.

Excel is a popular tool used for many different situations and purposes. Since it has several built-in mathematical functions, it is an excellent tool for statistical calculations. Specifically, it makes it easy to create a frequency table and identify class widths. 

So a class width refers to the distance between class boundaries. And it can also refer to the difference between the upper and lower boundaries of any category or class. Furthermore, class width is often used when creating frequency tables.

Usually, we can get the class width by dividing the total range of numbers in the data set by the number of intervals we want to create for our frequency table. However, we can easily perform this task of calculating the class width in Excel with the help of the MAX function and the MIN function.

Since it is really important to keep the class width consistent across all the intervals when making a frequency table, we can avoid mistakes or inconsistencies by using the functions in Excel. And this will ensure that our data is represented accurately in the distribution table. 

Let’s take a sample scenario wherein we need to calculate class width in Excel.

Suppose you are a teacher who has a list of the student’s scores on a test. And you want to create a frequency distribution table to display the class or intervals of scores there is in a class. But, you first need to calculate the class width to know how to divide the intervals in the distribution table.

So you calculate the class width using the MAX and the MIN functions. Afterward, you made the frequency distribution table according to the result of the class width.

Before we continue how to calculate class width in Excel, let’s first learn how to write the functions we will be using in Excel.

The Anatomy of the MAX Function

The syntax or the way we write the MAX function is as follows:

=MAX(number1, [number2])

Let’s take apart this formula and understand what each term means:

• = the equal sign is how we activate any function in Excel.
• MAX() refers to our MAX function. And this function is used to return the largest value in a set of values. Additionally, the function ignores logical values and text values.
• number1 is a required argument. So this refers to 1 to 255 numbers, empty cells, logical values, or text numbers for which we want to get the maximum value.
• number2 is an optional argument. And it refers to the same 1 to 255 numbers, empty cells, logical values, or text numbers for which we want to get the maximum value. 

The Anatomy of the MIN Function

The syntax or the way we write the MIN function is as follows:

=MIN(number1, [number2])

Let’s take apart this formula and understand what each term means:

• = the equal sign is how we begin any function in Excel.
• MIN() is our MIN function. And this function is used to return the smallest number in a set of values. Similarly, it ignores logical values and text values.
• number1 is a required argument. So it refers to 1 to 255 numbers which we want to extract the minimum value from.
• number2 is an optional argument. And this is an additional argument that still refers to 1 to 255 numbers which we want to get the minimum value from. 

Great! Now we can move on and dive into a real example of calculating class width in Excel.

A Real Example of Calculating Class Width in Excel

Let’s say we have a data set containing a list of test scores from students. And we want to calculate the class width of the test scores to create a frequency distribution table. So our initial data set would look like this:

Since class width refers to the difference between the lower and upper boundaries of a class, we need it to create a frequency distribution table to know how to group the raw data. 

If we already have a frequency distribution table and we simply want to calculate the class width, we can easily do that by subtracting the first-class value. For instance, the first class in a frequency distribution table is 1-10. To get the class width of this specific table, we simply subtract 10 from 1.

Since the answer is 9, this is the class width of the frequency distribution table. However, sometimes we only have the raw values from a data set. In this case, we will use the MAX and MIN functions to calculate the class width. 

So our formula would be class width = (max - min) / n wherein the max refers to the maximum value in the data set, min is the minimum value in the data set, and n is the number of classes we want in our frequency distribution table.

Let’s say we have the raw values of the test scores, and we want to have 4 classes in our data set. So the formula will get the minimum test score and subtract it from the maximum test score. After, it will be divided by 4, which is the number of classes we want. 

Then, we can proceed to create our frequency distribution table based on the results. 

So our final data set would look like this:

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now we can explain the process of how to calculate class width in Excel.

How to Calculate Class Width in Excel

In this section, we will discuss the step-by-step process of how to calculate class width in Excel using the MAX function and the MIN function. Furthermore, each step has detailed instructions and pictures to guide you along the way.

To apply this method to your work, simply follow the steps below.

1. Firstly, we must decide how many classes we want to have in our frequency distribution table. In this case, we want to have 4 classes. Next, we will type in the formula “=(MAX(B2:B21)-MIN(B2:B21)) / 4”. Lastly, we will press the Enter key to return the result.

2. If the result returns with a decimal point, we can simply round up to the nearest integer for convenience. For example, the result is 5.75. So we will round up to 6.

3. Secondly, we can now create our frequency distribution table using the calculated class width. So we will input the class by 4 and count the frequency. And tada! We have successfully calculated the class width in Excel.

And that’s pretty much it! We successfully explained how to calculate the class width in Excel using the MAX and MIN functions. Now you can apply this method to your work whenever you need to get the class width of a data set.

Are you interested in learning more about what Excel can do? You can now use the MAX function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe