Table of Contents
Why Calculate Average Rating?
Average ratings play an important role in influencing potential buyers’ or users’ decision-making process. So, businesses calculate average ratings to understand average customer satisfaction with a product or service.
Microsoft Excel has plenty of functions for solving complex equations and calculating simple averages. There are direct functions for calculations, but in this article, we will use the SUM function in different methods to calculate the average rating in Excel.
4 Methods to Calculate Average Rating in Excel
Below is a detailed overview of all 4 methods to calculate average rating in Excel:
Method #1: Calculate Average Rating Using SUM Function
We have the data of a vacuum cleaner with its rating (1-5) and no.of customers who gave that rating.
Given,
Step 1: We will first multiply the Ratings by the given Number of Ratings.
The formula will be,
=Ratings x Number of Ratings
In Excel, you can add the below formula in cell D4.
= B4*C4
Step 2: We will press the ENTER key to get the result.
After that, by using Excel’s AutoFill feature, we can drag the Fill Handle down to calculate the values for the other cells with the same formula.
Step 3: Now, here, we have to find the sum of the number of ratings.
We will use the Excel formula: =SUM(D4:D8)
Press ENTER to get the result.
Step 4: Next, we will find the sum of the total no. of ratings too.
The function formula will be: =SUM(E4:E8)
Press the ENTER button to get the result.
Step 5: We will then find the average rating by dividing the no. of ratings by the total number of ratings.
So, the formula will be:
= C9/D9
Method #2: Calculate Average Rating Using SUM with SUMPRODUCT Function
Step 1:
We have to find the sum of the total no. of ratings.
Therefore, the formula will be:
=SUM(C4:C8)
Press ENTER to get the result.
Step 2: Now, we will apply the function based on SUMPRODUCT on Cell 11.
Thus, the formula will be:
=SUMPRODUCT(B4:B8, C4:C8)
Press ENTER to get the result.
Step 3: Now, we will go to Cell C12.
We will divide the subtotal rating by the total rating to calculate average.
Therefore, the formula will be:
=C11/C10
Press ENTER to get the result.
Thus, the average rating for the given vacuum cleaner is 3.8.
Method #3: Calculate Average Rating Using SUM with COUNT Function
We have the ratings for television, and we need to calculate the average using two different methods.
Given,
Step 1: First, we will find the total no. of customers. Go to cell no C15, and use the formula:
=COUNT(C4:C13)
Press the ENTER button to get the result.
Step 2: Now, go to cell C16 to find the subtotal rating. Here, we will use the below Excel function:
=SUM(C4:C13)
Press the ENTER button to get the result.
Step 3: Now, go to cell C17 and enter the below-given formula to calculate average:
=C16/C15
Press the ENTER button to get the result.
Thus, 10 customers gave an average rating of 3.9 for the television.
Method #4: Calculate Average Rating Using AUTOSUM
Step 1: We will first select all the cells containing the rating, i.e., cell range C4 to C13.
Step 2: Go to the Home tab and click on Average from the AutoSum tool.
As we can see, the AUTOSUM feature added the average rating of 3.9 to cell C14.
Frequently Asked Questions (FAQs)
Q1. What are the 4 types of averages in statistics?
Answer: Below are the 4 types of averages in statistics:
- Mean: It is the average of a set of numbers, which we calculate by adding all the values and dividing the sum by the total count.
- Median: It is the middle value in a given list of numbers, separating the higher and lower values equally.
- Mode: It is the number that appears most frequently in a given dataset.
- Range: The range is the difference between the highest and lowest values in a data set, representing the variability of the data.
Q2. Are mean and average the same?
Answer: Yes, arithmetic mean, and average are the same thing in statistics. We calculate the mean or average of a given dataset by adding all the values together and then dividing the sum by the total count of values.
Recommended Articles
This was a guide to how to calculate average ratings in Excel. We explained each step thoroughly, along with practical Excel templates. You may look for some similar articles below:
