How to Find Mean of Frequency Distribution in Excel (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, we perform mathematical operations in Excel. They can include Statistics at times. Whenever dealing with Statistics, the Frequency Distribution comes along. Usually, Frequency means the number of occurrences in a particular range or interval. And the Frequency Distribution displays the frequency counts. Finding the mean, median, mode, standard deviation, etc. from a frequency distribution table is essential. We can calculate the Mean in multiple ways. The computing procedure also depends on your dataset. In this article, we’ll show you the easy and effective ways to Find the Mean of Frequency Distribution in Excel.


How to Find Mean of Frequency Distribution in Excel: 4 Easy Ways

The arrangement of a dataset is a factor in how we determine the Mean. First, we’ll show a dataset that has only the numbers obtained by some students. To illustrate, we’ll use the following picture as an example. For instance, in the following dataset, we have some Students and their Scores. Here, we’ll determine the Mean of the Scores in Excel.


1. Find Mean of Frequency Distribution Manually with Simple Formula

In our first method, we’ll create a simple formula for finding the Mean of Frequency Distribution. We know the arithmetic mean is the Average of some given numbers. And we can calculate the average by dividing the sum of the numbers by the total number. Taking this fact into account, we’ll form the formula. Therefore, go through the procedures below to know about it.


1.1 Arithmetic Mean

To find the Arithmetic Mean, we’ll simply add the numbers manually. Then, divide it by the total numbers. Now, this method is simple only when the dataset is small. Applying this process to a large worksheet will be tiresome and time-consuming. This will also result in errors. Nevertheless, we’ll show you how to create this easy formula. So, follow the steps below to perform the task.

STEPS:

  • First, select cell C11.
  • Then, type the following formula:
=(C5+C6+C7+C8+C9+C10)/6
  • After that, press Enter to return the result.
  • Thus, you’ll see the Mean (55.5) of the Scores.


1.2 Use of Frequency

However, in the following dataset, we have the Scores and the Frequency. In such cases, we have to modify the formula. But, it’s still an easy process. We just need to multiply the Scores by their respective Frequencies. Afterward, add the product outputs and divide them by the total frequency. We can also use the SUMPRODUCT function to multiply the Scores by the Frequencies. It’ll reduce some load. This function will multiply the arrays we input in the argument section. Subsequently, it’ll determine the sum. Hence, learn the following steps to calculate the Mean of Frequency Distribution in Excel.

Find Mean of Frequency Distribution Manually with Simple Formula

STEPS:

  • Firstly, in the cell C11, insert the formula:
=((B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)+(B10*C10))/SUM(C5:C10)
  • Next, return the output by pressing Enter.
  • As a result, it’ll give the Mean.

Find Mean of Frequency Distribution Manually with Simple Formula

  • To use the SUMPRODUCT function, select cell D11.
  • Type the formula:
=SUMPRODUCT(B5:B10,C5:C10)/SUM(C5:C10)
  • Then, press Enter.
  • At last, you’ll get the same result (58.2).

Find Mean of Frequency Distribution Manually with Simple Formula

Read More: How to Create a Grouped Frequency Distribution in Excel


2. Use Average Command from Home Tab for Calculating Mean

Additionally, there are numerous features in Excel that are very useful. In this method, we’ll make use of one of such features. The Average feature in Excel computes the average effortlessly. Therefore, follow the process to carry out the operation.

STEPS:

  • First of all, click the cell C11.
  • Then, go to the Editing section under the Home tab.
  • Click the drop-down icon beside the AutoSum.
  • There, choose Average.

  • As a result, it’ll return the Mean of the Scores in cell C11.

Read More: How to Make Frequency Distribution Table in Excel


3. Insert AVERAGE Function to Get Mean in Excel

Moreover, we can apply the AVERAGE function to find out the Mean. This function calculates the average of a set of numbers. So, learn the following process to perform the task.

STEPS:

  • In the beginning, select cell C11.
  • Afterward, type the formula:
=AVERAGE(C5:C10)
  • Press Enter.
  • Lastly, it’ll return the precise Mean value.

Read More: How to Make a Relative Frequency Histogram in Excel


4. Find Mean of Frequency Distribution with Frequency & Midpoint

In this last method, we’ll use a different dataset. In the following dataset, there are no specific numbers to find the average. Instead, we have Class Intervals. And the number of occurrences (Frequency) in that interval. In such cases, we also need to have the Midpoint of the interval. Now, follow the steps below to Find the Mean of the Frequency Distribution for this kind of dataset.

Find Mean of Frequency Distribution with Frequency & Midpoint

STEPS:

  • Firstly, in cell E5, input the formula:
=C5*D5
  • Then, press Enter.
  • Use the AutoFill tool to complete the rest of the calculations. In this way, we’ll get the products of Frequency & Midpoint.

Find Mean of Frequency Distribution with Frequency & Midpoint

  • Now, apply the AutoSum feature in cells C11 and E11.
  • Consequently, it’ll return the sum of the frequencies and the sum of the frequency & midpoint multiplication in the respective cells.

Find Mean of Frequency Distribution with Frequency & Midpoint

  • Next, select cell G5 and type the formula:
=E11/C11
  • Subsequently, return the output by pressing Enter.
  • Lastly, you’ll get the desired Mean.

Find Mean of Frequency Distribution with Frequency & Midpoint

Read More: How to Do a Frequency Distribution on Excel


Download Practice Workbook

Download the following workbook to practice by yourself.


Conclusion

Henceforth, you will be able to find the mean of frequency distribution in Excel following the above-described methods. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo