Lab 3

MP3 players often list their storage space in terms of either how many typical songs they can hold (eg, 1,000), or in how much data they can hold (eg, 20GB). Neither of these measurements is perfect, because each person has different songs of varying length and quality that they like to listen to. Some people may listen to long classical music songs at a high quality to capture the subtle nuances, while others may simply download whatever popular songs they can get their hands on, regardless of quality. Of course, there are many variations in between.

In this lab, we'll calculate how many songs and how much play time you'll get when listening to your favorite genres on typical mp3 players on the market now. We'll only make simple assumptions and assume a flat bitrate. MP3 files can be relatively complex and have many options (stereo, joint-stereo, variable bitrate, etc). Other lossy music formats (ogg vorbis, aac, etc.) should have fairly similar behavior.

Some definitions:

After you're done with the entire lab, call me over and show me your spreadsheet so that you can get credit. If you absolutely run out of time and have to go somewhere, email me the lab so I can look at it later.

Open up Excel and look at the following table (I've based the table on the recommendations from this web site). Choose 2 music genres that most closely match what you like to listen to. If you don't like any of these genres, feel free to make up a genre/bitrate of your own so long as it seems reasonable.

As a side note: most people almost never listen to mp3s with a 320kbps bitrate. Based on my experience, most people are happy and/or use 192kbps or 128kbps.

Now that you've picked the 2 genres and you know the appropriate bitrate for each, go ahead and type that into Excel somewhere. So, a section of your Worksheet might look like this:

Now, we'll have to find out some information about how much data each mp3 player can hold. I've done some of this work. Remember that capacity is not the only measurement of how good an mp3 player is:

Now, choose 2 of the products above (e.g., Zune 80GB and iPod Shuffle 4 GB). If you have your own mp3 player or you prefer one that's not listed, feel free to choose that one (just be sure to get the correct capacity). In Excel, type up the product name and choose one of each product's capacities. Remember to leave off the "GB". In other words, in a cell the capacity should be "80" and not "80GB" or else Excel will get confused by the "GB."

Slightly below the listing of "Genre" and "Bitrate" that you've typed in, make some space and in Excel type up something like this (using your own choices):

This step is purposefully vague so that you will explore and think and struggle : Now, we need to convert the GB capacity into kilobytes because that's how we'll find out how many seconds of music of a given genre each mp3 player can hold. If you can't remember how to convert GB to kilobytes, consult our handy conversion chart (at the bottom of the page). You should do the conversion by using Excel formulas that reference the capacities of each product model you've chosen. This way, if you choose a new capacity, the kilobytes capacity will automatically update. Remember that to do division you should use the / character, multiplication uses *, and that to enter a formula you type =. The final result might look something like this (but will use formulas):

If you're really stuck, remember that a simple formula might look like: =D3*100. You can check your answers using Google. For example do a "search" for: 80GB in kilobytes. It gives the answer as "83 886 080 kilobytes."

Now that you have the kilobytes capacity of each model, we can find out how many seconds each player will hold of each type of music. Since you have 2 capacities and 2 genres, we'll have 4 total play times in all (2 capacities * 2 genres = 4 total possibilities).

Make some space in your Excel work sheet below the capacities. The general mathematical formula (ie, this will not work if you type it into Excel) to go from capacity to seconds using a given bit rate is this:

playtime = capacity * 8 / bitrate

The * 8 part converts the capacity (which is in kilobytes) into kilobits. Remember that the bitrate is in kilobits per second. Think about how that formula can be turned into an Excel formula that you can use in your worksheet.

Part of your Excel spread sheet should something look like this:

Then, go ahead and convert the seconds of playtime for each configuration into minutes by adding a new column and having the minutes cells update automatically based off of the seconds fields.

Now, it's possible we would like to know how many albums of a certain genre of music a certain mp3 player can hold. To do that, we need to know how many minutes are in the typical album. Feel free to disagree, but I'm just going to assume that the average holds 45 minutes of music. This number is separate from the type of music we're listening to and the quality of the music we're listening to.

At the top of the spread sheet in a blank area (you won't need any formulas, just literally type in the following), add the following:

Then, going back to the seconds and minutes area for each model/bitrate choice, add 2 more columns beyond the play time in minutes information. Add the words "Play time (albums):" in one column and then in the column after that create a formula to calculate how many albums that the model/bitrate will hold. Based your calculations off of the minutes field for that configuration and use the average album length field at the top of the sheet.

At this point, you should be able to see how many seconds or minutes or albums each model can hold if it's holding music only of a certain genre. You should be able to change the average album length as well, and see the "Play time (albums):" number update automatically.

The rest of the steps are pretty open ended. I want you do format and change how the work sheet looks. You should do at least 3 of the following (feel free to do more):

• change font colors, sizes, fonts, styles (bold, italic, underline), alignment, etc.
• change cell background colors
• insert some clip art or photos (related to the genres chosen? favorite artists?)
• insert some photos of the MP3 models chosen
• apply borders

You must format all of the numbers in the worksheet to show an appropriate amount of decimal places. For example, most people don't care if a model can hold 26214.333 seconds of music (maybe they only want seconds, not fractions of seconds). Nor do people generally want to know whether an mp3 player can hold 3.45 albums (they may only want to see that it can hold about 3.5 albums). To do formatting, go to the Home tab, and in the Number section you'll see various formatting buttons. Play around with them.

Consider what would happen if you invented/discovered a brand new music format that sounded better than all the others, but used a significantly lower bit rate. In the short term, you'd be happier cause you could fit more music on your player, but in the long term, you'd probably become rich.

Now, you are finished. Show me your lab and then you can leave (or, if you're completely out of time, email me it).

I lied. There isn't any bonus available for this lab, but the word "bonus" probably caught your eye. I found this site which has a single song in multiple mp3 bitrates. It's interesting to see at what level your ears can and can't hear the difference between 2 versions. If you have a musical background and/or good head phones/speakers, it makes it easier to tell the versions apart.

http://invisibleoranges.com/2009/12/can-you-tell-mp3-bitrates-apart.html