In this lab, we'll be using Excel1). Specifically, we'll crunch some numbers regarding caffeine and caffeine intake and then we'll make some graphs.
After you're done, call me over and I'll give you credit (assuming you didn't make a mistake, in which case, I'll help you fix it).
Go to http://www.energyfiend.com/the-caffeine-database and notice that they've got a nice big table with lots of different name brand drinks, the serving size of each drink, and the caffeine content in a serving.
For this step and the purposes of this lab, you'll want to use Internet Explorer to visit that site. Naturally Firefox will work just fine, but Internet Explorer does something extra that Firefox doesn't. This extra step involves slightly more work on our part, but it also involves us learning. So if you're not using IE, load it up now and visit the above site. Also, start Excel.
Going to the caffeine listing site, starting just before the word "Name" in the gray table, click and drag all the way to the bottom right of the table. Copy (ctrl-c) the content and then paste (ctrl-v) it into the top left corner of an Excel spread sheet.
You should notice that some of the data is colored and sometimes even blue. We'd like it to be plain. So, click in a blank cell and on the Home tab, click "Format Painter." This'll copy the formatting of the blank cell and allow us to transfer it to other cells. Now, click in the top left corner of the worksheet AND drag to the bottom right of the table. All the data should become plain again (black and white, etc).
One thing you should notice is that Excel is treating the energy drink called "180" as a number (and is therefore aligning it to the right). We'd like it to appear just like all the other product names. So, on the cell containing the "180" right click it, choose Format Cells, then on the Number tab, choose Text. This'll cause Excel to treat it as text (and therefore left align it) even though it technically is a number. Then, click OK. Auto adjust the columns to fit their data.
Go ahead and replace all the "mg/oz" cells, which are hard coded with data, to instead use a formula to calculate the milligrams of caffeine per ounce of drink. Then, apply formatting on all of the mg/oz data cells to always show 2 decimal places. Apply the formatting necessary to the ounces and caffeine data cells to show always 1 decimal place.
Rename this worksheet to be called "Data".
Select all the data (ctrl-a) then on the Home tab, click "Format as Table." Choose a style/color that you like. Yes, your table has headers (Excel will ask you this). Notice now that you can sort the data by any one of the columns. For example, you can find the drinks with the most/least caffeine per ounce, or the drinks with the most/least caffeine in general.
Off to the side of the data, write down at least 3 facts that you find by sorting various columns. This can just be in sentence form and should say something like "The drink with the most [blank] is [blank]."
Find the drinks (at least 3) that you typically consume in an average week and which also have some caffeine (e.g., "Bawls, Coffee (Brewed), and Snapple Tea"). If you never ever drink any drink on the list, pretend that you drink at least 3. Off to the side, or on another data sheet, make a list of those drinks and their various attributes (size, caffeine, mg caffeine/ounce). The data should reference the original table data, so that, if you change the size of a drink in the table your list will automatically update. Using the names of the drinks and the amount of caffeine/ounce in them, make a pie chart. Give it custom colors to match the drink. For example, Pepsi could be blue, Coca-Cola red, Root Beer could be brown etc).
Now, we'll make a much more complicated chart: a histogram. We'd like to see which concentrations of caffeine (milligrams/ounce) are more common than others.
Make a chart in Excel that looks like the table below. The 1st column will be the boundaries that Excel will use to decide which row a product belongs in. The 2nd column will be the labels that we'll use when we make the chart. The 3rd column we'll have Excel fill in. So for example, if the number 3 appeared in the product count cell for the row corresponding to products having caffine "up to 10" then there are 3 products in our list that have up to 10 mg/oz of caffeine, but no more.
| Up to: | Caffeine (mg/oz) | Product Count |
|---|---|---|
| 10 | Up to 10 | |
| 20 | Up to 20 | |
| 30 | Up to 30 | |
| 40 | Up to 40 | |
| 50 | Up to 50 | |
| 60 | Up to 60 | |
| 70 | Up to 70 | |
| 80 | Up to 80 | |
| 90 | Up to 90 | |
| 100 | Up to 100 | |
| 110 | Up to 110 | |
| 120 | Up to 120 | |
| Over 120 |
Now for the toughest part (which is still pretty easy). Select all the cells (they should be empty) in the product count column. Then, start typing =FREQUENCY( and go ahead and select all of the cells in the mg/oz section of the product table (don't forget the "(" after FREQUENCY). Type "," (comma) and then select all the cells in the "Up To:" column. Don't forget to select the empty one at the end. Hit ctrl-shift-enter. All of the product count cells should be filled in with some numbers. Verify that the numbers look approximately right.
Select the 2 columns, including their headers and then insert a bar chart or column chart (your choice). Adjust the colors / look of the chart. Also, be sure to adjust the vertical axis so that it better fits the data. Change at least 1 thing that we did not cover in class.
Go ahead and raise your hand so I can come check your work. I apologize in advance if you end up waiting for a long time. I'll try my best to quickly give everyone credit, while at the same time making sure everyone was able to complete the lab and learn something.