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`

**To learn more about multiplication in Excel, you can read Multiply Two Columns in Excel.**

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