12/14 Computer Lab Activity: New spreadsheet ideas of the day are:  1) I can copy data from other sources into my spreadsheet including special delimited data strings and convert them into columns using the Split function, and 2) I can sort data in my spreadsheet using the DATA menu tab Sort options.

Perform the following tasks and share the Google Sheets by 12/18.  You will have an hour of class computer time on 12/12 and 12/16.  Use formulas with cell references whenever possible.  Remember to use absolute refererence (F4 key: \$A\$1, \$A1 or A\$1) and =IF(test, true, false)  as needed.

1. Take the data at the bottom of this page and paste it into Google Sheets.  Use the SPLIT function [=split(A1,”, “)] to put the space delimited data into separate columns.  Copy the data and Edit – Paste Special – Paste Values Only to a new sheet.

Name : Thomas, John      McCullum, Alicia
Exam #1:   82                   65
Exam #2:   75                   79
Exam #3:   81                   84
Paper:         87                 92
Participation: 94                65

3. Calculate the final average for each student. The three exams should each count 25%
of the final average, the paper should count for 15%, and participation should count for
the remaining 10%.  Make the weighted amounts easily changeable so the grades can be
recalculated to view different scenarios. (Hint: use absolute references)

4. Calculate the class average for each exam.

5. Create a chart that shows the grade distribution for the final average. (A=90-100;
B=80-89; C=70-79; D=60-69; F=below 60)

6. Format the table and charts with borders, colors, etc.  Sort the data alphabetically and share the sorted file (with all the work listed above) to mike.myers@bethel.k12.or.us

Data (comma and space delimited file):

Last First Exam1 Exam2 Exam3 Paper Participation
Stevens, Tony 64 70 73 85 75
Rivers, Tina 70 77 88 95 90
Beal, Jenny 77 83 79 88 80
Calhoun, Richard 69 43 81 78 75
Nixon, Rachel 91 90 86 95 88
Freeman, Ralph 44 26 54 78 80
Copely, Roy 61 67 70 85 80
Kimmett, Roger 77 85 86 85 85
Allen, Renae 83 86 92 85 88
Victor, Isaac 97 80 82 85 88
Albright, Alan 69 69 50 85 75
Bredder, Richard 96 92 92 98 85
Gaby, Brad 95 89 89 95 85
Hendrix, James 91 84 92 95 85
Fonzerelli, Arthure 87 79 84 85 80
Cunningham, Richard 76 73 80 82 80
Van Dyke, Robert 84 75 78 88 80
Thompson, June 82 84 78 88 85
Ashe, David 70 41 57 73 70
Pepper, Claude 79 84 77 82 80
Lemeiux, Felix 84 67 78 88 80
Regan, Rhonda 97 93 92 92 85
Walton, Christine 87 73 82 90 88
Census, James 65 38 84 85 85
Richardson, Natasha 79 75 85 75 75
Montana, Joseph 71 80 78 88 90
Hindes, Martina 96 80 96 98 95
Levert, Christine 98 95 96 98 95
Tucker, Peter 87 78 86 82 80
Blackwood, Rita 59 42 54 75 75
Mahler, Blake 84 92 93 90 90
Black, Susan 51 67 76 78 80