Ranking using # Reviews and Mean Rating

S

statsnovice

New member
Joined
Mar 24, 2023
Messages
4
If I have two metrics, - number of reviews and the rating out of 5, and I want to combine them into a single meaningful rating that can rank a list, how can I do that?

For example, let's say I have the following:

BeachName-#Reviews-Rating

Pantai Kaneko Beach-10-4.9 (meaning 10 reviews with a mean rating of 4.9)
Pantai Kelating Beach-517-4.4
Pantai Pusat Beach-913-4.5
Pantai Abian Kepas Beach-156-4.4
Pantai Antap Beach-177-4.6
Pantai Manyar Beach-141-4.3
Pantai Pernama Beach-840-4.3
Pantai Saba Beach-102-4.4
Pantai Masceti Beach-1155-4.4
Pantai Cucukan Beach-1-4
Emerald Dive Spot-2-5
USAT Liberty Shipwreck-896-4.7
Pantai Nusantara Beach-62-4.1
Menjangan Island-137-4.6

I don't have more than grade 5 math.

What Excel formula could I use to combine these two numbers to rank this list by popularity.

I want to do more than say this beach has the most reviews....I would like to adjust that by the review out of five to try and get a better ranking.
 
statsnovice said:
If I have two metrics, - number of reviews and the rating out of 5, and I want to combine them into a single meaningful rating that can rank a list, how can I do that?

For example, let's say I have the following:

BeachName-#Reviews-Rating

Pantai Kaneko Beach-10-4.9 (meaning 10 reviews with a mean rating of 4.9)
Pantai Kelating Beach-517-4.4
Pantai Pusat Beach-913-4.5
Pantai Abian Kepas Beach-156-4.4
Pantai Antap Beach-177-4.6
Pantai Manyar Beach-141-4.3
Pantai Pernama Beach-840-4.3
Pantai Saba Beach-102-4.4
Pantai Masceti Beach-1155-4.4
Pantai Cucukan Beach-1-4
Emerald Dive Spot-2-5
USAT Liberty Shipwreck-896-4.7
Pantai Nusantara Beach-62-4.1
Menjangan Island-137-4.6

I don't have more than grade 5 math.

What Excel formula could I use to combine these two numbers to rank this list by popularity.

I want to do more than say this beach has the most reviews....I would like to adjust that by the review out of five to try and get a better ranking.
Click to expand...
Do you have the standard deviations of the ratings for each beach?
 
This looks to me like another example of engineering rather than mathematical question. It is obvious that when the numbers of reviews for two items are the same then the higher average rating is better, and when the average ratings are the same then the larger number of reviews wins. But how do you compare, say, 5 reviews averaging 4.8 with 1000 reviews averaging 4.3? Personally, I'd prefer the latter, but the point is there is no unique "correct" approach. This is a typical real life application problem: how do you choose the objective function? Once the function is chosen the rest becomes "more mathematical".

Here is an approach I'd try. Decide on a "soft threshold" of number of reviews (for example 25) which I consider as "not too small". Then build a monotone function which is close to 0 when number of ratings is small, value of 0.5 for 25 ratings and gets close to 1 as the number of ratings grow. To get the combined ratings you multiply the original rating by the value o this function. I.e. for the number of ratings [imath]n[/imath] and the rating [imath]r[/imath] your combined rating is equal to [imath]f(n) r[/imath].

One such function could be [math]f(n) = e^{-\frac{a}{x^2}}[/math], where for the threshold of 25 we would use [imath]a \approx 433[/imath]. Here is a graph of such function:
1679663907631.png
 
blamocur said:
This looks to me like another example of engineering rather than mathematical question. It is obvious that when the numbers of reviews for two items are the same then the higher average rating is better, and when the average ratings are the same then the larger number of reviews wins. But how do you compare, say, 5 reviews averaging 4.8 with 1000 reviews averaging 4.3? Personally, I'd prefer the latter, but the point is there is no unique "correct" approach. This is a typical real life application problem: how do you choose the objective function? Once the function is chosen the rest becomes "more mathematical".

Here is an approach I'd try. Decide on a "soft threshold" of number of reviews (for example 25) which I consider as "not too small". Then build a monotone function which is close to 0 when number of ratings is small, value of 0.5 for 25 ratings and gets close to 1 as the number of ratings grow. To get the combined ratings you multiply the original rating by the value o this function. I.e. for the number of ratings [imath]n[/imath] and the rating [imath]r[/imath] your combined rating is equal to [imath]f(n) r[/imath].

One such function could be [math]f(n) = e^{-\frac{a}{x^2}}[/math], where for the threshold of 25 we would use [imath]a \approx 433[/imath]. Here is a graph of such function:
View attachment 35306
Click to expand...
Thank you so much for your reply. I don't have the education to understand it in detail, but I think your approach is what I felt intuitively....give some weight to the number of reviews, with more reviews being more significant, and then apply that to the mean rating.

What I am not capable of is working out what formula I can use in Excel to apply to these two figures to get that outcome.

