Is there an Excel function for comparing ratings?

[JenniferMurphy]

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 1-5 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.

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

 

[diddi]

it is not too bad to code this formula for excel, but you are still going to have to consider what constitutes "few", "moderate", "many" which introduces a bias due to your interpretation. so depending on how you do your definitions you will arrive at an arbitrary number which you can use to compare with other numbers within your own system (only). what you could do is just fiddle with the definitions until the results "look like what you expect" which is fine for your own comparisons but is not robust from an analytical point of view

 

[JenniferMurphy]

it is not too bad to code this formula for excel, but you are still going to have to consider what constitutes "few", "moderate", "many" which introduces a bias due to your interpretation. so depending on how you do your definitions you will arrive at an arbitrary number which you can use to compare with other numbers within your own system (only). what you could do is just fiddle with the definitions until the results "look like what you expect" which is fine for your own comparisons but is not robust from an analytical point of view

Hmmm... I assume you are referring to the formula proposed by Marc Bogaerts over on StackExchange, right? Do you understand that formula? Can you explain it to me?

Are you aware of a formula or process that is "robust" from "an analytical point of view"?

 

[diddi]

yes i am talking about Bogaert. i dont do that sort of modelling, so i cant help with a better formula
score=5p/10+5(1−e−q/Q) is better than the bottom one he quotes for your purposes i think. i cant explain how he arrived at the formula, but basically it has 2 parts...
5p/10 is actually just halving the original review rating, which then allows the second part to contribute the other half of your final rating. it uses a function to add a correction for the number of reviews. that is where the Q is like a "fudge factor" to make the model look correct. sometimes you just cant come up with a true numerical solution when there are "human factors" to consider