3 Approaches to Calculate 5 Star Rating Average in Excel

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.

Overview of methods


📁 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.

Formula for average calculation for 5 star rating


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.

Dataset to calculate average of 5 star rating in excel

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.

Create a table to calculate average for 5 star rating and to add 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.

Manual Multiplication

Copying formula

  • 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.

Getting the average 5 star rating in excel

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.

Insert a formula to add star icon

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

Implement a set of standard ratings

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

New rule option from conditional formatting drop-down

  • 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.

New formatting rule window

  • 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.

New formatting rule window for icon customize

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

Outcome with average and desired star icons

📕 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.

Automatically determine the average 5 star rating in excel

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 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.
=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.

Calculating average 5 star rating from random users in excel

  • 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.

Automatically calculate the average for overall rating in excel

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

(Visited 37 times, 1 visits today)
Farihat

Farihat

An Excel enthusiast who wants to share and loves to solve Excel problems. I try to explore available options to guide readers to the maximum ways how they can solve. The world is limitless, and so are the problems in it. Let's help each other to grow. I would love to hear feedback and queries from readers. You can visit my LinkedIn for further information.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo