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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


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.

Sample Data


5 Quick Steps to Make a Relative Frequency Table in Excel

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.

Sample Data

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.

Steps to Make a Relative Frequency Table in Excel

Read More: How to Make Frequency Distribution Table in Excel (4 Easy Ways)


Step 2: Apply COUNTIFS Function to Count Frequency

  • We will apply the COUNTIFS function to count the number of frequencies for the range of 4049.
  • 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")

Steps to Make a Relative Frequency Table in Excel

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

Steps to Make a Relative Frequency Table in Excel

  • 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")

Steps to Make a Relative Frequency Table in Excel

Read More: How to Do a Frequency Distribution on Excel (3 Easy Methods)


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)

Steps to Make a Relative Frequency Table in Excel

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

Steps to Make a Relative Frequency Table in Excel

Read More: How to Make a Relative Frequency Histogram in Excel (3 Examples)


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

Steps to Make a Relative Frequency Table in Excel

  • Consequently, it will result in 0.2666667 as the relative frequency of the range 4049.

Steps to Make a Relative Frequency Table in Excel

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

Sample Data


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.

Sample Data

Read More: How to Calculate Cumulative Relative Frequency in Excel (4 Examples)


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.

We, the Exceldemy Team, are always responsive to your queries.

Stay with us and keep learning.


Related Articles

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo