Histogram is a type of graphical representation in excel and there are various methods to make one, but instead of using the analysis toolpak or from the pivot table we can also make a histogram from formulas and the formulas used to make a histogram are FREQUENCY and Countifs formulas together.
What is Histogram Formula?
The formula for histogram basically revolves around the area of the bars and it is very simple and it is calculated by the summation of the product of the frequency density of each class interval and the corresponding class interval’s width. The area of histogram formula is mathematically represented as,
Explanation of the Histogram Formula
The formula for calculation of the area of the histogram can be derived by using the following simple seven steps:
Step 1: Firstly, it is to be decided on how the process should be measured and what data should be collected. Once decided, the data is gathered and presented in a tabular form such as a spreadsheet.
Step 2: Now, count the number of data points gathered.
Step 3: Next, determine the range of the sample which is the difference between the maximum and minimum values in the data sample.
Range = Maximum value – Minimum value
Step 4: Next, determine the number of class intervals which can be based on either of the following two methods,
- As a thumb rule, use 10 as the number of intervals or
- The number of intervals can be calculated by the square root of the number of data points which is then rounded off to the nearest whole number.
Number of intervals =
Step 5: Now, determine the width of the interval class by dividing the range of the data sample by the number of intervals.
Class width = Range / Number of intervals
Step 6: Next, develop a table or spreadsheet with frequencies for each interval. Then, derive the frequency density for each interval by dividing the frequency by the corresponding class width.
Step 7: Finally, the area for the histogram equation is calculated by adding the product of all the frequency density and their corresponding class width.
Examples of Histogram Formula (with Excel Template)
Let’s see some simple to advanced example to understand the calculation of the histogram equation better.
Histogram Formula – Example #1
Let us consider the table below which shows the weights of children in a class.
From the above table, the following can be calculated
- Class width of the first interval = 35 – 30 = 5
- Class width of the second interval = 45 – 35 = 10
- Class width of the third interval = 50 – 45 = 5
- Class width of the fourth interval = 55 – 50 = 5
- Class width of the fifth interval = 65 – 55 = 10
- Frequency density of the first interval = 2 / 5 = 0.4
- Frequency density of the second interval = 7 / 10 = 0.7
- Frequency density of the third interval = 21 / 5 = 4.2
- Frequency density of the fourth interval = 15 / 5 = 3.0
- Frequency density of the fifth interval = 2 / 10 = 0.2
For the calculation of Histogram formula first, we will need to calculate class width and frequency density as shown above.
Hence, Area of histogram = 0.4 * 5 + 0.7 * 10 + 4.2 * 5 + 3.0 * 5 + 0.2 * 10
So, Area of Histogram will be –
- Therefore, the Area of histogram = 47 children
Graphical representation of a weight of children is shown below,
Relevance and Uses
The concept of a histogram equation is very useful as it is used to portray a set of data. Although a histogram looks quite similar to a bar chart, the end use of a histogram is very different from that of a bar chart. A histogram is useful in displaying a large amount of data in a more comprehensible way which is easy to visualize. A histogram captures the frequency density of each class interval. The median and the distribution of data can be determined from a histogram. Also, the skewness of the distribution can be determined, as if the bars on the left or the right are higher then it indicates that the data is skewed, or otherwise the data is symmetrical.
A histogram primarily finds its application in the case of large scale exercise like a nationwide census which may be conducted in every ten years. In such cases, the data is compiled and presented in a histogram so that it can be easily studied. Also, in the cases of surveys where a histogram is created so that anyone who is able to interpret the histogram can use the data later for further studies or analysis.
This has been a guide to Histogram Formula. Here we discuss the equation to calculate the area of histogram along with practical examples and downloadable excel templates. You may learn more about Fixed Income from the following articles –