How to Calculate Average Rating in Excel: 7 Methods

Method 1 – Calculate Average Rating Using Multiplication and SUM function

Step 1:

Go to Cell E5.
Put the formula below, which is the multiplication of the Rating and of Customer.

=C5*D5

Step 2:

Press Enter.

We get the subtotal of a 5-star rating.

Step 3:

Drag the Fill Handle icon to Cell E9.

Step 4:

We can perform this more easily. Put the following formula on Cell E5.

=D5:D9*C5:C9

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:

Find the no. of the total customer using the following formula on Cell D10.

=SUM(D5:D9)

Step 7:

Click the Enter button.

Step 8:

Put another SUM formula on Cell E10 to the Total Rating.

=SUM(E5:E9)

Step 9:

Click on Enter.

Step 10:

We get the total ratings and the total number of customers. Now, divide the total ratings by the total customers. Apply the following formula to Cell E11.

=E10/D10

Step 11:

Press the Enter button.

Our average rating is 2.84. We choose 2 decimal digits in the result.

Method 2 – Alternative Way for Average Rating Applying SUM Function Multiple Times

Step 1:

Go to Cell D11.
Put the following formula to get the total no. of customers.

=SUM(D5:D9)

Step 2:

Click the Enter button.

Step 3:

Apply a SUM function to get the total ratings at Cell D12.

=SUM(C5:C9*D5:D9)

Step 4:

Press the Enter button.

Step 5:

Divide the total ratings by the total customer to get the average.

=D12/D11

Step 6:

Click the Enter button.

Method 3 – Insert SUMPRODUCT with SUM Function to Measure Average Rating

Step 1:

Enter Cell D5.
Put the formula below to get the total customer.

=SUM(D5:D9)

Step 2:

Press the Enter button.

Step 3:

Apply the SUMPRODUCT based function on Cell D12.

=SUMPRODUCT(C5:C9,D5:D9)

Step 4:

Click the Enter button.

Step 5:

Go to Cell D13.
Write the following formula to divide the total ratings by the total customers.

=D12/D11

Step 6:

Press the Enter button.

This average rating is calculated by inserting the SUMPRODUCT function with the SUM function.

Method 4 – Combination of SUM and COUNT Functions for Average Rating

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

We add 3 rows in the dataset for calculation.

Step 1:

Count the total number of customers. Go to Cell C16.
Write the formula below.

=COUNT(C5:C14)

Step 2:

Press the Enter button.

This formula counts total customers from the Rating column.

Step 3:

Go to Cell C17.
Put the formula below to get total ratings.

=SUM(C5:C14)

Step 4:

Click the Enter button.

Step 5:

Enter Cell C18 to get the average applying the following formula.

=C17/C16

Step 6:

Press the Enter button.

We have a total of 10 viewers who gave the rating and got the average rating.

Method 5 – Insert AVERAGE Function to Calculate Average Rating

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)

Step 2:

Press Enter.

We calculated the average rating here.

Method 6 – Apply Excel AutoSum Tool to Determine Average Rating

Step 1:

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.

Look at Cell C15.

The average rating is shown here.

Method 7 – Excel VBA to Calculate Average Rating

Apply a VBA Macro code to calculate the average rating 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.

Step 2:

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

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