Histogram Formula in Google Sheets

What Is Histogram Formula in Google Sheets?

Histograms are one of the most frequently used graphs that show frequency distributions. A frequency distribution is used to show how often each value in a set of data occurs. They are similar to bar charts and are used to display quantitative continuous data. In a histogram, data is grouped within specific intervals and shows how many values fall within these intervals.

Histograms are basically used under the following conditions:

• With numerical data
• Analyzing customer requirements
• For determining whether a process’s output is distributed normally
• For Determining whether the outputs of two or more processes are different
• The distribution of data can be understood at a glance

In the example below, we have the histogram chart showing the daily traffic of different pages of a website. Here, each bar plotted with the numerical values is called a bin. In Google Sheets, the histogram can also be created using the FREQUENCY and COUNTIFS functions. When you use this formula, you can get a flexible approach where the histogram gets updated automatically with a change in the data.

Formula

The frequency density (D) of a class interval can be represented by the frequency (F) divided by the class width. (W). It can be expressed as:

A histogram represents the frequency using rectangular bars. Thus, to form a histogram, we need to find the frequency density for each class interval. Mathematically, a histogram can be represented by the following formula.

D=WF​.

These values are used as the heights of the bars in the vertical axis (Y), while the X-axis is labeled as the data variable with a continuous scale. Now, let us look at how to use the HISTOGRAM formula in Google Sheets with FREQUENCY and COUNTIFS.            

To effectively use the HISTOGRAM formula, follow the below steps.

1. First, sort and organize the data range for the histogram.
2. Next, select the appropriate Data Range for the histogram in Google Sheets.
3. Now, the critical part comes up: choose the right bin size. 
4. Choosing the Right Bin Size: Determining the Number of Intervals in Your Histogram
5. One of the critical decisions in creating a histogram is choosing the right bin size, which determines the number of intervals in your histogram. An accurate bin size ensures that the histogram correctly represents the data distribution and provides meaningful insights.

How to Use Histogram Formula in Google Sheets?

Let us look at how to create a histogram in Google sheets with formula. Below is a table that contains information on the number of refrigerator units sold by a store. Let us use the histogram formula to check the sales data.

Step 1: To calculate the histogram, set the frequency bins. Looking at the data, it would be best to set up frequency bins from 0 through 50 with intervals of 5. It is because a suitable bin size ensures that your histogram accurately represents how the data is distributed and provides meaningful insights.

Step 2: Let us enter the same in the range C2 to C11.

Step 3: Now, let us determine the frequency. This function returns the number of values that fall within the specific bin ranges provided while ignoring blank cells and text. Its syntax is  =FREQUENCY(data, classes).

• Data – The array or range containing the values to be counted
• Classes – The array or range containing the set of classes

Apply the formula =FREQUENCY(B2:B11, C2:C9) in cell D2.

Step 4: Press Enter. You get the values that are greater than the previous bin and less than or equal to the current bin. Cell D10 returns any values greater than the highest bin, which is 35.

Step 5: Now, to draw a histogram, go to Inserts-> Charts, and under Setup->Chart Type, click on the drop-down and choose the Histogram chart.

You get a histogram as shown below.

Examples

Let us look at some examples of how to create a histogram.

Example #1

Below is a dataset containing numbers ranging between 1 and 6. Let us look at how to make a histogram formula in Google Sheets using another formula, COUNTIFS.

Step 1: As seen in the earlier examples, the first step here is selecting the bin values. Since this is a straightforward dataset containing numbers 1-6, we have selected the bin as shown below in Column B.

Step 2: Now, let us determine the frequency values using the COUNTIFS function.

The formula that you enter in cell C2 for the top bin is as follows:

=COUNTIFS($A$2:$A$17,”<=”&$B2).

Here, the first argument is the range of values, and from this, we are counting how many values in it are less than the smallest bin, i.e., 0-1. (inclusive of 1). Input the formula and press Enter. You will get the number of 1’s in the dataset.

The formula for the last bin, i.e., beyond 5 in cell C7, is as below.

=COUNTIFS($A$2:$A$17,”>”&$B6).

