0
$\begingroup$

What I'm looking for is basically a way to create a weighted average (of a $10$-point rating system) in which a rating gains or loses significance based on its distance from $7.$

Detailed explanation:

(please excuse my non-math terminology) I created a database in Access I use to hold my album collection. I rate each song, so I thought I could average out the song ratings to get a calculated album rating. (I discovered, BTW, that one's subjective overall rating of an album is not always congruent with one's ratings of individual songs and can be influenced by things like song order).

I noticed that a mean average did not reflect the way I actually rate albums. That is, if on a $10$-point scale I list every song as a $9,$ I will say the album is a $10$; because if every song is a $9,$ that's one amazing album! In the same way, all $8$s would generally get near a $9.$ If, on the other hand, I rate every song on the album a $6,$ which I translate to decent-but-unexceptional, that's really a pretty crappy album that deserves at most a $5$ overall. An album with all $7$-rated songs is, however, a $7,$ so clearly that's the dividing line for me.

So I used VBA to create a function that would take all the songs on an album and give me a weighted average. It's a very elaborate function I created by futzing around in confusion that only kind of works (for example, sometimes raising a score will actually lower the overall album rating). I can barely explain or even understand my own algorithm, but it basically involves adding a bonus to each rating based on its distance from $7$ (using a variable I kept changing until I got halfway-decent results) and then adding a bonus based on the number of ratings with a bonus.

That is, if an album has ten $7$s and one $8,$ then the $8$ gets a bonus (I'll say $.15$ for this example), so the album total points would be: $7\cdot10+1\cdot(8+.15).$ Although since the bonus is calculated on the distance from $7,$ the bonus would be a calculation, so more like: $7\cdot10+1\cdot(8+((8-7)\cdot.15)).$

The more songs there are above $7,$ the more weight added. So if one $8$ gets that $.15$ bonus, $2 8$s would get (arbitrary example) $.19.$ An $8$ and a $9$ would get something higher still.

In the same way, numbers below $7$ get a penalty.

BTW, I do not cap the resulting rating at $10.$ This means I can have a calculated rating of over $12$ for an album (like Hard Day's Night), but I'm okay with that, as it gives me more information than capping it would give me.

I hope this makes sense and there's some straightforward way to do it. Thanks for your help.

$\endgroup$
1
  • $\begingroup$ You could create 10 different functions (polynomials) for each of the ratings. You'd design them such that the functions corresponding to the low ratings are decreasing, and have decreasing gradient and the functions corresponding to the high ratings are increasing. So for instance for the rating $6$, we'd have $$f_6(1) = 1\\f_6(2)=0.95\\f_6(3)=0.88\\f_6(4)=0.73\\\cdots$$ Then calculate the total rating by computing the sum of $n f_n(x_n)$ where $x_n$ is the number of songs of rating $n$. This effectively punishes having too many $6$'s. You could have steeper functions for ratings further away $\endgroup$
    – John Doe
    Commented Oct 28, 2019 at 21:18

1 Answer 1

Reset to default
0
$\begingroup$

John Doe's comment was just what I needed, but since it's just a comment I can't choose it as the answer. Anyway, I wrote a brief function that gives more consistent results than my old code. It's also much simpler and shorter. numberOf is the number of songs with a rating, Rating is the rating, and bonus_penalty is how much I'm adding/subtracting based on distance from neutral_rating. I simply do this for each rating, add the results for each function call, and then divide by the number of songs.

I believe this is more-or-less what John Doe was proposing, and it appears to work as it should. Thanks!

Function ScoreForRating(numberOf As Integer, Rating As Integer, bonus_penalty As Double, neutral_rating As Integer) Dim adjustment As Double

adjustment = 1 + numberOf * ((Rating - neutral_rating) * bonus_penalty)
ScoreForRating = (Rating * numberOf) * adjustment

End Function

$\endgroup$

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .