In this article, I’ll show you three excellent methods how to calculate average rating in **Excel**. Furthermore, these strategies will allow you to solve the problem that brought you here. However, you will learn some important **Excel **features and functions in this post that will come in handy for any Excel task.

## 📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.

## What is the Average Rating?

The term “**Average Rating**” refers to the numerical display of the average value of a collection of ratings. By adding up each individual rating and dividing by the total number of ratings, the average rating is determined.

A product, service, or anything else that has been rated by numerous people might have an average rating, which can be used to summarize and characterize the overall rating. When deciding whether to buy a product or a service provider to utilize, for example, the average rating gives a rough notion of what the majority of people think about the item being rated.

## Formula of Average Rating in Excel

The formula for calculating the average rating follows:

**Average rating = (rating1 + rating2 + … + rating)/ n**

Where,

**rating1**,**rating2**, …, ratings are the individual ratings, and n is the total number of ratings.

For example, if you have the following set of ratings: 1,2,3,4,5 the average rating would be calculated as **(1+2+3+4+5) / 5 = 3**.

## How to Use AVERAGE Function in Excel?

Excel’s **AVERAGE **function is a straightforward mathematical calculation that finds the mean of a set of data. Specifically, the **AVERAGE** function can be written as follows:

**=AVERAGE(number1, [number2], …) (number1, [number2], …)**

## Learn to Calculate Average Rating in Excel with These 3 Useful Techniques

Here in this article, we will learn how to calculate average rating in Excel using different approaches. To be exact, I’ve provided a total of 7 methods down below. Here we considered the rating data of a popular tv series as our dataset. The dataset consists Ratings and the number of subscribers.

**Method**

### 1. Using SUM function After Multiplication

Firstly we will employ the **SUM** function to find out the average rating. A simple mathematical operation that adds up the numbers in a group of cells is the **SUM** function in Microsoft Excel. Calculating totals, finding the sum of several columns or rows, or figuring out the total worth of a budget or investment portfolio are all common tasks that employ this feature in spreadsheet applications. The **SUM** function has the following syntax: the range is a collection of cells that you want to sum up by using the formula **SUM(range)**. For instance, you could use the formula **=SUM(C1:C10) **to sum a group of values in cells **C1 **through **C10** and obtain the answer. It is a useful feature for data manipulation in a worksheet as result. Let’s explore this approach step by step.

⬇️⬇️ **STEPS **⬇️⬇️

- Select cell
**E6**. - Write down the following formula to calculate the subtotal.

`=C6*D6`

- Afterward, press
**Enter**and drag the**fill handle**.

- Now go to cell
**D11**. - Again write down the following formula:

`=SUM(D6:D10)`

- Press
**Enter**and drag the**fill handle**to the right.

- Finally, insert the following formula to calculate the average rating.

**=E11/D11**

In this way, you can calculate the average rating in Excel.

**📕 Read More: ****2 Easy Ways to Compute Average Excluding 0 in Excel**

**Method**

### 2. Applying SUM Function Multiple Times

Again, the **SUM** function is to find out the average rating. But this time we will use it multiple times and we won’t use the fill handle option. This makes the process clearer to the readers and easier to understand. This method is less complicated than the previous one. The steps are given below.

⬇️⬇️ **STEPS **⬇️⬇️

- Go to cell
**C12**. - Insert the following formula to calculate the total number of subscribers.

**=SUM(D6:D10)**

- Hit
**Enter button**and move to the following steps. - Now select cell
**C13** - Then write down the following formula to calculate the
**Subtotal**rating.

`=SUM(C6:C10*D6:D10)`

- Click the
**Enter**button. - Pick cell
**C14**. - Finally, insert this formula to calculate the average rating:

`=C13/C12`

- Hit the
**Enter**button to see the final results.

Thus you will be able to calculate the average rating in Excel.

**Method**

### 3. Using SUMPRODUCT and SUM Functions Combined

