Link to home
Start Free TrialLog in
Avatar of Star Gazr1
Star Gazr1Flag for United States of America

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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

=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(LEFT(A1:A6,1)), B1:B6)/SUM(B1:B6),2) & " Stars"
SOLUTION
Avatar of Star Gazr1
Star Gazr1
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Please see image below, how to calculated Weighted Average:
User generated imageFirst get the weighted average by this formula:
=ROUND(SUMPRODUCT($A$2:$A$7,$B$2:$B$7)/SUM($B$2:$B$7),2)

Open in new window

Please see attached for further calculation
Average_Star.xlsx
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Star Gazr1

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