How to Make a Relative Frequency Table in Excel (with Easy Steps)

We frequently need to determine how vital and influential a specific range is. The relative frequency of a data set can be used to measure the effect of a range. In this tutorial, we will show you how to make a relative frequency table in Excel.

Introduction to Relative Frequency

Relative frequency is the ratio of a frequency within a specific range to the total number of frequencies. The frequency of an occurrence refers to the number of times it occurs. The percentage or dominance of a class range over the overall range can be determined using relative frequency.

How to Make a Relative Frequency Table in Excel: 5 Quick Steps

We’ve included a sample data set in the image below, which includes some weight information for certain specific people. Each class’s frequency must be determined. We could do it by hand, one by one, counting the frequency. However, if our data set is too vast, this is not a good idea. To count the frequency of each class, we’ll utilize the COUNTIFS function. Later, we’ll sum everything to get the overall frequency and create a ratio for each range frequency. We’ll also make a chart to show how the relative frequency compares to the discrete frequency.

Step 1: Specify the Range for Weights

Firstly, specify the range with an equal interval. In our example, we take a range from 40 to 100 with an equal interval of 10.

Step 2: Apply COUNTIFS Function to Count Frequency

We will apply the COUNTIFS function to count the number of frequencies for the range of 40–49.
In cell E5, for the criteria argument, we will apply the conditions of greater than or equal to 40 and less than or equal to 49.

=COUNTIFS($B$5:$B$19,">=40",$B$5:$B$19,"<=49")

As a result, because there are four cell values for the weight range between 40 and 49 kg, the answer will be 4.

Apply the same function to the other ranges as well. For example, in cell E10, we will insert the following formula with the COUNTIFS function.

=COUNTIFS($B$5:$B$19,">=90",$B$5:$B$19,"<=100")

Step 3: Use SUM Function to Count Total Frequency

To count the total frequency of the data set, write the following formula with the SUM function.

=SUM(E5:E10)

Press Enter to see the result of total frequency (15).

Step 4: Insert Formula to Make Relative Frequency Table

Now, divide each cell’s frequency by the total frequency to find the relative frequency.
For example, for the cell value of E5 (4), type the following formula.

=E5/E11

Consequently, it will result in 0.2666667 as the relative frequency of the range 40–49.

Repeat the procedure for the other ranges as well.
As a result, your relative frequency table will display the one below.

Step 5: Insert a Chart with a Relative Frequency Table

Finally, from the Insert tab, select any chart with the relative frequency table.
The orange color in the chart denotes the relative frequency of a given range, whereas the blue color denotes the frequency of that specific range.

Download Practice Workbook

Relative Frequency Table.xlsx

Conclusion

I hope this article has given you a tutorial about how to make a relative frequency table in an Excel spreadsheet. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

Please contact us if you have any questions. Also, feel free to leave comments in the section below.

Stay with us and keep learning.

<< Go Back to Frequency Distribution in Excel | Excel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!