Spreadsheets: Grades Lab13 Dec 2007

1.This exercise requires the Master.xls file. Retrieve Master.xls from: J:\Instruct\Schaefer\CS101\Spreadsheets\Master.xls or Save as Grd-1 in a Grades subfolder of your H:\CS101\Spreadsheets\ folder.

2.Delete columns I, J and K. Click the mouse on the gray column heading I, drag to K, release. Select Edit, Delete.

3.Go to cell E22 and change Jake Spoon's id number from 0 to 1000. Also change his grades in cells F22:H22 to 48, 45, and 48 respectively.

4.Save Grd-1.xls[This is the same name as used in step 1 above.]

5.Go to cell A1 [Ctrl+Home]

6.We're going to "play" with the sort feature of Excel.

oSelect the range: A1:H32 Click Data, Sort

oIn the top Sort By box, select Column C, Ascending

oIn the second box, select Column B, Ascending

oClick No Header Row, OK

The data should now be sorted by last name within city.

7.Sort again, but sort first by Col.D, and then C. After OK, the data should be sorted by city within phone number. Note that Call, Deets, and McCrae have the same phone number, and they live at the same ranch, Lonesome Dove.

8.Delete columns C and D. See task 2 above...... Save as Grd-2.xls

9.Move A1:F32 to B5: Select range A1:F32. Position the mouse pointer to an edge of the selected range so it becomes. Press the left mouse button and drag the outline so that cell A1 is over cell B5, release the mouse button.

10.Set the column Widths A through H respectively, "W7" means the column is of width7. We're assuming a default font of Arial, 12 point, bold.

A / B / C / D / E / F / G / H
W7 / W9 / W14 / W5 / W5 / W5 / W5 / W7

11.Create the following labels (note the formatting of the date):

A / B / C / D / E / :. / H
1 / CS101-x / YourName
2 / =Now()Format, Cells, Number, Custom. In the Type panel, type d mmm yyyy
3 / Name / Name
4 / Gender / First / Last / ID / Grades / Average

12.Save again as...... Grd-3.xls

13.Starting in cell A5,in the column labeled Gender, next to each name, enter an F for each female, and M for each male. Set Caps Lock to make this easier. Please note that "Woodrow" and "Augustus" are males. Save.

14.Using the column to the right of the grades, H5, enter a function to calculate the Average of the three grades for the first person; =Average(e5:g5) "E5:G5" in the parentheses of the Average function is an example of a range. Copy this function down to find the Average for each of the remaining persons. The range will be adjusted automatically for each row!
...... Save as Grd-4.xls

15.Data, Sort, My list has Header row, the data, A4:H36, withGender as the primary key, Ascending, and theAverage as the secondary key, Descending.

16.Find and label 3 additional averages:

Label:Function

D38: Female AverageH38:=Average(range) [Determine it]

D39: Male AverageH39:=Average(range) [Determine it]

D40: Class AverageH40:=Average(E5:G36)

17.Format the average column, H, as Number with two decimal places. Select H5:H40 FormatCells,Number with 2 decimal places.

18.Save again as...... Grd-5.xls

19.File, Print Area, Set Print Area:A1:H40. File, Page Setup; Sheet: Gridlines on, Row & Column Headings; Page: fit to page, OK. Preview to check. Grd-Last.xls

20.Select all the active cells. Tools, Options,View, Formulas, OK.

Page Setup; Sheet, Gridlines on, Row & Column Headings; Page:
fit to page, OK. Preview to check column widths. (See next page.)
Save as...... Grd-Formulas

jas::D:\CS101\Spreadsheets\Grades\Grades.doc- 1 -

Spreadsheets: Grades Lab13 Dec 2007

Excel Grades Lab Notes

To guarantee that all material in the sheet is printed out:

  • Click on File
  • Click on Page Setup
  • Click the Sheet tab

  • Check the cell range in the Print area. If it is empty, or does not cover the range A1:H40, then change the range to match these values.
  • The simplest way is to click, or select, the area within the Print area: box, delete the current values, and type in the range A1:H40, and press OK.
  • You can also click on the "range box" and then click and drag over the desired range in the sheet.
  • When you have the necessary range selected, click the range box that is present in the small Page Setup - Print area box:
  • You can also experiment with File, Print Area, Set Print Area to ensure that all the cells you want printed are represented in the print range.

jas::D:\CS101\Spreadsheets\Grades\Grades.doc- 1 -

Checklist: Grades LabLab date ______

File Dates______

NameLate Points______

_____1.Change Jake Spoon's grades and save again (same name)...... Grd-1

_____2.Delete columns C and D and Move range A1:F32 to B5...... Grd-2

_____3.Set column widths as per step 10.

_____4.Enter label information in step 11.

_____5.[2] Use Now function and format the date (=Now(), d mmm yyyy )...... Grd-3

_____6.[2] Enter gender designator, M / F, next to each name.

_____7.Function average in H5 and copy down to H36...... Grd-4

_____8.[2] Select A4:H36 and Sort by descending Average within ascending Gender.

_____9.Add labels for female, male and class averages in rows D38:40.

_____10.[2] Select the correct ranges for the female and male averages in rows H38:39.

_____11.Format all averages as numbers with 2 decimal places...... Grd-5

_____12.[2] Page setup, gridlines, B/W, row/col headings, fit to 1 page...... Grd-Last

_____13.Select Tools, formulas.

_____14.Page setup, gridlines, B/W, row/col headings, fit to 1 page...... Grd-Formulas

_____15.Turn-in printouts of Grd.Last.xls and Grd-Formulas.xls

_____16.Turn-in Checklist with your name written on it.

_____17.[2] Send Grd-Last.xls and Grd-Formulas as attachments to

____/23 Total

Send to: wo files: Grd-Last Grd-Formulas as attachments

Include in your folder: Checklist with your name filled-in; printouts of
Grd-Last and Grd-Formulas

jas::D:\CS101\Spreadsheets\Grades\Grades.docModified 1 Apr 2008