depending on which
class you are in.
Do a write-up of the
kurzweilAI.net news as described in the directions for homework1.
1.) The spreadsheet should include a title in cell C1 which should include your first and last names, the course number and section you are in, and the current semester and year. This will be too big for the cell, but we will fix that when we format later.
2.) Row 2 will be a blank row.
3.) If you have not done so already, center all of the entries in the spreadsheet by first clicking on the blank heading button that is to the left of the heading for column "A" and above the heading for row "1" as described in class. This selects the entire worksheet. Then click on the button on the “Home” tab in the “Alignment” area that centers the contents of cells. If a cell is already centered, doing the above will un-center it, so watch for that. If it does happen, you just click the “center button enough times so that all the cells in the spreadsheet are centered. Please note, this is one of the only times we will format anything in the spreadsheet until we have most of the values and formulas entered. This makes it much easier to complete this homework because we can fill down without dragging different formatting down.
4.) The column headings in row 3 will be "Last Name", "First Name", "ID", "Current Grade" "Test 1", "Test 2", "Test 3", "Test 4", "Test 5", "Test 6", "lab1", "lab2", "lab3", "lab4", "lab5", "lab6", "lab7", "lab8", "lab9", "lab10", "lab11", "hw1", "hw2", "hw3", "hw4", "hw5", "hw6", "Test Total Less Minimum Test Score", "lab Total Less Minimum lab Score", "hw Total Less Minimum hw Score", "Current Grand Total", and "% of Possible Points". Note that you don't put the double quotes into the spreadsheet The above values go in cells A3:AF3 and there are no empty cells in between in this row, so don’t skip cells even if it looks as if the contents won’t fit in the cell.
5.) Row 4 will have the maximum possible points for each assignment. Each of these scores will serve as a column heading for the column it is in. Tests are worth a maximum of 100 points, labs 50 points, and home work assignments are worth a maximum of 25 points. Only fill in the values for the first three tests, the first six labs and the first four homework assignments, the other cells in between will be empty. These numbers will be used in the calculations of the percent of possible points, so if there are no empty cells your percentages may be much too low resulting in low grades for the students in your Gradebook.
Rows 5 through 40 will have entries for students. Use the default font. The values and formulas that you put into these cells will be described in more detail below, but you should use the headings described above in step 3 to help you understand what goes in these cells when you get around to filling them in.
6.) Fill in the first student's last name in cell A5 with the string "last_name_1", then use the fill handle to fill down to row 40.
7.) Fill in the first student's first name with the string "first_name_1", then use the fill handle to fill down to row 40.
8.) Fill in the first student's ID. Use the following function:
=INT(
Fill this function down to row 40. Now highlight the first student's ID. Highlight all the formulas in column C, copy them to the clipboard, and then in the “Home” tab in the clipboard area click on the downward-pointing arrow at the bottom of the “Paste” button and select “Paste Values” This pastes only the numbers so that you will no longer have functions in this column. That way, the ID numbers will not change when the spreadsheet is automatically recalculated.
You will fill in the "Current Grade" column later, after creating the table of Letter Grades.
9.) For the test score for the first test for the first student, put this formula in cell E5 (under the Test 1 heading)
=INT(
This will generate random numbers between 50 and 100. Now fill that formula down to row 40 so each student has a score for test 1 between 100 and 50. Now fill across for tests 2 and 3. Leave the scores for tests 4, 5 and 6 blank. These do not have to be made into constants, you should leave these as formulas.
10.) For the score for the first lab for the first student, put this formula in cell K5:
=INT(
This will generate random numbers between 25 and 50. Now fill the formula down to row 40 so each student has a score for lab1 between 25 and 50. Now fill across for labs 2 through 6. Now each student should have a score between 25 and 50 for the first six labs.
11.) For the score for the first homework for the first student put the constant value 25 in cell V5. Now fill down to row 40 and then fill across to Y40 for the first four homework columns.
12.) For the "Test Total Less Minimum Test Score" put the following formula in cell AB4
=SUM(E4:J4) - MIN(E4:J4)
This will compute the sum of all the student's test scores and subtract the lowest score. Note that before the student's second test has been recorded in this Gradebook the score will still be zero because the lowest test score is dropped, and there is only one test score recorded so far. Now fill down to row 40.
13.) For the "lab Total Less Minimum lab Score" put the following formula in cell AC4
=SUM(K4:U4) - MIN(K4:U4)
This will compute the sum of all the lab scores for this row and subtract the lowest score. Note that before the scores for the second lab have been recorded this total score will still be zero because the lowest lab score is dropped, and there is only one lab score recorded so far. Now fill down to row 40.
14.) For the "hw Total Less Minimum hw Score" put the following formula in cell AD4
=SUM(V4:AA4) - MIN(V4:AA4)
This will compute the sum of all the homework scores for and subtract the lowest score. Note that before the scores for the second homework have been recorded score will still be zero because the lowest homework score is dropped, and there is only one homework score recorded so far. Now fill down.
15.) This instruction is intentionally left blank. Please go on to the next instruction.
16.) Enter the following formula in cell AE4
=SUM(AB4:AD4)
This will compute the student's grand total of points, less the lowest test, lab and homework scores and place it in cell AE4. Now fill down to row 40.
17.) In cell AF4 enter the following formula
=IF(AE$4<>0,AE4/AE$4,"")
This will put an empty string in cell AF4 if cell AE4 contains zero so that we avoid getting a divide by zero error. Otherwise, this formula will put the percent of all the points a student has earned in cell AF4. Why is the dollar sign used here? Now fill down to row 40.
18.) Now we will build a two column lookup table in cells AG6 through AH17 as follows:
Put the label "Letter
Grades" in cell AG5. Now merge and center this in cells AG5 and AH5,
and make it bold. In cells AG6 through cells AG17, put the cutoff points
for grades as a percentage, and in cells AH6 through AH17, put the
corresponding letter grades, as follows:
|
Letter Grades |
|
|
0 |
F |
|
.60 |
D- |
|
.63 |
D |
|
.67 |
D+ |
|
.70 |
C- |
|
.73 |
C |
|
.77 |
C+ |
|
.80 |
B- |
|
.83 |
B |
|
.87 |
B+ |
|
.90 |
A- |
|
.93 |
A |
Now select (highlight) cells AG6 through AG17. Format those cells as percentages by clicking on the percent button (labeled "%") on the formatting toolbar, or use the Format menu if necessary. Select the entire table and put the thickest border around the table (but only around the outside of the table, not in-between the percentages or between the letter grades). Now select the top cell in the table, the one that contains the string "Letter Grades", and put a border around that cell so that the title of the table has its own border. Note that the table in this web page appears to have borders around each cell, but in your spreadsheet, it should only have a border around the outside of the table, and also a border around the cells with "Letter Grades" in them, not between the percentages, the letter grades, or between the two columns of the table. Use the thickest solid line style for these borders.
19.) Now we can display the letter grade in column D. Enter the following formula in cell D5
=IF(AF5<>"",VLOOKUP(AF5,AG$6:AH$17,2),"")
The way the above formula works can be described as follows: If the student in this row does not have an empty string in their percent of possible points entry in column AF, then look up their letter grade in the table in AG$6 to AH$17 and put that grade in the current cell, otherwise put the empty cell in the current cell. Now you should fill down to row 40.
20.) We have to create one more row, and then we'll format the worksheet we've created. In cell C41, enter the text "Column Averages:" as a label for this row.
21.) Enter the following formula in cell E41
=IF(MAX(E5:E40)>0,AVERAGE(E5:E40),"")
This function will put an empty string in the cell if there are no numbers entered in that column yet, and the average of that column otherwise. Fill across to AF41.
Now we format the spreadsheet.
22.) Merge and center cells C41 and D41.
23.) In C41:D41 bold the text.
24.) In C41:D41 change the font size to 12 point.
25.) The title in C1 should be bolded
26.) The title in C1 should be16 point font.
27.) Merge and center cells C1 through M1 (use as many cells as you need for your title). Put the thickest bottom border on the merged selection.
28.) The column headings in A3:AF3 should have a thin bottom border from the FORMAT | CELLS | BORDERS menu selection. Please note, in this entire assignment don’t select an entire row, only select the cells you want to affect. In this case select only cell A3:AF3, not the entire row.
29.) The cells A3:AF3 should be bolded.
30.) A3:AF3 should be 12 point font.
31.) The column headings in A4:AF4 should have the very thickest bottom border.
32.) The cells A4:AF4 should be bolded in 12 point font. Note that there should be scores in this row only for test1 - test3, lab1 - lab6 and hw1 - hw4; the others should be empty.
33.) Now widen the columns as necessary to display a little white space on both sides of each cell in the spreadsheet. Alternatively you may wrap the text, but make sure that no word is broken across two lines. Check the merged cells C41:D41 to make sure you have widened the columns enough so that there is white margin on both sides of the text in the cell.
34.) Highlight cell AF4 and format it as a percent by clicking on the percent button.
35.) Select the range AF5 through AF40 by dragging from AF5 to AF40. Then, in the “Font” area of the “Home” tab click on the small button in the lower right corner to display the “Format Cells” dialog, then click on the “Number” tab. Choose percentage and make sure one decimal place will be displayed. Make sure it’s only one digit to the right of the decimal point for this range of cells.
36.) Select cells E41 through AE41. In the “Format Cells” dialog box (see above), select the "Number" tab at the top of the dialog box. In the "Category" selection box, select "Number". Use the up and down arrows on the "Decimal places" up-down control so that the number "2" is displayed in that up-down control to insure that exactly two digits will appear to the right of the decimal point in the row you selected in the worksheet. Now give the entire selected region (not the row, only the selected region) a top border by clicking on the Format menu, then the Cells... submenu, then the Borders tab, and selecting the top border.
37.) Select cell AF41. Format it as a percent. In the “Format Cells” dialog box, select the "Number" tab. Select the "Percentage" category and make it so "2" is displayed in the "Decimal places" up-down control. While the format cells dialog box is still open, click on the borders tab at the top of the dialog box and give this cell a top border like the rest of the averages have.
38.) Double check everything. Turn it in as described previously near the top of this web page.