4 Replies Latest reply on Mar 23, 2016 5:25 AM by Jason Alexandre

    Calculating a Weighted Mean

    Jason Alexandre



      I'm trying to calculate a weighted mean taking into account the number of ratings by beer style when computing the mean score for each style.  I'm slowly and will continue to add data to my set; however, I want to ensure that styles with only a few ratings (i.e. 1 or 2) are fairly represented against those styles that have many.


      I feel like I need to start with:  Count([Style)*SUM(Total) but am not sure where to go from there.


      I've attached the package workbook.  Thanks in advance for any insights.



        • 1. Re: Calculating a Weighted Mean
          Bill Lyons

          I'm not clear what you are looking for. Are you wanting to know how to create the weighted average using your formula? Or are you looking for advice in creating an appropriate formula for weighting the ratings?


          If you are looking for advice, I would ask a few more questions. Why do you think the ratings need to be weighted? How would a simple average not fairly represent a style with fewer ratings versus those with many?

          • 2. Re: Calculating a Weighted Mean
            Jason Alexandre

            Hi Bill - sorry for being vague.  I guess I'm looking for both.  I'm still inputting data (I have consumed a lot of beer over the years) and I have some styles with only one rating; whereas others with many (100+) and I'm looking for a way to convey that.  For example, right now I have only 1 smoked beer rating review; whereas I have 82 for Russian Imperial Stout.  I'm wondering the best way to factor that in and thought a weighted mean might be the way to go.


            Thanks for looking.



            • 3. Re: Calculating a Weighted Mean
              Bill Lyons

              I am only one opinion, but unless you have some evidence that a single taste is inherently biased in one way or another, I don't think a weighted average will help you.


              What I suggest is showing the distribution in something like a box and whisker plot. In this way, you can see not only the average of a particular style, but how wide the variation is, and nature of the distribution by quartiles and inter-quartile ratio (IQR). In doing this, you can also put another dimension on color to see if any other patterns emerge. In my example, I tried Brewery (far too many values), State (still too many) and stopped with Country. Of course, you could use whatever gives you more meaning. I would also suggest adding a bar chart showing the number of tastes.


              You can see my suggestions in the Tastings sheet in the attached, and in Dashboard 2. I hope this helps expand your range of possibilities.


              And, while you are expanding your possibilities, you might check out a place near me: Goat House Brewing .

              • 4. Re: Calculating a Weighted Mean
                Jason Alexandre

                Thanks Bill!  Agreed - too many options when looking at Brewery and State.  The general rule of thumb when rating a beer is to try to rate to style as opposed to preference (of course, it's all somewhat subjective).  I can tell you that Smoked Beer is not my favorite style (I prefer Lambic, Double Imperial Stout, Double IPA, etc.), which is why I haven't had many (I've had more than one, but need to enter about 1400 more reviews).  That said, you also have to look at the availability/accessibility of certain styles as well.  Some just aren't really made or regionally available.  For example, Kvass (Slavic in origin) and Happoshu (Japanese) are two styles I've never had because they aren't readily available in the US. I was hoping to be able to be sensitive to that factor as well.


                Thanks very much for the recommendations - I'm going to play with your suggestions and data set.  Let me know if you ever want to do a beer swap - locals for locals