Star Gazr1
asked on
Excel Calculate Average - Grouped Values
I am working with a very large set of data that contains a rating number. The example provided below contains a Star Rating (1 Star to 6 Stars), and the count of people for each Star Rating.
in Excel, from the data set below, is there a formula to calculate the average star rating for the entire population. Thanks for any assistance.
1 Star = 10,550
2 Stars = 410,00
3 Stars = 630,00
4 Stars = 250,000
5 Stars = 125,000
6 Stars = 0
in Excel, from the data set below, is there a formula to calculate the average star rating for the entire population. Thanks for any assistance.
1 Star = 10,550
2 Stars = 410,00
3 Stars = 630,00
4 Stars = 250,000
5 Stars = 125,000
6 Stars = 0
=SUMPRODUCT(VALUE(LEFT(A1: A6,1)), B1:B6)/SUM(B1:B6)
This rounds to 2 decimal places and adds "Stars" at the end. (I've bolded the number of decimals for the rounding)
=ROUND(SUMPRODUCT(VALUE(LE FT(A1:A6,1 )), B1:B6)/SUM(B1:B6),2) & " Stars"
=ROUND(SUMPRODUCT(VALUE(LE
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please see image below, how to calculated Weighted Average:
First get the weighted average by this formula:
Average_Star.xlsx
First get the weighted average by this formula:
=ROUND(SUMPRODUCT($A$2:$A$7,$B$2:$B$7)/SUM($B$2:$B$7),2)
Please see attached for further calculationAverage_Star.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much. I really appreciate the assistance. I had posted the same question a while back but the responses indicated it couldn't be done in Excel. I figured it could and that is why I posted the question again.
You're welcome and I'm glad I was able to help.
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015 and 2016
Experts Exchange Top Expert Visual Basic Classic 2012 to 2016
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015 and 2016
Experts Exchange Top Expert Visual Basic Classic 2012 to 2016