IT Lab 1 – Excel

1.) Open Excel from the Start Menu.

2.) Insert the title “My Grades” into cell A1.

3.) In row 3, start labeling cells across with the following: “Last Name”, “First Name”, “User ID”, “Email”, “Lecture Grade (70%)”, “Lab Grade (30%)”, “Semester Grade”, “Exam 1”, “Exam 2”, “Exam 3”, “Exam Average”, “Exam %”, “Quiz 1”, “Quiz 2”, “Hwk 1”, “Hwk 2”, “Hwk 3”, “Hwk/Quiz Total”, “Hwk/Quiz Grade”, and “Hwk/Quiz %”.

4.) This should take up cells A3 to T3.

5.) Fill out the cells below “Last Name” to “Email” (4 cells: A4, B4, C4, D4).

6.) Now, we’ll work on the Lecture Grade. Put some grades in exam1,2 and 3 (max 125 points); put in some grades for homeworks (max 10 points) and quizzes (max 15 points)

7.) Start with Exam Average. This cell should contain the sum of the exams multiplied by 100 and divided by 375.

i. Click on cell K4 and type in the following formula:

=SUM(H4:J4)*100/375

Press the enter key

ii. You should have your exam average

iii. Change the exam grades and see how your average changes. This is on a scale of 100 and can be compared with the letter grades on your syllabus.

8.) Now we need to get how much of a percentage of your lecture grade this is worth for the “Exam %” field. We do this by entering a formula (as you’ve most likely realized, they all start with an ‘=’) to multiply the exam average by 85% (or .85).

a. Our term will look like “=K4*.85”.

9.) Next we need to sum the homework and quiz grades together under the “Hwk/Quiz Total” cell (R4). Do this using the SUM formula discussed in step 7.

10.) Now we need to multiply that total by 100 and divide by 60 to get our “Hwk/Quiz Grade”. In cell S4 enter R4*100/60

11.) Our next step is to get the percentage of the Lecture grade this is worth. Do this by multiplying the “Hwk/Quiz Grade” field by 15% (or .15). This goes in the “Hwk/Quiz %” cell.

12.) To finalize our Lecture grade we must add the “Hwk/Quiz %” with the “Exam %” and put the result in the “Lecture Grade (70%)” cell (E4). In E4, type +L4+T4 (note you must enter the first plus so that the program will know you are entering a formula and the letter L.

13.) Finally, we need multiply the Lab Grade and the Lecture Grade by their respective weights and add them together in the “Semester Grade” Cell. Enter a lab grade of 80 (we’ll by pass all the work that needs to be done to get this value)

14.) In G4 type in a formula that would give you your lecture grade times 70% plus your lab grade times 30%. Don’t forget to start the formula with the equal mark and the name of the first cell you will be using.

You are finished. Print out the excel spreadsheet or show your document on the screen to your lab assistant.