Finally, we used the **SUMPRODUCT **and **SUM **functions in this technique. **Excel’s SUMPRODUCT function** is a multiplication operation that returns the total product of the input arrays or ranges multiplied by the number of input arrays or ranges. Common uses for this function in spreadsheets include computing weighted averages, summing up several items, and valuing a portfolio of investments.

**SUMPRODUCT(array1, [array2],…) **is the syntax for the SUMPRODUCT function when multiplying and adding together arrays or ranges of values. Both the rows and the columns of the arrays should be equal in size.

If you have a list of sales in cells **C1 **through **C10** and a list of weights in cells **C1 **through **C10**, you can calculate the weighted average of the sales with the formula **=SUMPRODUCT(C1:C10,D1:D10**).

To accomplish more advanced computations, the **SUMPRODUCT **function can be used in conjunction with other Excel functions and processes. The steps are given in proper sequence below

⬇️⬇️ **STEPS **⬇️⬇️

- Pick cell
**C12**. - Write down the following formula to calculate the total subscribers using the
**SUM**function.

`=SUM(D6:D10)`

- Hit
**Enter**key and follow the following steps. - Then write down the following formula to calculate the Subtotal rating using the
**SUMPRODUCT**function. - Afterward, click the
**Enter**button.

`=SUMPRODUCT(C6:C10,D6:D10)`

- Later go to cell
**C14**. - Finally, insert the following formula to calculate the average rating:

`=C13/C12`

- Again press the
**Enter**button to see the final results.

Accordingly, you can calculate the average rating in Excel.

**📕 Read More: ****3 Approaches to Calculate 5 Star Rating Average in Excel**

**Method**

### 4. Applying SUM with COUNT Function

We will use **Find & Select** Option to solve this problem. Microsoft Excel’s **COUNT **function is a fundamental statistical function that counts the number of numeric cells within a given range. In worksheets, we frequently use it for tasks like counting the number of things in a list, finding the number of items that meet a given requirement, or identifying the number of non-empty cells. To count a specific group of cells, type** COUNT(range)**, where the range is the cell range in question. If you want to know how many cells in your spreadsheet contain numbers, you can use the formula **=COUNT(C1:C10)** to do so. Here, I gave a detailed explanation of the procedure.

⬇️⬇️ **STEPS **⬇️⬇️

- First and foremost select cell
**C12**. - Write down the following formula:

`=SUM(C6:C10)`

- And press the
**Enter**key to see the total subscriber. - Now insert the following formula to calculate the subtotal rating in cell
**C13**.

**=SUM(C6:C10)**

- Again hit
**Enter**to see the results. - Finally, write down the following formula to calculate the average rating in cell
**C14**.

**=C13/C12**

- Press
**Enter**to see the output.

That’s how you can calculate the average rating in Excel.

**📕 Read More: ****7 Ways to Calculate Average Attendance with Formula in Excel**

**Method**

### 5. Inserting Only AVERAGE Function

We applied the **AVERAGE **function in this method. To **determine the average** (arithmetic mean) of a group of values, use Excel’s **AVERAGE **function. Inputs can be one or more values, and the function will calculate and return the average. the correct syntax is **=AVERAGE (number1, [number2, …])**. Any arguments that are not numbers or functions are disregarded by the function. Any values in the argument list that are not numbers will be disregarded, and the function can take up to 255 of them. With the **AVERAGE **function, you get a single integer that stands for the average of the data points you supply. I will show the steps of the technique one by one.

⬇️⬇️ **STEPS **⬇️⬇️

- Initially pick cell
**C12**. - Write down the following formula to calculate the average rating:

**=AVERAGE(C6:C10)**

- Tap
**Enter**to see the results.

Hence, you will be able to calculate the average rating in Excel.

**📕 Read More: ****6 Ways to Solve When Average Formula is Not Working in Excel**

**Method**

### 6. Utilizing Excel AutoSum Feature

