How To Weigh Averages of Groups of Numbers Based on How Many Numbers in Group

[chefelf]

Hello,

I'm looking for ideas on how to do this. I've looked up Mode, Median, and Weighted Averages and I'm not sure which, if any would work.

Currently I have a spreadsheet of albums by different bands. Each song on each album has a rating and this gives an average for an album. The thing is if an album is relatively short (say has 5-6 songs) and some one of them gets a low rating it brings down the average substantially. Alternately an album with 10+ tracks has its rating barely affected by one low rating.

Can anyone think of a formula or way to weight the albums differently based on how many songs are contained on the album?

Thanks in advance!

 

[C Moore]

Try median, in my mind that's what it is perfect for.

 

[Joe4]

Logically speaking, I am not certain "weighted averages" make much sense here.

You typically use weighted averages if you were taking averages from each album to make one, overall average for the whole musical group (to compare to other musical groups).
In this case, I wonder simply equally average each song (and not the album).

However, if you are just trying to get the average for a single album (to compare to other albums), I think a normal average serves that purpose, and weighted average really don't come into play here. As C Moore suggested, you could also opt to use a Median in this case.

 

[chefelf]

Thanks so much for your replies. Maybe I'm not explaining myself clearly as I'm having a hard time describing it.

I feel like median may not give me the result as I'm looking for as it will just deliver a midpoint and may also be inaccurate as an album with six 5 ratings and one 1 rating will deliver the same as an album with six 1 ratings and 1 5 rating.

I'm wondering if there is some combination of the two (Average and Median) that may work. Or if there was a formula that weighed the value of low ratings lower depending on how many tracks were on the album.

Sorry to ramble on, just trying to figure out the best way to make this calculation. Thanks again for your quick replies!

 

[Joe4]

Please read my previous reply in more detail and comment on what I said.

What exactly are you trying to compare?
Albums to other albums?
Or all the albums from one band/group to all the albums of another band/group?

If you are trying to just compare albums to other albums, I see no reason for any sort of weighting. It does not seem to make any logical sense to do so.
Why would it matter if one album has more tracks than another, if you are simply trying to compare the album, in its entirety, to another album?
A regular average should do the job here.

 

[chefelf]

Hi, sorry for the explanation. Having a hard time putting it into words.

I'm trying to compare albums. Some albums have 3 tracks. Some have 23 tracks. Trying to find a way to balance it out better so that low or high ratings on one track can have a varying weight based on the number of tracks on the album. Just focusing on tracks on each album individually, not all the albums from one group.

Thanks again for your help!

 

[Joe4]

I'm trying to compare albums. Some albums have 3 tracks. Some have 23 tracks. Trying to find a way to balance it out better so that low or high ratings on one track can have a varying weight based on the number of tracks on the album. Just focusing on tracks on each album individually, not all the albums from one group.

But that is already what averaging kind of does! It averages them out compared to the rest of the tracks on the album.
With a "weighted" average, that would be saying that certain tracks count more or don't count as much as other tracks on that same album.
That doesn't seem to make sense to me, does it to you?

Let's walk through an example. Let's say that your rating system is from 1-5, with 1 being "very bad", and 5 being "very good".

Now, let's say that you have two albums

Album 1
Has three tracks
You have rated them 3, 1, and 5, respectively
Average rating then is 3

Album 2
Has five tracks
You have them rated 2, 4, 5, 3, and 1 respectively
Average rating then is also 3

So both albums have the same average rating.
Are you saying that in your mind, they should NOT be rated them same?
If so, please explain which one should be rated higher and why?

 

[chefelf]

I completely understand what you're saying. This is what we've been doing for a while. We were looking for an alternate rating for some albums where a low (1) or high (5) score can have a much greater effect on some albums with fewer tracks. An album with a song that gets a 1 but only has 4 tracks is much more noticeable than an album where one song gets a 1 but it has 20 tracks.

Just trying to figure out a calculation that could balance that based on the number of tracks on an album.

Thanks again and sorry that this is a little convoluted.

 

[Joe4]

I completely understand what you're saying. This is what we've been doing for a while. We were looking for an alternate rating for some albums where a low (1) or high (5) score can have a much greater effect on some albums with fewer tracks. An album with a song that gets a 1 but only has 4 tracks is much more noticeable than an album where one song gets a 1 but it has 20 tracks.

Just trying to figure out a calculation that could balance that based on the number of tracks on an album.

Thanks again and sorry that this is a little convoluted.

I think you are ignoring some important facts about statistics though, with trying to take that approach.
A bad score SHOULD have more of an effect on a small sample size than a large one (and vice versa).

If you look at your example, let's say we have this:

Album 1
4 tracks
Three tracks have a rating of 5, one has a rating of 1
Average rating would be 4

Album 2
20 tracks
Nineteen tracks have a rating of 5, one has a rating of 1
Average rating would be 4.8

The both have exactly one "bad" track, and the rest are great. So, would you rather have the album with 19 great tracks, or the album with 3?
The average rating would say to choose the second, and I think we would all agree with that, right?
So I think that you are trying to accomplish is already "baked in" to the logic with how average works.

If you are trying to look for ways to differentiate between similar ratings, maybe you could also include the Standard Deviation (Standard Deviation in Excel).
Or simply incorporate the number of tracks as your secondary sort (after average), but even that raises issues (if two albums have overall BAD scores, is the album with more tracks really better, as it would have more bad songs?).

If you are still having issues with this, please lay out an actual example, and show us where the average gives you results you think are incorrect.

 

[Joe4]

You actually see this kind of thing play out all the time in sports.

In most sports, the way they rank teams is by winning percentage. So a team that is 9-3 (9 wins, 3 losses) is ranked the same as a team that is 12-4. Both have a winning percentage of .750.
Neither one is punished for playing less games, or rewarded for playing more games (as the old saying goes, all they can do is play the games on their schedule)!

What this "winning percentage" really is is just the "average number of wins they get per contest".
All other things being equal, using this average is probably the best way we have of comparing them (of course, in the real world, all things aren't equal; some college athletics use analytics to rate the level of competition each team plays, but that doesn't really come in to play here, as no single songs are worth anymore than any other single songs).