Lab 2

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 (illegally?) 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).

Some definitions:

After you're done with the entire lab, call me over and show me your spreadsheet so that you can get credit.

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 its reasonable.

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

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 and that to enter a formula you type =. The final result might look something like this:

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

Remember that the capacity was in kilobytes and the bitrate was in kilobits, so that's why we multiplied by 8. 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.

At the top of the spread sheet in a blank area, add something like this:

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 (related to the genres chosen?)
• insert some photos of the MP3 models chosen (from the Internet)
• 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 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.

Now, you are finished. Show me your lab and then you can leave.