Using Excel properly to create artist lists

Post Reply
Zorg
Unquestionable Presence
Posts: 577
Joined: Fri Feb 10, 2012 1:17 am
Location: London, United Kingdom

Using Excel properly to create artist lists

Post by Zorg » Sun Feb 19, 2012 6:37 am

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.)

User avatar
Stephan
Site Admin
Posts: 913
Joined: Sun Feb 05, 2012 10:34 am
Contact:

Re: Using Excel properly to create artist lists

Post by Stephan » Sun Feb 19, 2012 3:27 pm

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).
Last edited by Stephan on Sun Jul 28, 2013 10:22 am, edited 1 time in total.
Reason: Wrong, check my post further down for a better formula

Old Forums
Archives
Posts: 324
Joined: Sun Feb 05, 2012 1:22 pm

Re: Using Excel properly to create artist lists

Post by Old Forums » Sun Feb 19, 2012 8:17 pm

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.
You do not have the required permissions to view the files attached to this post.

User avatar
Stephan
Site Admin
Posts: 913
Joined: Sun Feb 05, 2012 10:34 am
Contact:

Re: Using Excel properly to create artist lists

Post by Stephan » Sun Feb 19, 2012 8:25 pm

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.
You do not have the required permissions to view the files attached to this post.
Last edited by Stephan on Sun Feb 19, 2012 8:30 pm, edited 1 time in total.
Reason: Update

Zorg
Unquestionable Presence
Posts: 577
Joined: Fri Feb 10, 2012 1:17 am
Location: London, United Kingdom

Re: Using Excel properly to create artist lists

Post by Zorg » Sun Feb 19, 2012 8:42 pm

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

User avatar
Listyguy
Die Mensch Maschine
Posts: 2289
Joined: Sun Feb 05, 2012 8:34 pm

Re: Using Excel properly to create artist lists

Post by Listyguy » Sun Feb 19, 2012 10:59 pm

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

User avatar
Stephan
Site Admin
Posts: 913
Joined: Sun Feb 05, 2012 10:34 am
Contact:

Re: Using Excel properly to create artist lists

Post by Stephan » Mon Feb 20, 2012 6:41 am

Aligning the text left seems like a better solution.

User avatar
Listyguy
Die Mensch Maschine
Posts: 2289
Joined: Sun Feb 05, 2012 8:34 pm

Re: Using Excel properly to create artist lists

Post by Listyguy » Mon Feb 20, 2012 2:59 pm

Stephan wrote: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.

User avatar
nicolas
Moderator
Posts: 1415
Joined: Mon Feb 06, 2012 12:55 pm
Location: Paris area, France

Re: Using Excel properly to create artist lists

Post by nicolas » Tue Jul 23, 2013 9:07 pm

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
Running Up That Hill
Posts: 1924
Joined: Fri Feb 10, 2012 2:35 pm
Location: Paris

Re: Using Excel properly to create artist lists

Post by Nassim » Thu Jul 25, 2013 11:55 am

nicolas wrote: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
Die Mensch Maschine
Posts: 3170
Joined: Thu Feb 09, 2012 10:38 pm

Re: Using Excel properly to create artist lists

Post by Brad » Thu Jul 25, 2013 12:23 pm

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

User avatar
nicolas
Moderator
Posts: 1415
Joined: Mon Feb 06, 2012 12:55 pm
Location: Paris area, France

Re: Using Excel properly to create artist lists

Post by nicolas » Thu Jul 25, 2013 2:36 pm

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

User avatar
Stephan
Site Admin
Posts: 913
Joined: Sun Feb 05, 2012 10:34 am
Contact:

Re: Using Excel properly to create artist lists

Post by Stephan » Fri Jul 26, 2013 7:02 am

Nassim wrote: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.
nicolas wrote: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.

User avatar
nicolas
Moderator
Posts: 1415
Joined: Mon Feb 06, 2012 12:55 pm
Location: Paris area, France

Re: Using Excel properly to create artist lists

Post by nicolas » Fri Jul 26, 2013 7:42 am

Stephan wrote: 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.

User avatar
nicolas
Moderator
Posts: 1415
Joined: Mon Feb 06, 2012 12:55 pm
Location: Paris area, France

Re: Using Excel properly to create artist lists

Post by nicolas » Fri Jul 26, 2013 8:57 am

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

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

User avatar
Henrik
Site Admin
Posts: 5394
Joined: Sun Feb 05, 2012 10:09 am
Location: Älvsjö, Stockholm, Sweden
Contact:

Re: Using Excel properly to create artist lists

Post by Henrik » Fri Jul 26, 2013 9:27 am

Hey mods! Please check our forum.
Everyone you meet fights a battle you know nothing about. Be kind. Always.

User avatar
nicolas
Moderator
Posts: 1415
Joined: Mon Feb 06, 2012 12:55 pm
Location: Paris area, France

Re: Using Excel properly to create artist lists

Post by nicolas » Fri Jul 26, 2013 9:31 am

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

User avatar
nicolas
Moderator
Posts: 1415
Joined: Mon Feb 06, 2012 12:55 pm
Location: Paris area, France

Re: Using Excel properly to create artist lists

Post by nicolas » Sat Jul 27, 2013 6:59 am

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

User avatar
Stephan
Site Admin
Posts: 913
Joined: Sun Feb 05, 2012 10:34 am
Contact:

Re: Using Excel properly to create artist lists

Post by Stephan » Sun Jul 28, 2013 9:10 am

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
Running Up That Hill
Posts: 1924
Joined: Fri Feb 10, 2012 2:35 pm
Location: Paris

Re: Using Excel properly to create artist lists

Post by Nassim » Tue Jul 30, 2013 7:36 am

Stephan wrote: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)

User avatar
Stephan
Site Admin
Posts: 913
Joined: Sun Feb 05, 2012 10:34 am
Contact:

Re: Using Excel properly to create artist lists

Post by Stephan » Tue Jul 30, 2013 1:43 pm

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.

User avatar
Blanco
Rust Never Sleeps
Posts: 693
Joined: Wed Dec 12, 2012 4:25 am
Location: Mexico City
Contact:

Re: Using Excel properly to create artist lists

Post by Blanco » Tue Jul 30, 2013 4:36 pm

Henrik wrote: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?

User avatar
Stephan
Site Admin
Posts: 913
Joined: Sun Feb 05, 2012 10:34 am
Contact:

Re: Using Excel properly to create artist lists

Post by Stephan » Wed Jul 31, 2013 6:54 am

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.

User avatar
nicolas
Moderator
Posts: 1415
Joined: Mon Feb 06, 2012 12:55 pm
Location: Paris area, France

Re: Using Excel properly to create artist lists

Post by nicolas » Tue Aug 06, 2013 8:33 am

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

Post Reply