Finally, we used **AutoSum **Feature in this technique. The **AutoSum **feature in Excel can also be used to quickly calculate the average of a range of numbers. To do this, you can select the range of cells that you want to average and then press the “**Alt + =**” keyboard shortcut. This will automatically insert the **AVERAGE **function into the active cell, with the selected range as its argument. The function will then return the average of the numbers in the selected range. If you prefer to use the ribbon, you can click the AutoSum button on the Home tab, then select the “**Average**” option from the drop-down menu. The steps are given in the proper sequence below:

⬇️⬇️ **STEPS **⬇️⬇️

- Select the cell range
**C6:C10**. - Select the options
**Home**tab >**AutoSum**feature >**Average**option.

- Finally, you will see the average rating.

Thus, you can calculate the average rating in Excel.

**Method**

### 7. Excel VBA to Calculate Average Rating

We will use the **VBA **option to solve this technique. Excel the **VBA **macros are a collection of instructions that can be used to automate various operations, including data preparation, chart creation, and calculation. Examples of VBA macro functionality include the creation of a new worksheet with pre-populated data taken from another worksheet depending on certain criteria and automatically formatting a set of cells each time the workbook is accessed. The steps are given in proper sequence below

⬇️⬇️ **STEPS **⬇️⬇️

- Take the cursor to the sheet name and click right.
- Select the option
**View code**.

- Insert the following
**VBA**code:

```
Sub AverageRating()
Dim rng As Range
Dim cel As Range
Dim total As Double
Dim count As Integer
Dim avg As Double
Set rng = Application.InputBox("Select the range of cells to calculate average", Type:=8)
For Each cel In rng
If IsNumeric(cel.Value) Then
total = total + cel.Value
count = count + 1
End If
Next cel
avg = total / count
MsgBox "The average rating is: " & avg
End Sub
```

- Run the code.
- Input the cell range of rating of which you want to calculate the average rating.

- Finally, you can see the average rating in a message box.

Accordingly, you will be able to calculate the average rating in Excel.

## How to Calculate Weighted Average Rating in Excel?

WE took the relative importance of various values in data collection into account during the weighted average computation in Excel. It is used to figure out the average of a group of values, each of which has a particular weight or level of significance. The steps are given below:

⬇️⬇️ **STEPS **⬇️⬇️

- Select cell
**D12**. - Insert the following formula:

**=SUMPRODUCT(C6:C10*D6:D10)**

- Finally, press
**Enter**button to see the weighted average.

Accordingly, you can calculate the weighted average.

## How to Calculate Average Rating with Criteria Using AVERAGEIF Function in Excel

Now, what if we want to calculate an average rating for a particular rater? To accomplish this, we will use the **AVERAGEIF** function. The **AVERAGEIF **function is a helpful tool for examining and summing up data depending on certain circumstances since you may use it to average just those cells in a range that fulfill a predetermined requirement. I demonstrated the process below:

⬇️⬇️ **STEPS **⬇️⬇️

- Pick cell
**D12**. - Write down the following formula:

`=AVERAGEIF(C6:C10,"IMDB",D6:D10)`

- Finally, press
**Enter**to see the results.

Thus, you can calculate the average for particular criteria.

## 📄 Important Notes

`🖊️`

Verify that you’ve employed Absolute Reference when requires. To get an absolute cell reference, press F4.

`🖊️`

Moreover, always look for ways that are easy to use in different situations.

`🖊️`

Finally, every time you try to use shortcuts on the keyboard.

## 📝 Takeaways from This Article

`📌`

You can calculate the average rating using

`📌`

Moreover, You can calculate the average rating using.

`📌`

In addition, You can calculate an average rating

`📌`

Finally, you can calculate the average rating

**Conclusion**

First and foremost, I hope you were able to apply the principles I taught in this Excel lesson on how to calculate average rating in Excel. As you can see, there are numerous ways to accomplish this. However, you should choose the strategy that is best for your situation with care. Above all, I sincerely hope you can put it to good use. Please share your thoughts in the comments section about how you felt throughout the article and what we should change or add to make things better for you. However, if you have any Excel-related issues, please leave them in the comments section. The **Excelden **team is available to answer your inquiries at any time. Finally, keep reading to educate yourself. Please visit our website **Excelden.com** for further Excel-related articles.

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