EVELINER ERG Sheet h at the 1. Enter the me celles main the past w or the form beloww w El Tease kothe head COLUMN und d These AUTOFIT SELECTION C them set the FORMAT and G 2. Enter the fol use of a low and c cess that GR The demonstres the oa cell the c GS: Now copy this soos G G7 and T icken cells to make the active ct Then open the and choose COPY and higher should now appear around the cell New click on el Gandong the per f ect G6 GS are now highlighted this you need to open the EDIT menu pain, but this me sekated the PASTE .. Note that when you copy this time there the w number for the ces change according which he has been copied 3. Enter the below the cel e DO BI 6. Enter the form below l GS and the G6 GS This demonstrates the use of h er . SCSI specie a spreadsheet Notice that when the map whitewe mains the same where the ces change whe n copied SS SS SS 55115 s indicated below and notice how the final averages 7. Make the changes to the cell change 4. Enter the formulas below in the cells indicated. These formulas demonstrate three methods for calculating averages for a column of data. CIO: DIO: EIO: (5+6+7+c8)4 sumd5d84 avenge(e58) 5. Enter the information below in the cells indicated. This will establish the weight each exam is given in a student's final average. Weights B12: C12: DI 2: E12: F12: 6. Enter the formula below into cell G5 and then copy it into cells G6 to 68. This demonstrates the use of an absolute reference" (e.g., SCS12) that points to a specific cell in a spreadsheet Notice that when the formula is copied into other cells, the absolute reference remains the same whereas the relative references change according to the location into which the formula is copied. GS: SeS125Sds 125+SeS125+515125 7. Make the changes to the cell contents indicated below and notice how the final averages change a DS: 74 b. C7: 98 c. C12.25 and E12:35 d. E12:30 and F12-15 8. Just when you thought you were finished calculating final grades, you realize that you forgot someone. Yoknow, that quiet student that always sits in the back of the room. Anyhow, you can start all over or simply insert a new row for the forgotten student a. Move the cursor to row 6 and click once on any cel in this row or the row heading Open the INSERT menu, select ROWS option Notice how a new rows inserted after row 6. Also, check the formulas entered into cells DII, EI1, G5, G7, 68, and G9 have now all changed toccomodate the newly insert row. b. Now that an additional student has been added to your grade book, the formulas used to calculate the averages for Exams and 2 are incorrect this is because these formulas still assume only four grades are to be averaged. To comect this, copy the formula in cel Ell to cells Cil and Dil c. Enter the information below in the identified cells. A6: Linder, Bamy B6: 993-14-9283 C6: 81 D6: 73 E6: 83 F6: 65 d. Notice that the exam averages change when the new student's grades are entered but a final average is not automatically calculated for him. This is because the formula was not copied into that new row. Copy the formula in cell G5 into cell G6. Now your grade roll is completed Your final spreadsheet should look like the one below. 1 IPSC 556: Policy Analysis 2. Spring 1995 EXAM EXAM #1 EXAM 13 94 74 89 90 4. STUDENT NAMES 5. Thomas, Steven 6.Linder, Barry z Alexander, Suzette 8Richards, Billy Joe 9. Rasmussen, Betty STUDENT ID 999-25-5683 993-14-9283 1999-52-6938 998-71-2838 997-74-4447 FINAL PART. AVERAGE 85.9 65 76.8 8091.65 90893 9092.45 9391 971 88 959490 10 11 Averages 92. 2 83894 131 Weights 0.25 0.3 0. 3 0 .15 9. IF statements can be used to automatically assign letter grades to each student a. Enter the following formula H5: if(g5>89,"A", if(g5>79,"B"if(g5>69,"C",ifg5>59,"D",F))) b. Copy the formula in cell H5 to cells H6 through H9. c. The IF command evaluates the first logical test (ie., G5>89). If the statement is true an "A" is assigned. If the statement is false, the next logical statement is evaluated (i.e., G5>79). To place an IF statement inside another IF statement is referred to as 'nesting." Excel allows up you to nest up to seven IF statements. 10. To save the grade sheet onto your diskette, open the FILE menu, select the SAVE AS command, and enter the following name at the blinking cursor in the shaded box AGRADESHT.XLS EXCEL EXERCISE #1: Grade Sheet 1. Enter the information in the spreadsheet below. Be sure that the information is entered in the same cells as given, or the formulas below will not work. B C D E F G A 1 PSC 556: Policy Analysis 2. Spring 1995 FINAL PART. AVERAGE 4 STUDENT NAMES 5 Thomas. Steven 6 Alexander, Suzette 7 Richards, Billy Joe B Rasmussen, Betty STUDENT ID 999-25-5683 999-52-6938 998-71-2838 997-74-4447 EXAM EXAM EXAM #1 #2 3 94 65 939197 92 83 88 95 94 90 90 90 You will notice that when you enter the information in the first column, the text runs over into the next cell. To adjust the size of the column, once all the information is entered for the first column, click on the column heading that is the letter A). Then open the FORMAT menu, select the COLUMN options, and then select the AUTOFIT SELECTION command. 2. Enter the formula below into cell G5 and copy it into cells G6 to G8. This demonstrates the use of a "relative reference" (e.g., C5) that points to the contents of a cell GS: =c53+d5*.3+e5* 3+15.1 Now copy this formula to cells G6, G7, and G8. To do this click on cell G5 to make it the active cell. Then open the EDIT menu and choose the COPY command (a flashing border should now appear around the cell G5). Now click on cell G6 and drag the pointer so the range of cells from G6 to G8 are now highlighted. At this point you need to open the EDIT menu again, but this time selected the PASTE option.. Notice that when you copy this formula into other cells the row numbers for the cells change according to the row into which the formula has been copied. 3. Enter the information below in the cell indicated. B10: Averages 4. Enter the formulas below in the cells indicated. These formulas demonstrate three methods for calculating averages for a column of data. CIO: DIO: E10 "(c5+6+7+c8)/4 sum(d5:08)/4 -average(e5:8) 5. Enter the information below in the cells indicated. This will establish the weight each exam is given in a student's final average. B12: C12: Weights DI2: E12: F12: 6. Enter the formula below into cell G5 and then copy it into cells G6 to G8. This demonstrates the use of an "absolute reference" (e.g., SCS12) that points to a specific cell in a spreadsheet. Notice that when the formula is copied into other cells, the absolute reference remains the same whereas the relative references change according to the location into which the formula is copied, -Sc$12*c5+Sd$12*d5+Se$12*e5+$f512*15 7. Make the changes to the cell contents indicated below and notice how the final averages change. a. DS: 74 b. C7: 98 c. C12:.25 and E12: .35 d. E12:.30 and F12: .15 8. Just when you thought you were finished calculating final grades, you realize that you forgot someone. You know, that quiet student that always sits in the back of the room. Anyhow, you can start all over or simply insert a new row for the forgotten student. a. Move the cursor to row 6 and click once (on any cell in this row or the row heading). Open the INSERT menu, select ROWS option. Notice how a new row is inserted after row 6. Also, check the formulas entered into cells DII, E11, G5, G7, G8, and G9 have now all changed to accomodate the newly insert row. b. Now that an additional student has been added to your grade book, the formulas used to calculate the averages for Exams # and #2 are incorrect (this is because these formulas still assume only four grades are to be averaged. To correct this, copy the formula in cell Ell to cells CII and DII. c. Enter the information below in the identified cells. A6: Linder, Barry B6: 993-14-9283 C6: 81 D6: 73 E6: 83 F6: 65 d. Notice that the exam averages change when the new student's grades are entered but a final average is not automatically calculated for him. This is because the formula was not copied into that new row. Copy the formula in cell G5 into cell G6. Now your grade roll is completed Your final spreadsheet should look like the one below. B C D 1 PSC 556: Policy Analysis 2 Spring 1995 4 STUDENT NAMES 5. Thomas, Steven 6 Linder, Barry 7 Alexander, Suzette 8. Richards, Billy Joe 9 Rasmussen, Betty STUDENT ID 999-25-5683 993-14-9283 999-52-6938 998-71-2838 997-74-4447 EXAMI EXAM EXAM #1 #2 #3 9 4 7489 81 73 91 98 FINAL PART. AVERAGE 90 85.9 65 76.8 80 91.65 89.3 90 92 45 93 95 101 Averages 92.2 83 89.4 12 Weights 0.25 0.3 0.3 0.15 9. IF statements can be used to automatically assign letter grades to each student. a. Enter the following formula. HS: -if(g589,"A",if(g5>79,"B", if(g5>69,"C"if(g5>59,"D","F*)))) b. Copy the formula in cell H5 to cells H6 through 19. c. The IF command evaluates the first logical test (i.e., G5>89). If the statement is true an "A" is assigned. If the statement is false, the next logical statement is evaluated (ie., G5>79). To place an IF statement inside another IF statement is referred to as "nesting." Excel allows up you to nest up to seven IF statements. 10. To save the grade sheet onto your diskette, open the FILE menu, select the SAVE AS command, and enter the following name at the blinking cursor in the shaded box: A:GRADESHT.XLS EVELINER ERG Sheet h at the 1. Enter the me celles main the past w or the form beloww w El Tease kothe head COLUMN und d These AUTOFIT SELECTION C them set the FORMAT and G 2. Enter the fol use of a low and c cess that GR The demonstres the oa cell the c GS: Now copy this soos G G7 and T icken cells to make the active ct Then open the and choose COPY and higher should now appear around the cell New click on el Gandong the per f ect G6 GS are now highlighted this you need to open the EDIT menu pain, but this me sekated the PASTE .. Note that when you copy this time there the w number for the ces change according which he has been copied 3. Enter the below the cel e DO BI 6. Enter the form below l GS and the G6 GS This demonstrates the use of h er . SCSI specie a spreadsheet Notice that when the map whitewe mains the same where the ces change whe n copied SS SS SS 55115 s indicated below and notice how the final averages 7. Make the changes to the cell change 4. Enter the formulas below in the cells indicated. These formulas demonstrate three methods for calculating averages for a column of data. CIO: DIO: EIO: (5+6+7+c8)4 sumd5d84 avenge(e58) 5. Enter the information below in the cells indicated. This will establish the weight each exam is given in a student's final average. Weights B12: C12: DI 2: E12: F12: 6. Enter the formula below into cell G5 and then copy it into cells G6 to 68. This demonstrates the use of an absolute reference" (e.g., SCS12) that points to a specific cell in a spreadsheet Notice that when the formula is copied into other cells, the absolute reference remains the same whereas the relative references change according to the location into which the formula is copied. GS: SeS125Sds 125+SeS125+515125 7. Make the changes to the cell contents indicated below and notice how the final averages change a DS: 74 b. C7: 98 c. C12.25 and E12:35 d. E12:30 and F12-15 8. Just when you thought you were finished calculating final grades, you realize that you forgot someone. Yoknow, that quiet student that always sits in the back of the room. Anyhow, you can start all over or simply insert a new row for the forgotten student a. Move the cursor to row 6 and click once on any cel in this row or the row heading Open the INSERT menu, select ROWS option Notice how a new rows inserted after row 6. Also, check the formulas entered into cells DII, EI1, G5, G7, 68, and G9 have now all changed toccomodate the newly insert row. b. Now that an additional student has been added to your grade book, the formulas used to calculate the averages for Exams and 2 are incorrect this is because these formulas still assume only four grades are to be averaged. To comect this, copy the formula in cel Ell to cells Cil and Dil c. Enter the information below in the identified cells. A6: Linder, Bamy B6: 993-14-9283 C6: 81 D6: 73 E6: 83 F6: 65 d. Notice that the exam averages change when the new student's grades are entered but a final average is not automatically calculated for him. This is because the formula was not copied into that new row. Copy the formula in cell G5 into cell G6. Now your grade roll is completed Your final spreadsheet should look like the one below. 1 IPSC 556: Policy Analysis 2. Spring 1995 EXAM EXAM #1 EXAM 13 94 74 89 90 4. STUDENT NAMES 5. Thomas, Steven 6.Linder, Barry z Alexander, Suzette 8Richards, Billy Joe 9. Rasmussen, Betty STUDENT ID 999-25-5683 993-14-9283 1999-52-6938 998-71-2838 997-74-4447 FINAL PART. AVERAGE 85.9 65 76.8 8091.65 90893 9092.45 9391 971 88 959490 10 11 Averages 92. 2 83894 131 Weights 0.25 0.3 0. 3 0 .15 9. IF statements can be used to automatically assign letter grades to each student a. Enter the following formula H5: if(g5>89,"A", if(g5>79,"B"if(g5>69,"C",ifg5>59,"D",F))) b. Copy the formula in cell H5 to cells H6 through H9. c. The IF command evaluates the first logical test (ie., G5>89). If the statement is true an "A" is assigned. If the statement is false, the next logical statement is evaluated (i.e., G5>79). To place an IF statement inside another IF statement is referred to as 'nesting." Excel allows up you to nest up to seven IF statements. 10. To save the grade sheet onto your diskette, open the FILE menu, select the SAVE AS command, and enter the following name at the blinking cursor in the shaded box AGRADESHT.XLS EXCEL EXERCISE #1: Grade Sheet 1. Enter the information in the spreadsheet below. Be sure that the information is entered in the same cells as given, or the formulas below will not work. B C D E F G A 1 PSC 556: Policy Analysis 2. Spring 1995 FINAL PART. AVERAGE 4 STUDENT NAMES 5 Thomas. Steven 6 Alexander, Suzette 7 Richards, Billy Joe B Rasmussen, Betty STUDENT ID 999-25-5683 999-52-6938 998-71-2838 997-74-4447 EXAM EXAM EXAM #1 #2 3 94 65 939197 92 83 88 95 94 90 90 90 You will notice that when you enter the information in the first column, the text runs over into the next cell. To adjust the size of the column, once all the information is entered for the first column, click on the column heading that is the letter A). Then open the FORMAT menu, select the COLUMN options, and then select the AUTOFIT SELECTION command. 2. Enter the formula below into cell G5 and copy it into cells G6 to G8. This demonstrates the use of a "relative reference" (e.g., C5) that points to the contents of a cell GS: =c53+d5*.3+e5* 3+15.1 Now copy this formula to cells G6, G7, and G8. To do this click on cell G5 to make it the active cell. Then open the EDIT menu and choose the COPY command (a flashing border should now appear around the cell G5). Now click on cell G6 and drag the pointer so the range of cells from G6 to G8 are now highlighted. At this point you need to open the EDIT menu again, but this time selected the PASTE option.. Notice that when you copy this formula into other cells the row numbers for the cells change according to the row into which the formula has been copied. 3. Enter the information below in the cell indicated. B10: Averages 4. Enter the formulas below in the cells indicated. These formulas demonstrate three methods for calculating averages for a column of data. CIO: DIO: E10 "(c5+6+7+c8)/4 sum(d5:08)/4 -average(e5:8) 5. Enter the information below in the cells indicated. This will establish the weight each exam is given in a student's final average. B12: C12: Weights DI2: E12: F12: 6. Enter the formula below into cell G5 and then copy it into cells G6 to G8. This demonstrates the use of an "absolute reference" (e.g., SCS12) that points to a specific cell in a spreadsheet. Notice that when the formula is copied into other cells, the absolute reference remains the same whereas the relative references change according to the location into which the formula is copied, -Sc$12*c5+Sd$12*d5+Se$12*e5+$f512*15 7. Make the changes to the cell contents indicated below and notice how the final averages change. a. DS: 74 b. C7: 98 c. C12:.25 and E12: .35 d. E12:.30 and F12: .15 8. Just when you thought you were finished calculating final grades, you realize that you forgot someone. You know, that quiet student that always sits in the back of the room. Anyhow, you can start all over or simply insert a new row for the forgotten student. a. Move the cursor to row 6 and click once (on any cell in this row or the row heading). Open the INSERT menu, select ROWS option. Notice how a new row is inserted after row 6. Also, check the formulas entered into cells DII, E11, G5, G7, G8, and G9 have now all changed to accomodate the newly insert row. b. Now that an additional student has been added to your grade book, the formulas used to calculate the averages for Exams # and #2 are incorrect (this is because these formulas still assume only four grades are to be averaged. To correct this, copy the formula in cell Ell to cells CII and DII. c. Enter the information below in the identified cells. A6: Linder, Barry B6: 993-14-9283 C6: 81 D6: 73 E6: 83 F6: 65 d. Notice that the exam averages change when the new student's grades are entered but a final average is not automatically calculated for him. This is because the formula was not copied into that new row. Copy the formula in cell G5 into cell G6. Now your grade roll is completed Your final spreadsheet should look like the one below. B C D 1 PSC 556: Policy Analysis 2 Spring 1995 4 STUDENT NAMES 5. Thomas, Steven 6 Linder, Barry 7 Alexander, Suzette 8. Richards, Billy Joe 9 Rasmussen, Betty STUDENT ID 999-25-5683 993-14-9283 999-52-6938 998-71-2838 997-74-4447 EXAMI EXAM EXAM #1 #2 #3 9 4 7489 81 73 91 98 FINAL PART. AVERAGE 90 85.9 65 76.8 80 91.65 89.3 90 92 45 93 95 101 Averages 92.2 83 89.4 12 Weights 0.25 0.3 0.3 0.15 9. IF statements can be used to automatically assign letter grades to each student. a. Enter the following formula. HS: -if(g589,"A",if(g5>79,"B", if(g5>69,"C"if(g5>59,"D","F*)))) b. Copy the formula in cell H5 to cells H6 through 19. c. The IF command evaluates the first logical test (i.e., G5>89). If the statement is true an "A" is assigned. If the statement is false, the next logical statement is evaluated (ie., G5>79). To place an IF statement inside another IF statement is referred to as "nesting." Excel allows up you to nest up to seven IF statements. 10. To save the grade sheet onto your diskette, open the FILE menu, select the SAVE AS command, and enter the following name at the blinking cursor in the shaded box: A:GRADESHT.XLS
