Lutful Hamid
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 5-star rating average in Excel using different functions in Excel.
Suppose, we have collected ratings from customers on TV. Different customers have put different ratings. From the figure below, we can see that 281 users have put 5-star rating, 720 users have put 4-star rating, 981 customers have put 3-star rating, 219 customers have put 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.
Download Practice Workbook
3 Easy Ways to Calculate 5 Star Rating Average in Excel
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 the average of 5-star ratings.
Read More: How to Calculate Average in Excel (Including All Criteria)
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 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, SUMPRODUCT Function will multiply the Rating column by 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 the average of 5-star ratings.
Similar Readings:
- Running Average: How to Calculate Using Excel’s Average(…) Function
- How to Calculate VLOOKUP AVERAGE in Excel (6 Quick Ways)
- Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)
- How to Generate Moving Average in Excel Chart (4 Methods)
- Calculate Moving Average in Excel (4 Examples)
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 the 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 that you can learn the methods by yourself.
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. Hope these will answer your queries. If you have any further queries regarding how to calculate 5-star rating average in Excel, you may contact us or leave us feedback. Our team is ready for catering to all your needs.
