Farihat
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.
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.
🔨 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.
🔨 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.
🔨 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
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.
🔨 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
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 IDs, 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.
🔨 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.
🔨 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
