Excel offers various kinds of charts, especially histograms. In the histogram chart, there is a fundamental component named Bin Range to define the charts in Excel. Basically, Bin Range is defined as intervals that are obtained by dividing the entire dataset value by several spaces. More importantly, it is essential to calculate the Bin Range in Excel for making a histogram. If you don’t know how to estimate it, don’t get worried. In this article, we are going to show you the steps to calculate the Bin Range in Excel. So, let’s get started.
What Is Bin Range?
The distribution of numerical data is roughly depicted by a histogram. The first stage in creating a histogram is to bin the range of values. This Bin means dividing the dataset into a series of intervals. Then we count the number of values that fall into each gap. We can define the bins as discrete intervals that don’t overlap. The bins (intervals) must be close together and frequently be the same size.
For instance, you have this dataset: 1, 2, 2, 3, 3,6, 2, 10, 3. Here, the maximum and minimum numbers of the dataset are 10 and 1, respectively. So, if you want to divide this dataset range into two intervals, each interval will be 5 (rounded value of (10-1)/2 or 4.5) units long. You see that from 1 to 5, there are 7 data (1, 2, 2, 2, 3, 3, 3) and from 6 to 10, there are two data (6 and 10).
How to Calculate Bin Range in Excel: 4 Easy Methods
There are several ways to calculate the Bin Range. We tried to cover all the methods for doing it. We have taken a dataset of No. of Students under different Departments to calculate the Bin Range.
Not to mention, we have used the Microsoft 365 version. You can use any other version at your convenience.
1. Using MAX and MIN Functions
To calculate the Bin Range, you have to calculate the Bins first. It means finding the upper and lower values of your dataset and then making the intervals according to your preferences. There are quite handy methods for finding out the highest and lowest values of a certain dataset. For example, using the MAX and MIN functions is one of them. Here, we have demonstrated to you the steps to be followed to use it.
In the beginning, go to cell E5 and insert the formula
The MIN(C5:C15) will find out the minimum value from the C5:C15 range.
After that, move to cell E11 and input the formula
It will find out the maximum value of the C5:C15 range.
Now, go to cell E6 and enter the formula.
Here, we have taken 10 as our bin interval.
Eventually, press ENTER and drag it down to cell E10. You will then get the Bins just like in the image below.
In this step, we will calculate the range of our bins. To do this operation, select cell F5 and insert the formula.
Sequentially, it will create a range of 60-69 after pressing ENTER.
Afterward, drag down the Fill Handle tool for the other cells.
Consequently, you will get your desired Bin Range just like the image below. But in the last cell, the formula will be-
That means you don’t need to subtract 1 from the last bin limit.
2. Utilizing LARGE and SMALL Functions
You can also find the upper and lower values by utilizing the LARGE and SMALL functions, respectively. These functions take a data array and a value to find out the kth largest or smallest value of a dataset. Follow the steps to clear the confusion.
📌 Steps:
- Firstly, move to cell E5 and enter the formula.
In the above formula, the SMALL function searches for the 1st smallest value in the range of C5:C15 because we entered the logical argument as 1.
- Then, go to cell E11 and write up the formula.
It will search for the first largest value of C5:C15.
- Thus, you will get the following result.
- Afterward, follow Step 3 as we mentioned earlier to get the bin interval.
Read More: How to Calculate Range for Grouped Data in Excel
3. Using the FREQUENCY Function
In a bin table of a set of data or datasets, the Excel FREQUENCY function calculates how frequently numerical values occur within the ranges you provide. It will compute a frequency distribution and provide it back. This function returns the distribution in the form of a vertical array of values representing a count per bin. Follow the steps below to use this function.
📌 Steps:
- In the very beginning, make a Bins and Bin Range following the above method of using MAX and MIN functions.
- Hence after, move to cell G5 and enter the formula.
This function finds the numerical values under the range of E5:E11, makes a count, and displays them.
Lastly, your result will look like the image below.
4. Employing PivotTable
You can get and calculate the Bin Range by creating a PivotTable. It will automatically create the range of your dataset. Go through the steps to do it.
📌 Steps:
- Initially, select your entire data range >> go to Insert tab >> choose Pivot Table.
- PivotTable from table or range will appear and make the Table/Range as Sheet1!$C$5:$C$15 >> check the Existing Worksheet box >> create the Location in Sheet1!$E$4. Then press OK.
- A PivotTable Fields sidebar will show. Drag down the No. of Students to Rows area.
- Then, click on the Sum of No. of Students drop-down of the ∑Values and select Value Field Settings.
- A Value Field Settings dialog box will pop out. From there, select Count and hit OK.
- Eventually, a pivot table will appear in the sheet, and right-click on any cell of the table and pick Group.
- A Grouping window will appear. Uncheck the Starting at and Ending at boxes and select 5 in the By box. It will take the intervals as 5.
- Finally, press OK.
Lastly, you will get the Bin Range and Bin Counts just like in the below image.
How to Calculate Conditional Bin Range
Sometimes, you may need to calculate the upper and lower ranges of specific criteria. For example, in our dataset, we want to find the minimum value starting from 80 and the maximum value the same as before. For a conditional Bin Range like this, we have discussed two methods for making your conception at ease.
1. Employing MAX and MINIFS Functions
We can use the MAX and MINIFS functions in Excel 2019 and Microsoft 365. The MINIFS function is not available in the other versions of Excel. The MINIFS function is a combination of MIN and IF functions. See the steps for better visualization.
📌 Steps:
- Firstly, go to cell E5 to enter the formula.
The above formula checks for the minimum value >=80 in the range of C5:C15.
- Then, move to cell E13 and write the formula.
It will find out the maximum value from C5:C15.
- Consequently, follow Step 3 and Step 4 stated above to create the Bin Range and the final image will look like the image below.
2. Applying MAX, MIN, and IF Functions
You can apply the MAX, MIN, and IF functions combinedly to calculate the conditional Bin Range. The IF function finds out the values under special criteria, and then the MAX and MIN functions will find out the largest and smallest values, respectively.
📌 Steps:
- First of all, go to cell E5 and input the formula.
Formula Breakdown:
IF(C5:C15>=80, C5:C15)→ This function will look for values greater than or equal to 80 and return the values.
MIN(IF(C5:C15>=80, C5:C15))→ It will give you the minimum value by taking the result of the IF function.
How to Create Histogram Bin Range in Excel
As we said earlier, in the histogram chart the Bin Range holds a very significant role in specifying the intervals. You can plot a histogram chart by the Bin Range. Here we show you the step-by-step way to plot a histogram chart.
📌 Steps:
- Firstly, select the entire data range and go to the Insert tab >> pick Insert Statistic Chart >> choose Histogram.
- A Histogram Chart will appear.
- Now, you need to format the chart.
- Double-click on the X-axis value.
- A Format Axis sidebar will open and check the Overflow bin and Underflow bin and make the Bin Width 10.
- Then, move to the Series Options of that Format Data Series. Pick up a suitable color.
Eventually, after creating the Axis and Title, your chart will look like the image below.
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
Conclusion
That’s all about today’s session. And these are some easy methods to calculate bin range in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Thanks for your patience in reading this article.
Related Articles
- Calculate Moving Range in Excel
- How to Calculate Time Range in Excel
- How to Calculate Interquartile Range in Excel
- Calculate Percentage Range in Excel
<< Go Back to Range Formula in Excel | Excel Range | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
