Using Excel properly to create artist lists

[Zorg]

So thanks to Moonbeam's wonderful program I've got a nice list of 250 odd albums which are all ranked, which is on Excel now and looks great. Now what I want to do is to use those results to make an ranked artists list, using a logarithmic scale just like AM. So what I'm wondering, is whether excel has some clever way of finding all the albums by say, The Beatles, and then automatically inputting their ranks into a second sheet on excel so that I can then put the logarithmic function in. Or is the work simply tedious, where I have to write out all the artists, find manually all their rankings and then put them into the second spreadsheet myself? Because if it's that manual, aside from being boring, I'd be liable to human error, and I'm hoping that there's some special way to automatically lift it from the album spreadsheet. I'm not tech savvy enough to know, so any help is greatly appreciated! I've been yearning to know what comes out top with this new list.

(Actually I'm pretty sure Bob Dylan would be first, but that's not really the point.)

[Stephan]

Yep, fortunately there is. Here's what I use:

Code: Select all

=IFERROR(INDEX(Albums!$A$2:$C$251;SMALL(IF(LEFT(Albums!$A$2:$C$251;LEN(B2))=B2;ROW(Albums!$A$2:$C$251)-ROW($B$2)+1;ROW($C$251)+1);1);1); $A$111)

You'll have to enter this formula using ctrl+shift+enter because it's a string (it will display the formula in the formula bar with {} brackets around it if you did it right), and this formula will then check the tab 'Albums' within the selection A2:C251 for the first mention of the entry in cell B2 (the artist name) in the current tab. This allows you to simply copy the formula down for however many artists you want (that's why those values are not absolute references). Finally, the A111 cell will have the value that will be displayed if there is no match. The higher you set this value the more important longevity becomes, so I personally have it set to something like 9999.

Then, in the next column you'll want to change one number, almost at the end, into a two, for it to find the second mention of the word in cell B2, again using ctrl+shift+enter:

Code: Select all

=IFERROR(INDEX(Albums!$A$2:$C$251;SMALL(IF(LEFT(Albums!$A$2:$C$251;LEN(B2))=B2;ROW(Albums!$A$2:$C$251)-ROW($B$2)+1;ROW($C$251)+1);2);1); $A$111)

This you can do for as many albums as you want to count in the formula and it'll nicely show the positions of all those albums in the row for that particular artist.

The following formula, which is also used on AM and uses 6 albums (but you can of course use as many as you want) will determine the final album score:

Code: Select all

=(LN(LN(100+C2))+LN(LN(100+D2))+LN(LN(100+E2))+(LN(LN(100+F2))+LN(LN(100+G2))+LN(LN(100+H2))))

You can of course also do this for your songs list and combine the album and song scores into a total artist score (AM uses a 0.4 value for songs and 0.6 for albums iirc).

[Old Forums]

interesting artist list.xlsx

Hmm, I can't get this thing to work, I always get an error of some sort after typing in ctrl+shift+enter. I logged into the Old Forums account, so I can upload my sheet to here, so could you take a quick look at it, to just show me what I'm doing wrong? I'm sure I'm being really stupid, but I've spent about an hour over it, and I really can't get it to work.

[Stephan]

Make sure you have a column of ranks in front of the artists as the formula looks for the artist in row B, not A. That should fix it!

Edit: Added the fixed Excel file.

[Zorg]

Ah, perfect. I still didn't get it to work, but i found the fixed version here. Score!

[Listyguy]

On your list, I noticed that "1999" is on the right and everything else is on the left. It's annoying me.
If you have any desire to fix it, just put an apostrophe (') in front of the title.

[Stephan]

Aligning the text left seems like a better solution.

[Listyguy]

Aligning the text left seems like a better solution.

Well, in excel, putting an apostrophe in front of a number, it views it as test instead of a number. But aligning it to the left would work also.

[nicolas]

Sorry to dig this up, but I would like to do the same with my songs and albums lists. I started with just the albums and tried to modify the formula, because I want to use a 972 albums lists instead of 250.

I managed to change the numbers in the formula but there are some errors in the results. Some bands have the same album rankingd listed twice ??? I can correct that manually

Now if I want to add the songs is it easy?

[Nassim]

Sorry to dig this up, but I would like to do the same with my songs and albums lists. I started with just the albums and tried to modify the formula, because I want to use a 972 albums lists instead of 250.

I managed to change the numbers in the formula but there are some errors in the results. Some bands have the same album rankingd listed twice ??? I can correct that manually

Now if I want to add the songs is it easy?

Should work exactly the same. The biggest potential issue should be duet and featuring, in which case the formula will take none of the artists into account.

By the way, I'd be happy to start an Artist Poll, it's been quite a while.

[Brad]

By the way, I'd be happy to start an Artist Poll, it's been quite a while.

I'm in favor!

[nicolas]

I'm working my way through it and it's not so difficult.
I have also issues with eponymous albums. The formula counts the band AND album names.

About the artist poll that would be a great idea, but I can't run it; the school year to come will be extremely busy for me

[Stephan]

The biggest potential issue should be duet and featuring, in which case the formula will take none of the artists into account.

The formula was designed to check for a match that begins with the same text, so an album by "Bruce Springsteen & the E-Street Band" should still be credited to "Bruce Springsteen". Duets will be an issue though, as only the first artist in the duet will get it credited.

I have also issues with eponymous albums. The formula counts the band AND album names.

That's weird, because the formula should check only column B. I've tested it, and it doesn't do that for me.

[nicolas]

That's weird, because the formula should check only column B. I've tested it, and it doesn't do that for me.

Which part of the formula allows the checking of only column B? i'm not an Excel expert, and maybe i copied something wrong in my own sheet.

[nicolas]

Well, I must have messed up with something cause now the formula gives me 1000 instead of 9999 when it finds no match

It's above my skills. I'm trying to fiddle with formulas that I don't understand.

[Henrik]

Hey mods! Please check our forum.

[nicolas]

???? What do you mean Henrik?
edit : Oh yeah I got it

[nicolas]

It's working now!
Except the eponymous bug that is still here.
There is another bug if you delete a row

[Stephan]

I see now where the problem occurs; the formula I posted in the opening post checks the C column as well. This does indeed lead to it count eponymous albums twice. Sorry about the confusion there. The correct formula is:

Code: Select all

=IFERROR(INDEX(Albums!$A$2:$B$1001;SMALL(IF(LEFT(Albums!$A$2:$B$1001;LEN(B2))=B2;ROW(Albums!$A$2:$B$1001)-ROW($B$2)+1;ROW($B$1001)+1);1);1); $A$199)

I'll try and also explain the formula bit by bit. It's generally easiest to start in the middle since it's such a long string of formulas. This is the central formula:

Code: Select all

IF(LEFT(Albums!$A$2:$B$1001;LEN(B2))=B2;ROW(Albums!$A$2:$B$1001)-ROW($B$2)+1;ROW($B$1001)+1)

The IF function tests something and displays a certain number when it's wrong, and another number when it's right. The logical test is: LEFT(Albums!$A$2:$B$1001;LEN(B2))=B2. What this tests for is a match between B2 and the LEFT-most part of a string in A2:B1001 in the Albums tab with a LENgth equal to B2. This is included to have Artist X and his Band still credited to Artist X. If the test is true (so if there is a match), it displays: ROW(Albums!$A$2:$B$1001)-ROW($B$2)+1

This will display the total number of rows, minus the row in which it encounters a match, +1 (because we have headers). When it's not true it displays a row number for an empty row (ROW($B$1001)+1)), which'll lead to an ERROR (more on that later).

Around that we have the SMALL formula (SMALL(central;n)), which makes the central formula pick the nth time this thing occurs. By changing this value we can have it select the 2nd or third time the central formula finds a match. Around that we have the IFERROR(INDEX( formulas, which are there to prevent Excel from showing an ERROR when the central formula does not find a match. Instead, it will display a value you've told it to. This is the very last number in the great big formula. This number is something you set in your Artists tab.

All the 1001 numbers in the formula above should be a number that's more than the total number of albums in your Albums tab. You could simply make it 1000000 to prevent trouble with this, but this will cause your computer to have a little bit more trouble with the calculations. Again, always ctrl+shift+enter after you've edited the formula.

[Nassim]

You can of course also do this for your songs list and combine the album and song scores into a total artist score (AM uses a 0.4 value for songs and 0.6 for albums iirc).

Shouldn't it be the opposite ?
I feel that since the highest ranked artist should be the one with the lowest score, putting a bigger coeff to albums is in favor of artists with the best songs rank, and I thought we were aiming for the opposite.

For instance with a 5 score on songs and 6 on albums you would get 5.4 and end ranked higher than a band with 6 on albums and 5 on songs (who would get 5.6)

[Stephan]

I think you're forgetting that the song and album scores should be as low as possible as well. So the band with a 5 score on songs and 6 score on albums is more of a songs band and scores (6*0.6)+(5*0.4) = 5.6, while the other band (which is more of an albums band) scores (5*0.6)+(6*0.4) = 5.4 and is thus the better band. That's as it should be, no?

By the way, I don't think Henrik uses these 0.6/0.4 values anymore.

[Blanco]

To be exact, the artist list is based 35.4% on the songs list and 64.6% on the albums list, which reflects the amount of data behind the two sources.

as said here.

Is that the numbers you say? would be something like 0.65/0.35?

[Stephan]

Well, 0.646 and 0.354.

Interestingly, that's pretty close to what I use for own artist list, which is 0.675 and 0.325.

[nicolas]

Thanks Stephan! I'm back from Croatia and I'm gonna take a look at it!