Maruf Islam
The Relative Frequency Distribution in Excel is a phenomenon where any numerical values symbolize the density of the occurred values within a range. In this article, we demonstrate how to calculate and illustrate Relative Frequency Distribution in Excel.
Let’s say we have five typical products and their sales Quantity in a dataset. And we want to calculate the relative frequencies of the products then illustrate the distribution using a chart.
Download Excel Workbook
What is Relative Frequency?
The number of times an entry occurs in a dataset is frequency. For example, from our dataset, we can count the value instances within a range as shown in the following picture.
The above screenshot shows how many values are in those respective ranges in our dataset. In an instant, we can figure out that we have 3 instances between 50 to 59 (range) in our dataset and it goes the same for the others.
Now, relative frequency is a ratio or quotient of the occurrence time of values within a range and the total occurrence. So, the formula turns out to be
Relative Frequency= (Frequency within a Range/Total Number of Frequency)
We can illustrate this Relative Frequency Distribution using a Bar or Pivot chart. For this reason, we illustrate relative distribution using the Bar and Pivot Chart in the latter sections.
2 Easy Ways to Illustrate Relative Frequency Distribution in Excel
Method 1: Relative Frequency Distribution Using Bar Chart (Calculated by Formula)
As we have a typical sale dataset with sold Quantity in it, at first, we have to count the occurrences of sold Quantities of the products. Then we calculate the relative frequency using the Division Operator (/).
However, we don’t know the maximum and minimum value of the Quantity amounts. That’s the reason, prior to the frequency or relative frequency calculation we need to find the maximum and minimum values.
🔁 Relative Frequency Calculation
Step 1: Write the following formulas in any adjacent cells (i.e., F5 and G5) to get the Maximum and the Minimum values from the Quantity range.
=MAX(D5:D19)=MIN(D5:D19)
We use the MAX function to get the Maximum value within the Quantity range.
Applying the MIN function, we get the Minimum value among the Quantity amounts.
Step 2: Now, Delete the MAX and MIN values as you know them.
Insert two adjacent columns named BIN (Range) and Frequency.
Type 50 to 130 in BIN column sequentially (leaving 10 differences between the Min (i.e., 50) and Max (i.e.,130) values).
Step 3: Paste the following formula to count the frequencies in an adjacent cell (i.e., G5).
=FREQUENCY(D5:D19,F5:F12)The syntax of the FREQUENCY function is
FREQUENCY (data_array, bins_array)The arguments refer,
data_array; array of values of which you want the frequencies.
bins_array; array of intervals, indicating a range of numbers where the frequencies occur within.
Comparing the syntax and arguments,
D5:D19=data_array
F5:F12=bins_array
In the formula, we enter a short number of cell references for bins_array. That’s because the FREQUENCY function returns more than one item than bins always.
Step 4: Press ENTER and the formula spills the frequencies in all the rows similar to the picture below.
Step 5: Use the below SUM formula to add all the frequencies.
=SUM(G5:G13)
After Hitting ENTER, you’ll get the total frequency number like the following screenshot.
Step 6: To calculate the relative frequency just use the Division Operator (/) as done in the formula.
=G5/$G$14
Step 6: Press ENTER then Drag the Fill Handle to bring out all the relative frequencies of each number range.
🔁 Inserting the Bar Chart
Since we calculate the relative frequency, now we can insert a Bar chart to illustrate the Relative Frequency Distribution.
Step 1: Select the relative frequency range (i.e., H5:H12). Then Go to Insert Tab > Select Clustered Column (from the Charts section).
In a moment, the Bar Chart appears. Edit the Bar Chart Title (i.e., Relative Frequency Distribution) as shown in the following image.
🔁 Editing Bar Chart X-Axis
You see, in the x-axis, the chart doesn’t include the Bin(range) values. To include the range values in the x-axis you have to edit the chart as instructed in the following steps.
Step 1: Right Click on the chart. A Context Menu appears.
In the Context Menu, Select Select Data.
Step 2: Select the Data Source window that appears.
In the window, Click on Edit (under horizontal axis label).
All other selections will be made automatically.
Step 3: Axis Labels window appears.
In the Axis Labels window, Select the Range (i.e., F5:F13) you want to appear in the horizontal axis.
Click OK.
Executing all these above-discussed steps leads you to an outcome like the picture below. In the picture, we can see the Relative Frequency Distribution illustrated by a Bar Chart.
You can use other forms of charts however for convenience we use the Bar Chart illustration.
Read more: How to Calculate Percent Frequency Distribution in Excel
Similar Readings
- Use Formula in an Excel Table Effectively (With 4 Examples)
- How to Use Excel Table Reference (10 Examples)
- Lookup a Table and Return Values in Excel (3 Simple Ways)
- How to Refresh All Pivot Tables in Excel (3 Ways)
Method 2: Relative Frequency Distribution Using Pivot Chart
From the dataset, we know that we don’t have the frequencies for each number range. To get the frequencies, we use Excel’s Pivot Table feature to count the frequencies then use the frequencies and row labels to insert a Pivot Chart.
🔁 Frequency Calculation
Step 1: Select the entire dataset after that Go to the Insert Tab > Click on Pivot Table (in the Tables section).
Step 2: PivotTable from table or range dialog box appears.
In PivotTable from table or range dialog box, Mark the New Worksheet option.
Click OK.
Step 3: Pivot Table Fields side menu appears.
Checked Quantity in the Pivot Table Fields side menu then Drag the Quantity field to the Rows and Values region as shown in the below screenshot.
After executing Step 3, all the frequencies of different number ranges appear in the following image.
🔁 Relative Frequency Calculation
In the earlier section of this method, we find the frequencies of different number ranges. Now, we need to calculate the relative frequencies. So, we change the resultant range’s heading Row Labels to Range and Count of Frequencies to Frequency. We only do that for a better presentation.
Step 1: Write the below formula in any adjacent cell (i.e., D3).
=C3/$C$9
Step 2: Hit ENTER then Drag the Fill Handle to bring out the relative frequencies for respective number ranges.
🔁 Inserting Pivot Chart
After getting the relative frequencies, it’s possible to make an illustration of that relative frequency distribution using a Pivot Chart. By following the latter sequences, you can insert a Pivot Chart.
We make a separate range using the Range and Relative Frequency column in order to insert the Pivot Chart.
Step 1: Select the entire range (i.e., B11:C17). Afterward, Go to Insert Tab > Click on Pivot Chart (in Pivot Chart section).
Step 2: The PivotTable Fields side menu appears.
Checked both Range and Relative Frequency fields as shown in the following screenshot.
You can view a more lucid illustration of the Relative Frequency Distribution in the latter picture.
Conclusion
In this article, we discuss Relative Frequency Distribution in Excel and illustrate it using Bar and Pivot Charts. I hope this article clears the concept of relative frequency and its usage to understand a dataset. Comment, if you have further queries or have something to add. See you in my other articles.
