Very often we persuade others to rate products. We get different ratings from different customers. We need to calculate the average to get an overview of how much the product is appreciated. In this article, we will show you how to calculate a 5-star rating average in Excel using different functions in Excel.
Suppose, we have collected ratings from customers on TV. Different customers have different ratings. From the figure below, we can see that 281 users have put a 5-star rating, 720 users have put a 4-star rating, 981 customers have put a 3-star rating, 219 customers have put a 2-star rating, 505 customers have put 1-star rating, and so on. We will walk you through 3 different methods to calculate the average rating of the product.
How to Calculate 5 Star Rating Average in Excel: 3 Easy Ways
1. Calculate 5 Star Rating Average Using SUM Function
We can use the SUM function to calculate the average of 5-star ratings. To do so first we need to multiply the Ratings by the Number of Ratings. We write in cell E5 the formula,
=C5*D5
After pressing the ENTER key, we will get the value of the product of Rating and No of Ratings in the Rating * No of Ratings column.
By dragging down the Fill Handle to use the AutoFill feature of Excel we can get values in other cells in the Rating * No of Ratings column.
We will get corresponding values in other cells as well.
Now we will get the sum of Rating * No of Ratings column and divide it by the sum of No of Ratings column. To do so, we will use the SUM Function of Excel. In the G8 cell, we would write
=SUM(E5:E10)/SUM(D5:D10)
Formula Breakdown
SUM(E5:E10) >> Shows us the sum of E5 to E10 cells in the Rating * No of Ratings column.
      Output is >> 8171
SUM(D5:D10)>> Shows us the sum of E5 to E10 cells in the Rating column.
     Output is>>3006
SUM(E5:E10)/SUM(D5:D10)>> Shows us the result of dividing the first sum by the latter.
     Output is>> 8171/3006 =2.72
     Explanation: Here, it shows the average of 5-star ratings.
Pressing the ENTER key we will get an average of 5-star ratings.
Read More: How to Calculate Average Rating in Excel
2. Calculate 5 Star Rating Average Using SUMPRODUCT Function
We can use the SUMPRODUCT function in Excel to calculate the average of 5-star ratings. To do so we will first get the product of Ratings and No of Ratings, and sum the different products using SUMPRODUCT Function. Then, we will divide it by the sum of the No of Ratings column values. In the G8 cell, we would write,
=SUMPRODUCT(C5:C10,D5:D10)/SUM(D5:D10)
Formula Breakdown
SUMPRODUCT(C5:C10,D5:D10)>> First, the SUMPRODUCT Function will multiply the Rating column by the No. of Ratings column. Then, it will sum all the results of multiplication.
   Output is >> 8171
SUM(D5:D10)>> Shows us the sum of E5 to E10 cells in the Rating column.
   Output is>>3006
SUMPRODUCT(C5:C10,D5:D10)/SUM(D5:D10)>> This shows us the result of dividing the first sum by the latter.
   Output is>>8171/3006 = 2.72
   Explanation: Here, it shows the average of 5-star ratings.
Pressing the ENTER key we will get an average of 5-star ratings.
Read More: How to Calculate Sum & Average with Excel Formula
3. Using SUM and COUNT Function
If we have a dataset that contains all the ratings given by individual users, we can use the SUM and COUNT functions to get the average rating. Here, we have ratings for TV given by different users denoted by different User ID. In that case, we will find the average using the simple concept of average. First, we will get the sum of the Rating and divide the sum by the number of a User ID.
To do so, we will write the following formula in the F8 cell.
=SUM(D5:D12)/COUNT(C5:C12)
Formula Breakdown
SUM(D5:D12)>> First, the SUM Function will sum all the cells in the Rating column.
    Output is >> 13
COUNT(C5:C12)>> Shows us the number of cells with values in the User ID column.
    Output is>>8
SUM(D5:D12)/COUNT(C5:C12)>> Shows us the result of dividing the first sum by the number of cells in the User ID column.
    Output is>>13/8 =1.63
    Explanation: Here, it shows the average of 5-star ratings.
Pressing the ENTER key we will get an average of 5-star ratings.
Things to Remember
SUMPRODUCT Function does the job of sum and product in a single function. You can avoid first getting the product of the numbers and then getting the sum by using this function. It will enable you to skip a step and save time.
Practice Section
We have included a practice section so you can learn the methods yourself.
Download Practice Workbook
Conclusion
To get an overview of our product, we very often rely on user ratings. We can use average user ratings to rank our products and further develop the product. In this article, we have shown 3 easy ways to do so. I hope this will answer your queries. If you have any further queries regarding how to calculate the 5-star rating average in Excel, you may contact us or leave us feedback. Our team is ready to cater to all your needs.
Related Articles
- How to Calculate Average Growth Rate in Excel
- How to Get Average Time in Excel
- How to Calculate Daily Average in Excel
- How to Calculate Daily Average from Hourly Data in Excel
- How to Calculate Weekly Average in Excel
- How to Calculate Average Days in Excel
- How to Calculate Monthly Average from Daily Data in Excel
<< Go Back to Excel Average Formula Examples | How to Calculate Average in Excel | How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
