# How to Calculate a 5 Star Rating Average in Excel (3 Easy Ways)

### Method 1 – Calculate a 5 Star Rating Average Using SUM Function

STEPS:

•  In cell E5, enter the following formula:
`=C5*D5`

• Press ENTER to get the value of the product of Rating and No of Ratings in the Rating * No of Ratings column.

• By dragging down the Fill Handle, we can use Excel’s AutoFill feature to get values in other cells in the Rating * No of Ratings column.

• We will get corresponding values in other cells as well.

• Get the sum of Rating * No of Ratings column and divide it by the sum of No of Ratings column.
• To do so,
• Use the SUM Function of Excel.
• In cell G8, enter the following formula:
`=SUM(E5:E10)/SUM(D5:D10)`

Formula Breakdown

`SUM(E5:E10)` >> Shows us the sum of E5 to E10 cells in the Rating * No of Ratings column.
Output is >> 8171

`SUM(D5:D10)`>> Shows us the sum of E5 to E10 cells in the Rating column.
Output is>>3006

`SUM(E5:E10)/SUM(D5:D10)`>> Shows us the result of dividing the first sum by the latter.
Output is>> 8171/3006 =2.72
Explanation: Here, it shows the average of 5-star ratings.

• Press ENTER to get an average of 5-star ratings.

Read More: How to Calculate Average Rating in Excel

### Method 2 – Calculate a 5 Star Rating Average Using the SUMPRODUCT Function

STEPS:

• In cell G8, enter the following formula:
`=SUMPRODUCT(C5:C10,D5:D10)/SUM(D5:D10)`

Formula Breakdown

`SUMPRODUCT(C5:C10,D5:D10)`>> First, the SUMPRODUCT Function will multiply the Rating column by the No. of Ratings column. Then, it will sum all the results of multiplication.
Output is >> 8171

`SUM(D5:D10)`>> Shows us the sum of E5 to E10 cells in the Rating column.
Output is>>3006

`SUMPRODUCT(C5:C10,D5:D10)/SUM(D5:D10)`>> This shows us the result of dividing the first sum by the latter.
Output is>>8171/3006 = 2.72
Explanation: Here, it shows the average of 5-star ratings.

• Press ENTER to get an average of 5-star ratings.

### Method 3 – Using SUM and COUNT Functions

STEPS:

• Enter the following formula in cell F8:
`=SUM(D5:D12)/COUNT(C5:C12)`

Formula Breakdown

`SUM(D5:D12)`>> First, the SUM Function will sum all the cells in the Rating column.
Output is >> 13

`COUNT(C5:C12)`>> Shows us the number of cells with values in the User ID column.
Output is>>8

`SUM(D5:D12)/COUNT(C5:C12)`>> Shows us the result of dividing the first sum by the number of cells in the User ID column.
Output is>>13/8 =1.63
Explanation: Here, it shows the average of 5-star ratings.

• Press ENTER to get an average of 5-star ratings.

## Things to Remember

SUMPRODUCT Function does the job of sum and product in a single function. You can avoid first getting the product of the numbers and then getting the sum by using this function. It will enable you to skip a step and save time.

## Practice Section

We have included a practice section so you can practice the methods.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Lutful Hamid

LUTFUL HAMID is an outstanding marine engineer who finds joy in navigating the realms of Excel and diving into VBA programming. To him, programming is like saving time when dealing with data, files, and the internet. His skills extend beyond the basics, covering Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he's shifted gears and now serves as a content developer. In this role,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF