7 Easy Ways to Calculate Average Rating in Excel

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:

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:

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.

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.

• Afterward, press Enter and drag the fill handle.

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

• Press Enter and drag the fill handle to the right.

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

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

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

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.

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

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

• Hit the Enter button to see the final results.

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

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.

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

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

• 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

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:

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

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

• 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

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:

• 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

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.

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:

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

• 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