How to Create a Relative Frequency Table in Excel – 5 Steps

 

Introduction to Relative Frequency

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

Sample Data


The sample dataset showcases people’s weight.

To determine the frequency of each class, use the COUNTIFS function.

Sample Data

Step 1- Specify the Range of Weights

  • Specify the range with an equal interval. Here, 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 a Categorical Frequency Table in Excel


Step 2 – Use the COUNTIFS Function to Count the Frequency

To count the frequency for the range 4049:

  • In E5, use the conditions greater than or equal to 40 and less than or equal to 49 as the criteria argument:
=COUNTIFS($B$5:$B$19,">=40",$B$5:$B$19,"<=49")

Steps to Make a Relative Frequency Table in Excel

  • As there are four cell values for the weight range between 40 and 49 kg, the answer is 4.

Steps to Make a Relative Frequency Table in Excel

  • Use the same function for the other ranges. For example, in E10, use the following formula:
=COUNTIFS($B$5:$B$19,">=90",$B$5:$B$19,"<=100")

Steps to Make a Relative Frequency Table in Excel

Read More: How to Make a Contingency Table in Excel


Step 3 – Use the SUM Function to Count the Total Frequency

  • To count the total frequency of the dataset, enter 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: 15.

Steps to Make a Relative Frequency Table in Excel

Read More: How to Calculate Percent Frequency Distribution in Excel


Step 4 – Use a Formula to Create a Relative Frequency Table

  • Divide the frequency of each cell by the total frequency to find the relative frequency.
  • For the cell value in E5 (4), use the following formula.
=E5/E11

Steps to Make a Relative Frequency Table in Excel

  • The output is 0.2666667: the relative frequency of the range 4049.

Steps to Make a Relative Frequency Table in Excel

  • Repeat the procedure for the other ranges.
  • The relative frequency table will be displayed.

Sample Data


Step 5 – Insert a Chart with a Relative Frequency Table

  • In the Insert tab, select a chart.
  • The orange color denotes the relative frequency of a given range, whereas the blue color denotes the frequency of that specific range.

Sample Data


Download Practice Workbook


Related Articles


<< Go Back to Frequency Distribution in Excel | Excel for StatisticsLearn Excel

Get FREE Advanced Excel Exercises with Solutions!
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo