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.

**Table of Contents**hide

## 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**.

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.