Class Roster
Open a file from the shared/versteeg/computer
app folder.
The file you open will depend on your class period:
| Computer Applications (merge/center, bold, font style - your choice, size - 16) | ||||||||
| Date (merge/center, bold, same font as row 1, size - 14) | ||||||||
| Males (merge/center, bold, 14, same font style) | Female (merge/center, bold, 14, same font style) | |||||||
| Last Name | First Name | Birthdate | Age | Last Name | First Name | Birthdate | Age | |
| Number of Students | (formula) | (formula) | ||||||
| Average Age | ||||||||
| Oldest Age | (formula) | (formula) | ||||||
| Youngest Age | (formula) | (formula) | ||||||
Formulas:
1. Determine the age of each student by creating a formula and show 2
decimal places in the answer.
2. Insert a formula for the number of students and show 2 decimal places
in the answer.
3. Insert a formula for the average age and show 2 decimal places in the
answer.
4. Insert a formula for the oldest age and show 2 decimal places in the
answer.
5. Insert a formula for the youngest age and show 2 decimal places in the
answer.
Sorting:
6. Sort the female side by age in descending order
(Z-A). Make sure to include last name, first name, birth date and age in
the sort.
7. Sort the male side by age in ascending order (A-Z). Make sure to
include last name, first name, birth date, and age in the sort.
Format:
8. Add rows necessary to include the information in the first four rows of
the table. Look at the picture above and you will see where the rows are
that you have to insert.
9. Column headings - Bold and center
10. Column widths - Last and First Name use auto fit, Birth date and Age use
12, Column E (empty) use 8.
11. Center birthdates and ages
12. Left align last names and first names
13. Format all ages to 2 decimal places
14. Format number of students, average, oldest and youngest ages to 2
decimal places.
15. Landscape the spreadsheet
Conditional Format:
16. Use conditional formatting and change all ages older than 15.49 to
have a yellow fill color and bold font.
CHARTS
17.
Male - Chart #1
Male
first name and age
Chart Type: Line with markers
Move
Chart Location - new sheet Male
Chart Title: Male Students in Class
Axis Titles: use appropriate titles
No legend
Format:
*Chart Title - font comic sans ms-size 16-dark
green
*Axis titles - font comic sans ms-size
14-red
*Change marker shape to any style (not the default) -size 8-any color (not
the default)
*Change line style-any style-color-weight (not default)
18. FEMALE - Chart #2
Female first name and age
Chart Type: Clustered column
Move
Chart Location - new sheet - Female
Chart Title: Female Students in Class
Axis Titles: use appropriate titles
No legend
Format:
*Chart title -font your choice (not default) -size 16- color your choice
(not default)
*Axis Titles -font your choice (not default) - size 14- color your choice
(not default)
*Change column color-your choice. For
example: make them all red or green.