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.

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

G5: =c5*.3+d5*.3+e5*.3+f5*.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.

C10: =(c5+c6+c7+c8)/4

D10: =sum(d5:d8)/4

E10: =average(e5:e8)

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: Weights

C12: .3

D12: .3

E12: .3

F12: .1

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., $C$12) 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.

G5: =$c$12*c5+$d$12*d5+$e$12*e5+$f$12*f5

7. Make the changes to the cell contents indicated below and notice how the final averages change.

a. D5: 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 D11, 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 #1 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 E11 to cells C11 and D11.

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.

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”,if(g5>59,”D”,”F”))))

b. Copy the formula in cell H5 to cells H6 through H9.

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 (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:

A:GRADESHT.XLS

QUESTIONS

1 Formulas in Excel start with

A. %

B. =

C. +

D. –

2 The cell reference for cell range of G2 to M12 is _______

A. G2.M12

B. G2;M12

C. G2:M12

D. G2-M12

3 What is the keyboard shortcut for creating a chart from the selected cell range?

A. F2

B. F4

C. F8

D. F11

4. Which of the following is not a valid Zoom percentage in Excel?

A) 10

B) 100

C) 300

D) 500

5. Can you set 0.5 inch left indentation for a cell in Excel?

A) Excel does not have indentation feature

B) You can specify indentation only if you turn the rulers on

C) Indentation can be set from Format Cells dialog box

D) The indentation can be specified only when printing

6. You can automatically adjust the size of text in a cell if they do not fit in width by

A) Double clicking on the right border of column header

B) From Format choose Columns and then Autofit Selection

C) From Format Cells dialog box mark Shrink to fit check box

D) All of above

7. What is entered by the function =today()

A) The date value for the day according to system clock

B) The time value according to system clock

C) Today’s date as Text format

D) All of above

8. Special category of Number tab in Format Cells dialog box can be used to apply formats like

A) Zip Code

B) Phone Number

C) Both of above

D) None of above

9. Where can you set the shedding color for a range of cells in Excel?

A) Choose required color form Patterns tab of Format Cells dialog box

B) Choose required color on Fill Color tool in Formatting toolbar

C) Choose required color on Fill Color tool in Drawing toolbar

D) All of above

10. When all the numbers between 0 and 100 in a range should be displayed in Red Color, apply

A) Use =if() function to format the required numbers red

B) Apply Conditional Formatting command on Format menu

C) Select the cells that contain number between 0 and 100 then click Red color on Text Color tool

D) All of above

Answers

1 – B / 2 – C /3 – D / 4 – D / 5 – C / 6 – C / 7 – A / 8 – C / 9 – D / 10-B