Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Calculate Average Rating in Excel (7 Methods)

The average rating means the review of any product. We can calculate this average rating using the formulas in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


What is the Average Rating?

The average rating, or more commonly known as review, is used for commercial purposes. This rating indicates the customer’s satisfaction value for a certain product. It also helps other customers to decide to buy or avail of that product. We may also say this as the average customer satisfaction score. The standard value of the average rating is settled based on 1-5 or 1-10 ratings. This value is defined as the sum of customers’ rating divided by the total number of customers.


7 Methods to Calculate Average Rating in Excel

We will show the average rating calculation using the Excel formula in the following methods.

We are trying to calculate the average rating of a movie named “Money Heist“. The calculation is based on the dataset given below.


1. Calculate Average Rating Using Multiplication and SUM function

We will use the SUM function and simple multiplication for this method.

We added the necessary columns and rows for our calculation in the dataset.

Step 1:

  • Go to Cell E5.
  • Put the formula below, which is the multiplication of the Rating and of Customer.
=C5*D5

Calculate Average Rating Using Multiplication and SUM function

Step 2:

  • Then press Enter.

We get the subtotal of 5-star rating.

Step 3:

  • Drag the Fill Handle icon to Cell E9.

Calculate Average Rating Using Multiplication and SUM function

Step 4:

  • We can perform this more easily. Put the following formula on Cell E5.
=D5:D9*C5:C9

Calculate Average Rating Using Multiplication and SUM function

Step 5:

  • Press the Enter button.

We do not need to apply the fill handle feature in this formula. We just put the whole range here.

Step 6:

  • Now, find the no. of the total customer using the following formula on Cell D10.
=SUM(D5:D9)

Calculate Average Rating Using Multiplication and SUM function

Step 7:

  • Click the Enter button.

Step 8:

  • Put another SUM formula on Cell E10 to the Total Rating.
=SUM(E5:E9)

Calculate Average Rating Using Multiplication and SUM function

Step 9:

  • Again click on Enter.

Step 10:

  • We get the total ratings and total no. of customers. Now, divide the total ratings by the total customer. Apply the following formula to Cell E11.
=E10/D10

Calculate Average Rating Using Multiplication and SUM function

Step 11:

  • Press Enter button.

Calculate Average Rating Using Multiplication and SUM function

So, our average rating is 2.84. We choose 2 decimal digits in the result.

Read More: How to Calculate 5 Star Rating Average in Excel (3 Easy Ways)


2. Alternative Way for Average Rating Applying SUM Function Multiple Times

In this method, we will apply the SUM function multiple times and will make a simple calculation. We added 3 rows in the dataset for calculation.

Step 1:

  • Go to Cell D11.
  • Put the following formula to get the total no. of customers.
=SUM(D5:D9)

Average Rating Applying Excel SUM Function Multiple Times

Step 2:

  • Click the Enter button.

Step 3:

  • Again, apply a SUM function to get the total ratings at Cell D12.
=SUM(C5:C9*D5:D9)

Average Rating Applying Excel SUM Function Multiple Times

Step 4:

  • Press the Enter button.

Step 5:

  • Now, we will divide the total ratings by the total customer to get the average.
=D12/D11

Average Rating Applying Excel SUM Function Multiple Times

Step 6:

  • Click the Enter button.

Average Rating Applying Excel SUM Function Multiple Times

Read More: How to Calculate Average of Multiple Columns in Excel (6 Methods)


Similar Readings


3. Insert SUMPRODUCT with SUM Function to Measure Average Rating

We will insert the SUMPRODUCT function with the SUM function in this section.

Step 1:

  • Enter Cell D5.
  • Put the formula below to get the total customer.
=SUM(D5:D9)

Step 2:

  • Now, press Enter button.

Step 3:

  • Now, apply the SUMPRODUCT based function on Cell D12.
=SUMPRODUCT(C5:C9,D5:D9)

Insert SUMPRODUCT With SUM Function to Calculate Average Rating

Step 4:

  • Click the Enter button.

