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

**Table of Contents**hide

**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`

**Step 2:**

- Then press
**Enter**.

We get the subtotal of **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:**

- Now, 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:**

- 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`

**Step 11:**

- Press
**Enter**button.

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)`

**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)`

**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`

**Step 6:**

- Click the
**Enter**button.

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

**Similar Readings**

**Running Average: How to Calculate Using Excel’s Average(…) Function****Calculate Average in Excel Excluding 0 (2 Methods)****How to Exclude a Cell in Excel AVERAGE Formula (4 Methods)****Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)****How to Calculate VLOOKUP AVERAGE in Excel (6 Quick Ways)**

**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)`

**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`

**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)`

**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)`

**Step 4:**

- Click
**Enter**button.

**Step 5:**

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

`=C17/C16`

**Step 6:**

- Press the
**Enter**button.

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**

**Generate Moving Average in Excel Chart (4 Methods)****How to Calculate Exponential Moving Average in Excel****Calculate Centered Moving Average in Excel (2 Examples)****Excel Formula to Find Displaced Moving Average (With Easy Steps)****How to Calculate Average Percentage Change in Excel (3 Simple Ways)**

**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)`

**Step 2:**

- Now, press
**Enter**.

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.

Now, look at **Cell C15**.

The 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**.

**Step 2:**

- Go to
**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
```

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

Now, look at the result.

**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

**Get Average Time in Excel (3 Examples)****How to Calculate 7 Day Moving Average in Excel (4 Ways)****Calculate Average of Text in Excel (2 Ways)****How to Calculate Average Percentage of Marks in Excel (Top 4 Methods)****Average Attendance Formula in Excel (5 Ways)****Determine Triple Exponential Moving Average in Excel****How to Calculate Percentage above Average in Excel (3 Easy Ways)**