Workshop Tasks
Below is a spreadsheet created in Google Sheets that contains some information about fictional students you may have met with this semester. Most of the information is self explanatory, but for context, assume that there were two events the students were required to attend. You want to see who is most and least organized in arriving at the events, and are offering a prize to the fastest student and will have a conversation with the slowest one, so two columns describe their place in line for the two events.
Your tasks:
- View the spreadsheet while logged into your Oberlin google account, and make a copy (File -> Make a copy) for yourself to work with today.
- Add formatting to the spreadsheet
- Bold headers
- Add borders to cells you're using
- Change the color
- Change the font
- Fill in the place in line information for each students in Column D. (Hint: Use the copy feature, do not type out all 49 rows by hand!!!!)
- Calculate a score for each student in Column F that adds their place in line for the first event to their place in line for the second event. Note to yourself whether the formula should use an absolute or relative reference.
- Use the COUNT function in E1 to calculate the total number of students.
- Use the COUNTIF function in E2 and E3 to count the total number of students in class years 2023 and 2027.
- Calculate the average GPA for each student. Write this by hand as formula, do not use the AVG function.
- Use the IF function in column H to print some text indicating if the student in that row has a GPA above or below average.
- Use the min, max, and vlookup functions to print the name of the fastest and slowest students in B1 and B2. (Hint: You may need to insert a column G that repeats the students' names.)
- Insert a bar chart of the count of students by class year. (Hint: click into cell C6.)
