Is there an Excel function for comparing ratings?

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,447
Office Version
  1. 365
Platform
  1. 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 1-5 scale. Column D is the average rating calculated as shown in the formulas.

1613702720219.png

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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,301
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Jul 23, 2011
Messages
1,447
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 20, 2004
Messages
3,301
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,996
Messages
5,639,448
Members
417,090
Latest member
schoelleya

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top