How to Calculate 5 Star Rating Average in Excel (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

how to calculate 5 star rating average in excel


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
To know more about multiplication in Excel, you can read this article.

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.

how to calculate 5 star rating average in excel

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.

how to calculate 5 star rating average in excel

We will get corresponding values in other cells as well.

how to calculate 5 star rating average in excel

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.

how to calculate 5 star rating average in excel

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.

how to calculate 5 star rating average in excel


Similar Readings:


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)

how to calculate 5 star rating average in excel

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.


Related Articles

Lutful Hamid
Lutful Hamid

Hello! Welcome to my Profile. I have completed BSc in Naval Architecture and Marine Engineering from BUET and MBA from IBA. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo