First, I will be the first to admit none of what I'm about to describe is necessary at all...but I was bored.
I got on a kick of cataloging the DR (Dynamic Range) score for all my CDs, using the http://dr.loudness-war.info/
website. Most were already listed, so it was just a matter of looking them up and making sure I was looking at the correct release. Some weren't there, so I would run the offline meter and upload the report to the website. Again, no real reason for this other than I was bored.
But then I had an idea: what if I used the 5-star rating system in iTunes to "rate" each album by its DR score? I could then, if I wanted, created smart playlists by album rating. Could be fun. But how to map the 20 point DR scale to the 5 point iTunes ratings? Duh - just divide by 4! Well, that *would* work, but I wanted a bit more custom mapping. I wanted a rating of Zero through 5, not 1 through 5. Google Spreadsheet to the rescue!
First, my new scale would look like this:
DR score of 0-3 = iTunes rank of 0
DR 4-5 = 1
DR 6-8 = 2
DR 9-11 = 3
DR 12-13 = 4
DR 14+ = 5
Somewhat arbitrary, but that's what I decided on.
So, in the spreadsheet, I first used conditional formatting to color code the DR scores (so that it's pretty!) I then used a convoluted formula that took me a while to figure out to map the DR score to the iTunes rank. Here's the formula:
=IF(E3<4, 0, IF(E3<6, 1, IF(E3<9, 2, IF(E3<12, 3, IF(E3<14, 4, IF(E3>=14, 5,))))))
Here's what the columns look like in the spreadsheet:
And here's some of the conditional formatting rules to do the color coding:
And here's the formula again (for column G), as it looks in Google Spreadsheets:
I'm now in the process of "rating" each album in iTunes. I shade column G gray once I've rated the album in iTunes, so I know what's been done. I have a long way to go.