5 star rating is a popular phrase, we come across in our day-to-day life. You can face it in restaurants, mobile applications, academic reports, research works, etc. Either you give feedback in these means of ratings or you take surveys on it from other users. Taking arbitrary ratings is no good if the data isn’t processed in an analytical manner. There comes the necessity to **calculate the average** of 5 star rating system in Excel. This article will guide you through different methods. All the methods have step-by-step instructions with a figurative description.

Following is an image where you can see the methods of how you can easily calculate an average 5 star rating in Excel for a given dataset.

## 📁 Download Excel File

Download the Excel file below.

## Formula for 5 Star Rating Average System Calculation

Among a set of data from users that give ratings to any service, it is inconvenient to understand an absolute rating that can convey the service status or improvements. To get rid of this inconvenience, you can easily calculate average of individual ratings from a 5 star rating system dataset. The usual average method can not portray the weight of ratings. So, a formula is available to successfully calculate the data and get equivalent results of the ratings.

The formula for calculating average for 5 star rating system is below.

## Learn to Calculate 5 Star Rating Average in Excel with These 3 Approaches

This article is going to introduce three ways to calculate average 5 star ratings in Excel. The following discussion will cover the uses of the **SUM**, **SUMPRODUCT**, and **COUNTA** functions and the **Conditional Formatting** option for an additional purpose. Therefore, users will get a walkthrough of these solutions. You will find a dataset to modify in the following methods below to help you understand.

**approach**

### 1. Use of SUM Function for Calculating Average for 5 Star Rating

**The SUM function** is a classic function to add all the available data in order to work with their summation. To calculate an average for 5 star rating system in Excel, we are in need of the **SUM** function to get the total rating. You can determine the product by manual multiplication formula accordingly. This method will also show, how to add star icons according to **the average rating** as output. The process is applicable to all the following methods as well.

**⬇️⬇️ STEPS ⬇️⬇️**

- To begin with,
**create a table**to individually enter the rating variables and calculate a range of**5**columns to showcase the average rating in the**Star**icon.

- Now, select cell
**E6**and enter the following formula.

**=D6*C6**

**🔨 Formula Breakdown**

**D6*C6**

👉 Here, **D6** and **C6** are the rating valuer and the number of users that chose that specific rating respectively.

- Then, using the
**Fill Handle**icon,**copy the formula**from**E6**to**E11**.

- Now, select cell
**D13**and enter the following formula.

**=SUM(E6:E11)/SUM(C6:C11)**

**🔨 Formula Breakdown**

**SUM(E6:E11)/SUM(C6:C11)**

👉 Here, **SUM(E6:E11)** and **SUM(C6:C11)** returns the summation for **E6:E11** and **C6:C11** individually.

👉 **SUM(E6:E11)/SUM(C6:C11)** results in the average rating. It divides the total ratings by the total number of users.

5 star ratings are easier to understand if they are in star icons. The average of all the rating inputs are available in this method. For **Icon** formatting, the **IF**, **ROUNDUP**, and **MOD** functions are useful to ensure the fractional star presentation. After getting values in the unit of **1**, we can use the **Conditional Formatting** option to add star icons and modify according to your need.

- After that, select cell
**B17**and enter the following formula.

**=IF(B$16<=$D$13,1,IF(ROUNDUP($D$13,0)=B$16,MOD($D$13,1),0))**

**🔨 Formula Breakdown**

**IF(B$16<=$D$13,1,IF(ROUNDUP($D$13,0)=B$16,MOD($D$13,1),0))**

👉 Here, **MOD($D$13,1)** returns the remainder of the division of **D13** by **1**.

👉 **ROUNDUP($D$13,0) **gives the rounded number of cell **D13** away from zero. It returns the value to the **IF** function.

👉 Here, **IF(ROUNDUP($D$13,0)=B$16,MOD($D$13,1),0)** generates a value that follows the condition that **ROUNDUP** return is equal to **B6** or not. If it matches, the return will be the same as the return of **MOD** function, otherwise **0**.

👉 Finally, **IF(B$16<=$D$13,1,IF(ROUNDUP($D$13,0)=B$16,MOD($D$13,1),0))** returns a value that follows the condition that **B16** is less than **D13** or not. If it matches, the return will be **1**. Otherwise, same as the return of the previous **IF** function.

- Then, using the
**Fill Handle**icon, copy the formula from**B17**to**F17**.

- Now, select the cell range
**B17:F17**. - Then, choose the
**New Rule**option from the**Conditional Formatting**drop-down under the**Home**tab.

- From the
**New Formatting Rule**window, choose the**Format all cells based on their values**from the**Rule Type**. - Then, go to the
**Edit**section below like the image to choose the**Icon Sets**and the**3-Star Icon**option from the**Format Style**and**Icon Style**drop-downs. - Next, make sure you check the
**Show Icon Only**option like the image.

- After that, select the
**Number**for both the**Data Type**drop-down. - Enter the upper value as
**1**and the mid-value as**5**. - Hit the
**OK**button.

