JenniferMurphy
Wellknown Member
 Joined
 Jul 23, 2011
 Messages
 1,438
 Office Version

 365
 Platform

 Windows
I would like to compare companies or products that have various ratings such as Amazon or Yelp. Here's an example of 5 hypothetical companies with Yelp ratings on a 15 scale. Column D is the average rating calculated as shown in the formulas.
Using just the average rating, company A gets a perfect score of 5.00. But it is based on just 1 rating, which could be made by someone at the company. Company B has 359 ratings almost all of which are 5's. The average rating is 4.95, but I would give it a higher rating than A. The question is, how much higher? And what about C, D, and E? Where do they rank? I would actually trust all of them more than A, but less than B.
I would like a formula that allows me to apply a correction factor or a weight to the number of ratings in each category.
I found another thread here that discussed a similar problem.
Member maabadi posted a link to a thread on StackExchange that contains an interesting formula from Marc Bogaerts in Brussels that seems to address the problem. At least the OP seemed happy with it.
Sadly, the math is beyond my skills. Can someone either explain the Bogaerts formula or suggest something else that will allow me to rank the companies in the example above in a more useful manner? Or point me to another board where this type of math is discussed. I thought about posting to that StackExchange thread, but it's over 6 years old and I haven't had good luck on StackExchange in the past.
Thanks
Using just the average rating, company A gets a perfect score of 5.00. But it is based on just 1 rating, which could be made by someone at the company. Company B has 359 ratings almost all of which are 5's. The average rating is 4.95, but I would give it a higher rating than A. The question is, how much higher? And what about C, D, and E? Where do they rank? I would actually trust all of them more than A, but less than B.
I would like a formula that allows me to apply a correction factor or a weight to the number of ratings in each category.
I found another thread here that discussed a similar problem.
Comparing the best product based on responses and rating
This is more of a general data analysis question in Excel. Let's say we are analysing three products where customers have provided feedback. We know the product, amount of responses and the rating, as shown in the table below: Product Responses Rating (out of 10) Product 1 590 6.4...
www.mrexcel.com
Member maabadi posted a link to a thread on StackExchange that contains an interesting formula from Marc Bogaerts in Brussels that seems to address the problem. At least the OP seemed happy with it.
Algorithm to calculate rating based on multiple reviews (using both review score and quantity)
First of all I must state that I am not a mathematician, so please correct me if I use wrong terminology. I am building a web application which needs to calculate the rating for each entity based on
math.stackexchange.com
Sadly, the math is beyond my skills. Can someone either explain the Bogaerts formula or suggest something else that will allow me to rank the companies in the example above in a more useful manner? Or point me to another board where this type of math is discussed. I thought about posting to that StackExchange thread, but it's over 6 years old and I haven't had good luck on StackExchange in the past.
Thanks