I got the idea for this tutorial from MiTapes, a music player app for the iPhone/iPad that allows you to create mixtapes, just as you did on cassette tapes back in the old days. With MiTapes, you select a tape duration (30, 45 or 60 minutes per side) and select which tracks you’d like to store on the tape. When the side is "full" a message is displayed.
Having replicated this functionality in Excel (I covered how I did this in another tutorial), I decided to bring it into the 21st century and create a digital version which calculates how much space is taken up by the music tracks on a digital device (i.e iPhone or MP3 player). Instead of using the duration of each track to determine when the tape was full, I used the size of each MP3 file to determine how much space is used on the digital device.
If you want to "play along" with this tutorial, you can download the demo file here.
As you can see from the screenshot below, the spreadsheet contains a selection of music tracks. Each track has a displayed file size (column C). The file size is stored as a text entry (because the data was imported from an iTunes playlist) and therefore a formula is required to extract and convert the size of each file (column D).
The formula in D2 is =VALUE(LEFT(C2,FIND(" ",C2)-1))
This formula extracts a number of characters from C2, starting at the left hand side. The number of characters to extract is determined by the position of the space character in C2 (that is what the FIND function does – it returns the position of the space character and subtracts 1 from it.) We are looking for the space character because that separates the number from the "MB".
So…
4.7 MB returns 4.7 (space is at character 4. 4-1 = 3 so the formula returns the first 3 characters of the cell
4 MB returns 4 (space is at character 2. 2-1 = 1 so the formula returns the first character of the cell
The VALUE function converts a numerical value (that is really text) into a real numerical value that can then be used in a calculation.
The formula in E2 calculates the file size in GB (the device capacity is measured in GB). The formula is =D2/1024
To include a track on the music player device, enter a Y into the appropriate cell in column F.
The formula in I2 which calculates how much space has been used, is =SUMIF(F2:F1001,"Y",E2:E1001). This adds up the values in column E but only if the corresponding cell in column F contains the letter Y.
ps: if you’d like to see a short video review of MiTapes, check out my personal blog