- Therefore, you will be able to calculate the average for 5 star rating in Excel.

**📕 Read More: ****2 Easy Ways to Compute Average Excluding 0 in Excel**

**approach**

### 2. Determine 5 Star Rating Average Utilizing SUMPRODUCT and SUM Functions

**The SUMPRODUCT function** is one of the most useful functions in Microsoft Excel. It sums the arrays added into the function individually and later multiplies them automatically. That is why it also has one of the most simple syntax with arrays only. In order to calculate average for 5 star ratings in Excel, multiplying ratings with their respective user number is the aim of the function. Unlike the first method, it doesn’t need step-by-step processing of dataset. The **SUM** function is also important here to divide the total rating product by total users.

⬇️⬇️ **STEPS **⬇️⬇️

- Initially, select cell
**D13**and enter the following formula.

**=SUMPRODUCT(C6:C11,D6:D11)/SUM(C6:C11)**

**🔨 Formula Breakdown**

**SUMPRODUCT(C6:C11,D6:D11)/SUM(C6:C11)**

👉 Here, **SUMPRODUCT(C6:C11,D6:D11)** returns the product for summation of **C6:C11** and **D6:D11** respectively. Both operations are done within this one function.

👉 **SUM(C6:C11)** gets the total user number with rating inputs.

👉 **SUMPRODUCT(C6:C11,D6:D11)****/SUM(C6:C11)** shows you the average rating. This divides total ratings by total number of users.

Therefore, you can calculate average as **3.24** directly from the given dataset.

**📕 Read More: ****7 Ways to Calculate Average Attendance with Formula in Excel**

**approach**

### 3. Merging SUM and COUNTA Functions for 5 Star Rating Average

**The COUNTA function** is applicable for counting cells that are not empty. This is the only convenient method for a dataset with user **ID**s, rather than total rating number and total user number processing like previous methods. To get the total ratings from the users, the **SUM** function is important here as well.

⬇️⬇️ **STEPS **⬇️⬇️

- Firstly, select cell
**D13**and enter the following formula.

**=SUM(D6:D11)/COUNTA(C6:C11)**

**🔨 Formula Breakdown**

**SUM(D6:D11)/COUNTA(C6:C11)**

👉 Here, **SUM(D6:D11)** and **COUNTA(C6:C11)** returns the summation for **D6:D11** and counts number of cells contains data from **C6:C11** individually.

👉 **SUM(D6:D11)/COUNTA(C6:C11)** shows you the average rating. This divides total ratings by total number of users.

- Moreover, this method gets you the average as
**3.33**directly from the given dataset.

**📕 Read More: ****6 Ways to Solve When Average Formula is Not Working in Excel**

## Average for Overall Rating Calculation in Excel

We have discussed **3** methods to calculate 5 star rating average in Excel. There are various cases where rating scales are different such as **7 star**, **10 star**, **20 star**, and so on. This portion of the article will help you with that scenario. For example, we will discuss a dataset for **10 star rating**. You can follow the template for other cases or overall rating datasets.

⬇️⬇️ **STEPS **⬇️⬇️

- Initially, select cell
**D18**and enter the following formula.

**=SUMPRODUCT(C6:C16,D6:D16)/SUM(C6:C16)**

**🔨 Formula Breakdown**

**SUMPRODUCT(C6:C16,D6:D16)/SUM(C6:C16)**

👉 Here, **SUMPRODUCT(C6:C16,D6:D16)** returns the product for summation of **C6:C16** and **D6:D16** respectively. Both operations are done within this one function.

👉 **SUM(C6:C16)** gets the total user number with rating inputs.

👉 **SUMPRODUCT(C6:C11,D6:D11)****/SUM(C6:C11)** returns you the average rating. This formula finally divides total ratings by the total number of users.

Finally, this method can help you to calculate average of **5.91** directly from the given dataset for a 10 star rating dataset.

## 📝 Takeaways from This Article

This article led the readers to understand the variety of options available to calculate an average for 5 star rating in Excel.

📌 Primarily, you can use manual multiplication to get the total ratings. Then, the **SUM** function can get you all the summation. Dividing them can easily help you to calculate the required average for 5 star rating in Excel.

📌 You can also try the **SUMPRODUCT** function and divide it by the summation return from the **SUM** function.

📌 Finally, you can combine the **COUNTA** function along with the **SUM** function, in the same way, to calculate the average for a 5 star rating dataset in Excel.

## Conclusion

To calculate an average for 5 star rating in Excel, you can use the **SUM**, **SUMPRODUCT**, and **COUNTA** functions and the **Conditional Formatting** option in **3** ways. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow **ExcelDen** to get more access to solutions regarding your Excel problems.

## Related Articles

**7 Quick Ways to Calculate Average Percentage Increase in Excel****6 Quick Ways to Calculate Average Percentage of Marks in Excel****3 Approaches to Calculate Average of Text in Excel****How to Calculate Average Percentage Change in Excel**