Spreadsheets

Software tools exist for organizing data (spreadsheet and database) and presenting information (documents). Microsoft Office must be purchased; both LibreOffice and OpenOffice are free and run on many platforms; Google Documents provides office applications that can be shared.

This assignment will give you practice in using a spreadsheet to organize data. You may use either Microsoft Office (available in all computer labs on campus), Google Documents or any open-source software you have on your personal computer.

  1. Microsoft Tutorials (2010)are here. It requires that you download their tutorial viewer. The 2007 tutorials are sufficient and don't require you to download anything.
  2. Google Documents Tutorials are at google docs. There are also several youtube tutorials -- google for them.

Excel (Microsoft) and Calc (LibreOffice and OpenOffice) and (Google) Spreadsheet work pretty much the same. This means if you can use one of them it's fairly easy to figure out how to use the others. And, when all else fails, there's Google.

Steps

I've created an Excel spreadsheet. It contains the nutritional composition of some foods (taken from Laurel's Kitchen (1986)).
Download this file and open with a spreadsheet application.

The first row of any spreadsheet is typically used for "column headers". A column header defines what type of data is stored in that column.

Each row of a spreadsheet should be thought of as a "record". It represents a single item in the spreadsheet that has several attributes: those data items that are defined by the column headers. The first column should be reserved to contain a unique value. In the case of our nutrition spreadsheet, the name of each food item is unique. There are no duplicate values in the first column.

When possible, ALWAYS arrange data in your spreadsheet in this way.

Sorting Data

This spreadsheet is sorted on column A (by food name). This can be useful when you want to look up a particular food.

  1. Sorting a single column:
    1. Using the mouse, select the rows to sort. (Don't select the column name.)
    2. From the menu bar, select Data | Sort.... You can sort up to three levels.
  2. Sorting multiple columns:
    1. First on the food category column, and then on the food column.
  3. Your chance for creativity:
    1. Sort this table in some other interesting way.
  4. Notice you can always UNDO what you've done.

Formatting

Rows, columns and individual cells can all be formatted. In Excel, click the Format Button. Notice how you can resize rows and columns, hide and show columns, and format the contents of cells.

  1. Format the cells that contain numbers so that that all the numbers have two digits to the right of the decimal point.
  2. Format all the columns so that they have the same width.

Performing Calculations

There is a column with the heading "calories, calculated". Let's write a formula to determine how closely the the listed calories correspond to the calories we calculate.

There are approximately 4 calories per gram of carbohydrate and per gram of protein; 9 calgories per gram of fat.
The calculated calories for each food is 4*carbs+4*protein+9*fat.

How do we know which operations are done first? Multiplication or division? We hope that the spreadsheet performs multiplication before addition, but to be absolutely safe, we can always use parentheses!

  1. Click the mouse in cell J2.
  2. Type the equals "=" sign
  3. Type the following: 4* (click the mouse in cell D2) + 4* (click the mouse in cell F2) + 9* (click the mouse in cell G2). Hit Enter.
  4. Calculate the calories for the whole column: Right cell J2 to copy. Mark all the cells in the column to copy to (hold mouse button down), then paste. Examine each cell. Notice how the spreadsheet has copied the formulas to pertain to the cells in each row.

Here's a challenge for you! Instead of putting the 4 for carbs and protein, and 9 for fat in the formula, define these values using 6 cells: use three for titles, and the other 3 for the values. Putting them in rows underneath the nutritional data.

  1. Use the cell references for the values for multiplication instead of the literal numbers in the first row for the calculation (J2).
  2. Copy and paste the formula into that column for the other rows. Do the values calculated look the same as before?
  3. Look at the formulas that were calculated. What happened?
  4. Google: Excel relative and absolute cell reference
  5. Make the appropriate changes in your spreadsheet.

Charts

  1. In the column named "calories, difference", calculate the difference between the listed calories and the calculated calories.
  2. Select all the cells except for the last 2 columns. Insert a chart. Select something like a bar chart. It doesn't look very good. Edit the chart - reverse the x and y plot. Does it look any better? What else can you do?
  3. Pick some columns in your spreadsheet to chart. Add columns if you like. Make it interesting!

When you are finished...

Show me your updated spreadsheet. Save your updated spreadsheet AND save it as a pdf file. To get your pdf file to look "good",

Upload the excel and pdf files to moxie into your isc110 folder.

Edit your index.html file (in your isc110 folder). Write a paragraph that describes the assignment AND what you did with the nutrition spreadsheet. Place this paragraph either before or after the hyperlinks. Make it visually pleasing.

It should look like this:

    <li>Nutrition Spreadsheet Assignment:</li>
       <p>Make sure to write a paragraph about this assignment</p>
       <ol>
       <li><a href="nutrition.xlsx"> (Excel file) </a></li>
       <li><a href="nutrition.pdf"> (pdf file) </a></li>
       </ol>

How do you create a pdf file from an Excel spreadsheet file? Make sure that the entire spreadsheet fits on onepage. (There is a way to accomplish this in Excel.)

MAKE SURE your links work AND that the files look ok!!