Exercise I - Basics
- Open whichever spreadsheet application you have on your laptop, and enter the data in the table below into a new sheet.
|
Name |
Age |
Weight (lbs) |
Height (in) |
Weight (kg) |
Height (meters) |
BMI (kg/m2) |
|
Joe |
45 |
272 |
72 |
|
|
|
|
Jane |
37 |
165 |
65 |
|
|
|
|
Sarah |
55 |
223 |
64 |
|
|
|
|
Tom |
61 |
201 |
62 |
|
|
|
|
Ayesha |
48 |
193 |
68 |
|
|
|
|
Sally |
41 |
289 |
63 |
|
|
|
|
Dorothy |
29 |
213 |
70 |
|
|
|
|
James |
38 |
188 |
66 |
|
|
|
- Format the column titles so that the text for each title is centered and boldfaced.
- Single Cell: First, select cell A1 and click "B" on the toolbar or, from the Home menu, click "B" in the font selections.
- Format entire row: Next, click on the grey "1" for row #1. This should make the entire row become "selected", and it should appear dark. With row 1 (column titles) selected, click on the dark capital letter B on the toolbar. This should make all the column titles bold-faced. Then click on the centering icon.
- Change the Name of the worksheet to "BMI Data"
The worksheets at the bottom of your screen have titles. The default names are "Sheet 1", "Sheet 2," etc. You can change the names by double-clicking on the worksheet title to select it, and then typing in a new name.
- Freeze Titles (Window, Freeze Panes)
- Use the scroll bar at the right side of the window to scroll down. Note that the column titles disappear.
- In Excel 2010 you can freeze the column titles at the top, so they are always visible. First place your cursor in cell A2 to indicate that you want the cells in the first row to freeze. Then click on "View", then select "Freeze Panes". Note that when you do this the line between rows 1 & 2 becomes a little bit darker.
- Now scroll down and up again, noting that you can see the column titles at all times.
- Adjust the column width so that all of the column titles are accommodated. To do this, click on a vertical line between two column letters, e.g., between column "C" and column "D". Click and hold and move the mouse right or left to expand or reduce column width.
- Compute Joe's weight in kilograms (kg) in column E, but first add a comment to cell E1.
- Put your cursor on cell E1, and click on "Review", "Add Comment". When the comment box opens, type in "1 lb. = 0.453592 kg".
- Then place your cursor in cell E2. Enter "=C2*0.453592 and hit the Enter key. You should get a weight of about 123.377 kgs. in cell E2.
- Compute Joe's height in meters .
- Now add a comment to cell F1 as follows: "1 inch = 2.54 cm; 100 cm =1 meter".
- Then place your cursor in cell F2 to compute the height in meters based on the subject's height in inches. Since 1 inch =2.54 centimeters, and 100 centimeters = 1 meter, Joe's height in meters can be computed by entering the following into cell F2:
=D2*2.54/100
This should give a result of 1.8288 meters.
- Fix the number of decimal places for cells F2, G2, and H2 at 2 decimal places, i.e., hundredths. Click on F2 and hold the left mouse button down, and move to the right to select these three cells. Then release the mouse and click on "Home" from the top menu, then toward the right side of the toolbar where it says 'cells', select "Format", then "Format Cells" (bottom of the pop down), then "Number", and then select 2 decimal places. All three cells will be formatted at 2 decimal places, even though there is nothing in cell H2 yet.
- Compute Joe's BMI. BMI is computed as weight in kg. divided by height in meters squared. Put your cursor in cell H2 and enter the following:
=E2/(F2^2)
You should get a BMI of 36.89. Note also that the cell is already formatted to two decimal places.
Note also that the parentheses around (F2^2) are critical, since they indicate that the value of F2 (meters) should first be squared (i.e., raised to the second power), and the result should then be divided into E2.
- Now we will replicate the formulas you created for Joe so that they can be applied to all of the other subjects. First, select cells E2, F2, and G2. Note that the lower right corner of cell G2 is dark. Carefully position your cursor over this corner so that the cursor turns into a black "+". Then click and hold the left mouse button and drag your cursor down to the bottom of cell G9 so that the block of missing calculations is outline by a broken line. Then release the cursor. All of the appropriate calculations should have been completed for all subjects, and all cells should be formatted at 2 decimal places.
- Note: If you make a mistake, you can go back a step by hitting the curving back arrow on the toolbar. In fact, you can back up multiple steps. If you don't see the curved undo arrow on your toolbar, you may have to add it. There is also a redo icon that can be added.
- Sort the data set by age. Select the entire block of data by placing your cursor in cell A1 and holding the left mouse button while you scroll down to cell G9. Then release the mouse button. The data set should still be highlighted. From the upper menu bar, select "Data" and then select "Sort" which appears toward the middle of the toolbar below the top menu. You can now indicate which variable you want to sort by, and you should select "age", which is in column B. Note that you can sort in ascending or descending order, and you can designate whether or not your data has a "header" row, i.e., column titles. If it does and you check the box to indicate this, then the sort function will use these column titles.
- Use the built in formulas for COUNT, SUM, and AVERAGE. Excel and Numbers (the Mac/iPad spreadsheet) have many built in functions that facilitate computations in many fields (e.g., financial, math, statistical, logical, database management, etc.). To illustrate, you will use our simple data set to compute the number of observations and the sum and average of the numeric variables.
In cells A11 through A15 enter the following labels: Count, Sum, Average, Std. Deviation, Median.
In cell B11 enter the function "=COUNT(B2:B9)".
In cell B12 enter the function "=SUM(B2:B9)"
In cell B13 enter "=AVERAGE(B2:B9)".
In cell B14 enter "=STDEV.S(B2:B9)".
In cell B15 enter "=MEDIAN(B2:B9)".
Now replicate these functions to the right in order to apply them to columns C through G.
Now select rows 12-15 and format them to one decimal place.