# Is there an Excel function for comparing ratings?

#### JenniferMurphy

##### Well-known Member
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.

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

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### diddi

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member
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

Replies
3
Views
211
Replies
2
Views
191
Replies
2
Views
176
Replies
0
Views
115
Replies
0
Views
225

### Forum statistics

1,126,932
Messages
5,621,676
Members
415,849
Latest member
PhoenixRising2015

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back