It returns all the values in cells A2 to A17 that are greater than the value in cell B6, which is 5. Input the formula in cell C7 and press Enter.

Step 3: Let us calculate the values for the bins in between the first and the last. Enter the following formula in cell C3.

=COUNTIFS($A$2:$A$17,”>”&$B2,$A$2:$A$17,”<=”&$B3).

Explanation:

It counts all the values in the dataset that are greater than the bin in the above row(1 in this case) and less than or equal to the bin in the same row(2).

Enter the formula in C3 and press the Enter key.

Step 4: Now, drag the formula =COUNTIFS($A$2:$A$17,”>”&$B2,$A$2:$A$17,”<=”&$B3) to the remaining cells.

You can see that the only two values which are not referenced will change and thus the lower and upper bin values keep changing.

=COUNTIFS($A$2:$A$17,”>”&$B2,$A$2:$A$17,”<=”&$B3)

Now, you get all the values required for the histogram. You can draw a chart, as mentioned in the previous example, using Google sheets histogram chart.

Example #2

Let us try to draw a double histogram. The marks of students in two subjects are given below. Let us try to sort it out in a histogram.

Step 1: Let us set the bin values. Since it is graded, let us set the bin in intervals of 10, as shown below in Column D.

Step 2: Let us use the frequency histogram formula to find the values for Maths. Enter the following in cell E2.

The first argument is the range containing the Maths dataset, and the second is in the bins considered.

Step 3: Press Enter. You get the frequency values for Maths.

Step 4: To get the same values for science, enter the following formula in cell F2.

=FREQUENCY(C2:C11,D2:D11)

Step 5: Press Enter. You get the values for science.

Step 6: Let us try to plot a histogram chart for both values. Select both the ranges (Columns E and F) as well as Column A. Go to the “Insert” menu and select “Chart.”

Step 7: In the chart editor, which appears to your right, click on “Chart type.” Select the chart title “Histogram.” You will get the double histogram chart.

Example #3

 Now that you have learned to get the values by applying the Google Sheets Histogram formula, let us look at how to customize the Histogram chart you have obtained using Google Sheets. For this, we can take the chart obtained in the previous example.

Step 1: You can change the color of a particular histogram by clicking on any of the bars. You immediately get the Chart editor of the right side. Under Customize-> Series, you can choose the “Fill Color” option and change the color.

Step 2: Thus, the histogram color is changed.

Step 3: Next, you can change the horizontal and vertical axis by clicking on them. In the Chart editor option, which appears on the right, you can enter the Maximum and Minimum values. Here, we have changed it to 1 and 4, respectively. Observe the changes in the image below.

Thus, you can change any features of the histogram chart easily using Google sheets.

Relevance and Uses

1. Histograms are an indispensable tool for analyzing and visualizing data, especially for data-driven processes. Google Sheets is a convenient platform for creating histograms, as it has many powerful features.
2. Histograms are helpful when dealing with large datasets or when you compare multiple datasets.
3. Histograms explore the distribution of numerical data and its patterns in data analysis. 
4. Using histograms, you can identify outliers and gain insights into the underlying trends. 
5. Histograms allow us to identify patterns and trends, providing valuable insights into the underlying data. With Google Sheets, you can create multiple histograms, enabling comparative analysis and gaining deeper insights.

Important Things to Note

1. Histograms allow us to identify patterns, trends, and outliers, providing valuable insights into the underlying data. A histogram can show the shape of a frequency distribution of a data set.
2. Analyzing and setting the bin is an important skill. There are several rules of thumb for selecting the bin size, such as the square root rule, the Scott rule, etc. 
3. As FREQUENCY is an array function, to change the number of bins, you will have to delete the existing formula, add new bins, and then re-enter the new formula.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the Histogram Formula in Google Sheets, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is Histogram Formula in Google Sheets. We explain how to use the Histogram Formula in Google Sheets with examples and points to remember. You can learn more from the following articles. –

• Weeknum In Google Sheets
• MODE Google Sheets Function
• Goal Seek In Google Sheets