Step 5:

  • Now, go to Cell D13.
  • Write the following formula to divide the total ratings by total customers.
=D12/D11

Insert SUMPRODUCT With SUM Function to Calculate Average Rating

Step 6:

  • Press Enter button.

So, this average rating is calculated by inserting the SUMPRODUCT function with the SUM function.

Read More: How to Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)


4. Combination of SUM and COUNT Functions for Average Rating

The combination of SUM and COUNT functions is used in a different situation. When we have only the raw data of the review, this combination is more useful than the others.

In that case, the dataset maybe like this.

We add 3 rows in the dataset for calculation.

Step 1:

  • First, we will count the total number of customers. Go to Cell C16.
  • Write the formula below.
=COUNT(C5:C14)

SUM and COUNT Function for Average Rating in Excel

Step 2:

  • Press the Enter button.

This formula counts total customers from the Rating column.

Step 3:

  • Now, go to Cell C17.
  • Put the formula below to get total ratings.
=SUM(C5:C14)

SUM and COUNT Function for Average Rating in Excel

Step 4:

  • Click Enter button.

Step 5:

  • Enter Cell C18 to get the average applying the following formula.
=C17/C16

SUM and COUNT Function for Average Rating in Excel

Step 6:

  • Press the Enter button.

SUM and COUNT Function for Average Rating in Excel

Here, we have a total of 10 viewers who gave the rating and get the average rating.

Read More: How to Calculate Average and Standard Deviation in Excel


Similar Readings


5. Insert AVERAGE Function to Calculate Average Rating

We can also apply the AVERAGE function to calculate the average rating. We are considering the dataset below for this example.

Step 1:

  • Go to Cell C15.
  • Write the following formula based on the AVERAGE function.
=AVERAGE(C5:C14)

Insert AVERAGE Function to Calculate Average Rating

Step 2:

  • Now, press Enter.

Insert AVERAGE Function to Calculate Average Rating

So simply we calculated the average rating here.

Read More: [Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)


6. Apply Excel AutoSum Tool to Determine Average Rating

AutoSum tool has different features for calculation. We choose the Average feature for this method from AutoSum.

Step 1:

  • First, select all the cells containing ratings.

Step 2:

  • Go to the Editing group from the Home tab.
  • Select the Average feature from the AutoSum tool.

Apply Excel AutoSum Tool to Determine Average Rating

Now, look at Cell C15.

Apply Excel AutoSum Tool to Determine Average RatingThe average rating is shown here.

Read More: How to Calculate Average in Excel (Including All Criteria)


7. Excel VBA to Calculate Average Rating

We will apply a VBA Macro code to calculate the average rating. The average rating will calculate for the dataset given below.

Step 1:

  • Go to the Developer tab.
  • Click on Record Macro.
  • Put a name for the Macro and press OK.

Excel VBA to Calculate Average Rating

Step 2:

  • Go to Macros field.
  • Select Average_Rating macro and Step Into it.

Excel VBA to Calculate Average Rating

Step 3:

  • Write the following VBA code on the command module.
Sub Average_Rating()
 Dim data As Range
 Dim total_rating, total_viewers As Integer
 Dim rating As Double
  Set data = Application.InputBox("Select Range", "Input Section", Type:=8)
  total_rating = WorksheetFunction.Sum(data)
  total_viewers = WorksheetFunction.Count(data)
  rating = total_rating / total_viewers
  MsgBox ("The Average Rating is " & rating)
End Sub

Excel VBA to Calculate Average Rating

Step 4:

  • Click on the marked box to run the code or press F5.

Step 5:

  • In the Input Section window select our desired range.
  • Then press OK.

Excel VBA to Calculate Average Rating

Now, look at the result.

Excel VBA to Calculate Average Rating

Read More: How to Calculate Average of Multiple Ranges in Excel (3 Methods)


Things to Remember

  • Carefully select the cell references.
  • When applying AutoSum first select the desired range

Conclusion

In this article, we described how to calculate the average rating in Excel based on two situations. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo