Music Charts Question

Hi guys,

I'm terribly sorry if I'm posting in the wrong category. Math has never been a strong suit of mine, so I hope this counts as a statistics question.

I work in the radio industry and use music charts very often, however in most cases they are limited by geography. I'm wondering what the formula or process would be to combine elements of different charts to create a new one.

For example, if I wanted to compile a weekly chart to determine the biggest songs in the world based on several country's existing charts, how would I do that? Just find the average based on how many times a song appears on each chart and at what position? What if I wanted to use songs that fit into a specific genre but appear on general charts around the world? Once I figure out the formula, is there a way to automate it through excel or something like that?

I hope that makes sense. If you need more clarity, please ask.

Thanks in advance for any help. It's greatly appreciated!!

radiogeoff said:

Hi guys,

I'm terribly sorry if I'm posting in the wrong category. Math has never been a strong suit of mine, so I hope this counts as a statistics question.

I work in the radio industry and use music charts very often, however in most cases they are limited by geography. I'm wondering what the formula or process would be to combine elements of different charts to create a new one.

For example, if I wanted to compile a weekly chart to determine the biggest songs in the world based on several country's existing charts, how would I do that? Just find the average based on how many times a song appears on each chart and at what position? What if I wanted to use songs that fit into a specific genre but appear on general charts around the world? Once I figure out the formula, is there a way to automate it through excel or something like that?

I hope that makes sense. If you need more clarity, please ask.

Thanks in advance for any help. It's greatly appreciated!!

Click to expand...

First just the general rating: There are many methods to do that but one which is used quite often is to add up the scores and, if you like, divide by the number of charts which is essentially what you said.

In Excel, one row would be the Chart; Column C row 1 for Chart 1, D1 for Chart 2, E1 for Chart 3, .... Then starting in row 2, column A you have the songs; A2 for song 1, A3 for song 2, A4 for song 3, ... Now the total will go in column B. That is Bj will be the sum of column Cj to xj where x is your last chart column and j = 2, 3, 4, ..., N+1 for your N songs. For example, if you had 6 charts and 10 songs, x would be H and N would be 10. Now enter the data; location ij is the place value for that columns Chart and that rows song.

You could then then use the Excel custom sort function on the block A2 to x(N+1) and sort on column B. If you sort lowest to highest, then you would have your songs rated from 1 [at A2] to N [at A(N+1)]. Each rating period you could either add or decrease the number of rows or replace one song going out with one coming in.

For a particular genre do the same but only allow songs of that genre in the list. If a country had that genre list, you might use that and even give a special weight, say 1/2 or 3/4 or ... the place value since a lower number is better.