EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Formula and Functions Calculate Average Rating
 

Calculate Average Rating

Anjali Singh
Article byAnjali Singh
Madhuri Thakur
Reviewed byMadhuri Thakur

Calculate Average Rating in Excel

Table of Contents
  • Why Calculate Average Rating?
    • Method #1: SUM
    • Method #2: SUM with SUMPRODUCT
    • Method #3: SUM with COUNT
    • Method #4: AUTOSUM

Why Calculate Average Rating?

Average ratings play an important role in influencing potential buyers’ or users’ decision-making process. So, businesses calculate average ratings to understand average customer satisfaction with a product or service.

 

 

Microsoft Excel has plenty of functions for solving complex equations and calculating simple averages. There are direct functions for calculations, but in this article, we will use the SUM function in different methods to calculate the average rating in Excel.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

4 Methods to Calculate Average Rating in Excel

Below is a detailed overview of all 4 methods to calculate average rating in Excel:

You can download this Calculate Average Template here – Calculate Average Template

Method #1: Calculate Average Rating Using SUM Function

We have the data of a vacuum cleaner with its rating (1-5) and no.of customers who gave that rating.

Given,

Method 1

Step 1:  We will first multiply the Ratings by the given Number of Ratings.

The formula will be,
=Ratings x Number of Ratings

In Excel, you can add the below formula in cell D4.
= B4*C4

Method 1-1

Step 2: We will press the ENTER key to get the result.
After that, by using Excel’s AutoFill feature, we can drag the Fill Handle down to calculate the values for the other cells with the same formula.

Method 1-2

Step 3: Now, here, we have to find the sum of the number of ratings.
We will use the Excel formula: =SUM(D4:D8)

Method 1-3

Press ENTER to get the result.

Method 1-3-2

Step 4: Next, we will find the sum of the total no. of ratings too.
The function formula will be: =SUM(E4:E8)

Method 1-4

Press the ENTER button to get the result.

Method 1-4-2

Step 5: We will then find the average rating by dividing the no. of ratings by the total number of ratings.

So, the formula will be:
= C9/D9

Calculate Average Rating-Method 1-5

Result: Thus, the average rating for the vacuum cleaner is 0.3.

Method 1-5-2

Method #2: Calculate Average Rating Using SUM with SUMPRODUCT Function

Step 1:
We have to find the sum of the total no. of ratings.

Therefore, the formula will be:
=SUM(C4:C8)

Calculate Average Rating-Method 2

Press ENTER to get the result.

Calculate Average Rating-Method 2-1-2

Step 2: Now, we will apply the function based on SUMPRODUCT on Cell 11.
Thus, the formula will be:
=SUMPRODUCT(B4:B8, C4:C8)

Calculate Average Rating-Method 2-2

Press ENTER to get the result.

Calculate Average Rating-Method 2-2-2

Step 3: Now, we will go to Cell C12.
We will divide the subtotal rating by the total rating to calculate average.

Therefore, the formula will be:
=C11/C10

Calculate Average Rating-Method 2-3

Press ENTER to get the result.

Calculate Average Rating-Method 2-3-2

Thus, the average rating for the given vacuum cleaner is 3.8.

Method #3: Calculate Average Rating Using SUM with COUNT Function

We have the ratings for television, and we need to calculate the average using two different methods.

Given,

Calculate Average Rating-Method 3

Step 1: First, we will find the total no. of customers. Go to cell no C15, and use the formula:
=COUNT(C4:C13)

Method 3-1

Press the ENTER button to get the result.

Calculate Average Rating-Method 3-1-2

Step 2: Now, go to cell C16 to find the subtotal rating. Here, we will use the below Excel function:
=SUM(C4:C13)

Method 3-2

Press the ENTER button to get the result.

Method 3-2-2

Step 3: Now, go to cell C17 and enter the below-given formula to calculate average:
=C16/C15

Calculate Average Rating-Method 3-3

Press the ENTER button to get the result.

Method 3-3-2

Thus, 10 customers gave an average rating of 3.9 for the television.

Method #4: Calculate Average Rating Using AUTOSUM

Step 1: We will first select all the cells containing the rating, i.e., cell range C4 to C13.

Calculate Average Rating-Method 4-1

Step 2: Go to the Home tab and click on Average from the AutoSum tool.

Method 4-2

Note: You don’t need to select a cell for the result, as AutoSum directly adds the answer to the cell after the selected range.
Result: 
As we can see, the AUTOSUM feature added the average rating of 3.9 to cell C14.

Calculate Average Rating-Method 4-2-2

Frequently Asked Questions (FAQs)

Q1. What are the 4 types of averages in statistics?
Answer: Below are the 4 types of averages in statistics:

  • Mean: It is the average of a set of numbers, which we calculate by adding all the values and dividing the sum by the total count.
  • Median: It is the middle value in a given list of numbers, separating the higher and lower values equally.
  • Mode: It is the number that appears most frequently in a given dataset.
  • Range: The range is the difference between the highest and lowest values in a data set, representing the variability of the data.

Q2. Are mean and average the same?
Answer: Yes, arithmetic mean, and average are the same thing in statistics. We calculate the mean or average of a given dataset by adding all the values together and then dividing the sum by the total count of values.

Recommended Articles

This was a guide to how to calculate average ratings in Excel. We explained each step thoroughly, along with practical Excel templates. You may look for some similar articles below:

  1. Basic Excel Formulas
  2. Advanced Excel Formulas and Functions
  3. Cheat Sheet of Excel Formulas
  4. Calculate Income Tax in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Calculate Average Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Calculate Average Template

EDUCBA

डाउनलोड Calculate Average Template

🚀 Limited Time Offer! - ENROLL NOW