If in Excel I have two columns, A1 for the number of reviews and A2 for the mean rating, could I impose on you further to suggest a formula I would enter to achieve this outcome?

I understand if you don't have time, but again, thank you for sharing your knowledge to the depth you have - I am very grateful.
 
statsnovice said:
Thank you so much for your reply. I don't have the education to understand it in detail, but I think your approach is what I felt intuitively....give some weight to the number of reviews, with more reviews being more significant, and then apply that to the mean rating.

What I am not capable of is working out what formula I can use in Excel to apply to these two figures to get that outcome.

If in Excel I have two columns, A1 for the number of reviews and A2 for the mean rating, could I impose on you further to suggest a formula I would enter to achieve this outcome?

I understand if you don't have time, but again, thank you for sharing your knowledge to the depth you have - I am very grateful.
Click to expand...
Sorry, but I don't know Excel and, so, I don't know whether the formula I suggested can be implemented in it.
 
statsnovice said:
Thank you so much for your reply. I don't have the education to understand it in detail, but I think your approach is what I felt intuitively....give some weight to the number of reviews, with more reviews being more significant, and then apply that to the mean rating.

What I am not capable of is working out what formula I can use in Excel to apply to these two figures to get that outcome.

If in Excel I have two columns, A1 for the number of reviews and A2 for the mean rating, could I impose on you further to suggest a formula I would enter to achieve this outcome?

I understand if you don't have time, but again, thank you for sharing your knowledge to the depth you have - I am very grateful.
Click to expand...
Hi @statsnovice,

If I understand @blamocur's post correctly then the attached Excel spreadsheet might be what you need.

You can see the "formula" I have used if you click on any of the cells (containing a number) in the fourth column. I tried formatting those values to 2 or 3 decimal places but some are so small that they then just show up as a string of zeros.

I have assumed that @blamocur's formula was meant to be \(\displaystyle f(n) = e^{-\frac{a}{n^2}}\) rather than \(\displaystyle f(n) = e^{-\frac{a}{x^2}}\) but if that assumption is incorrect then I am sure he will (soon) let us know. ?

I presume you know how to reorder the results yourself?

NB: After downloading the attached (.txt) file you will need to change its extension (to .xlsx) so that Excel can Open it, of course.

Hope that helps.
 

Attachments

  • # # Beach Ratings.txt
    10.2 KB · Views: 4
The Highlander said:
Hi @statsnovice,

If I understand @blamocur's post correctly then the attached Excel spreadsheet might be what you need.

You can see the "formula" I have used if you click on any of the cells (containing a number) in the fourth column. I tried formatting those values to 2 or 3 decimal places but some are so small that they then just show up as a string of zeros.

I have assumed that @blamocur's formula was meant to be \(\displaystyle f(n) = e^{-\frac{a}{n^2}}\) rather than \(\displaystyle f(n) = e^{-\frac{a}{x^2}}\) but if that assumption is incorrect then I am sure he will (soon) let us know. ?

I presume you know how to reorder the results yourself?

NB: After downloading the attached (.txt) file you will need to change its extension (to .xlsx) so that Excel can Open it, of course.

Hope that helps.
Click to expand...
Hi The Highlander @the-highlander and thank you so much for taking the time to help in this way.

I am not sure how @mentions work on this forum,. so forgive me for not tagging you and @blamocur correctly.

I will be able to reorder, if by that you mean sort...so that is all good!

And yes, will await @blamocur 's feedback but I am sure that is perfect....

And thank you to both of you kind people for helping me in this way.

Cheers!
 
statsnovice said:
And yes, will await @blamocur 's feedback but I am sure that is perfect....
Click to expand...
I don't think I can provide a meaningful feedback since I don't have Excel nor do I know how to use it. But I believe you can build graphs in Excel, os if you create a list of the function values and plot them against the function argument you should be able to see if whether the scaling function looks right.
 
blamocur said:
I don't think I can provide a meaningful feedback since I don't have Excel nor do I know how to use it. But I believe you can build graphs in Excel, os if you create a list of the function values and plot them against the function argument you should be able to see if whether the scaling function looks right.
Click to expand...
The main concern was whether my re-interpretation of your equation was correct?

That is, was it OK for me to change your:-
\(\displaystyle f(n) = e^{-\frac{a}{x^2}}\)     ​
To:-
\(\displaystyle f(n) = e^{-\frac{a}{n^2}}\)  ?​

If not, then my spreadsheet may have produced spurious results for the OP. ?
 
The Highlander said:
The main concern was whether my re-interpretation of your equation was correct?

That is, was it OK for me to change your:-
\(\displaystyle f(n) = e^{-\frac{a}{x^2}}\)     ​
To:-
\(\displaystyle f(n) = e^{-\frac{a}{n^2}}\)  ?​

If not, then my spreadsheet may have produced spurious results for the OP. ?
Click to expand...
That's right, I meant [imath]n[/imath], not [imath]x[/imath] in there -- sorry for the typo.
 
You must log in or register to reply here.
Top