Excel Practice

Microsoft Excel

Preliminaries

This practice lab will demonstrate the use of a spreadsheet to solve a simple, real problem.

Goals

1. Using Microsoft Excel
2. Let Excel perform tedious calculations for you
3. Build a spreadsheet that will calculate you can add to to calculate your CS115 grade for you, just fill in your grades.

To Do

1. Start Microsoft Excel

(As described in class)

2.Part 1: Develop a spreadsheet to solve the following problem:

You are a teaching assisant for CS115 and the professor has asked you to devise a spreadsheet to calculate the final grade for each student in the class.

The make up of the grade is as follows:

• 3 in class exams:
  • The highest semester exam is 20% of the final grade
  • The median semester exam is 15% of the final grade
  • The lowest semester exam is 10% of the final grade
• The average of 2 projects is worth 30% of the final grade.
• A cumulative final exam is worth 20%.
• The average of 2 homework assignments is worth 5% of the final grade.

1. You should make up appropriate labels for all columns. The column headings should be in a font size of 14 and bold. Row 1 column A should contain your name. Row 2 column A should contain a title for the spreadsheet.

   Column headings should appear in row 3 and the data for the first student should appear in row 4.

2. Exams, homework, labs, and final make up the columns with students making up the rows. Student name should appear in column A, followed by the three exam grades, followed by the two project gades, followed by the two homework grades, followed by the final exam grade. Your calculation of the final numerical grade should be in Column J.

The formula for calculating the final numerical grade is very long. Notice that your formula has to be able to determine the average of your projects and homework and the largest, smallest, and median of your exams. The formulas for calculating average, minimum, and maximum appear below.

Median is calculated by: =median(first_cell_in_range:last_cell_in_range)

For the first student, you will need these partial formulas in your formula that appears in Column M:

• Projects: .3*average(e4:f4)
• Homework: .05*average(g4:h4)
• Highest exam: .2*max(b4:d4)
• Lowest exam: .1*min(b4:d4)
• Median exam: .15*median(b4:d4)

You will build a formula on column J that contains each of the partial formulas listed above added together and added to 20 percent of the final exam which appears in column I.

Before you start grumbling about the long tedious formula for each of the students, Remember, you only have to enter it once. You can then just drag it through the other students and excel will appropriately adjust the the row part of the address. What type of addressing is this an example of?

Save your spreadsheet. Instructions are given later on how to print it.

3. Part 2: Add a lookup table to compute final course grade

You are going to add a lookup table to compute your final grade.

Per the course outline, grades are distributed on the following scale:

• >= 93 , A = 4.0
• 87-92, B+ = 3.5
• 81-86, B = 3.0
• 75-80, C+ = 2.5
• 69-74, C = 2.0
• 63-68, C- = 1.5
• 57-62, D = 1.0
• < 56 , F = 0.0

Your course grade is going to appear in column K. Add a column heading for the course grade.

You must define an array (table) in your spreadsheet in which excel will look up the final numerical grade to determine the letter grade. The table is to be defined in columns L and M beginning in row 4.

To define the table, Column L should contain the following entries: 0, 57, 63, 69, 75, 81, 87, 93. This data represents the first grade in each course-grade range.

Column M should contain the following entries: 0.0, 1.0, 1.5, 2.0, 2.5, 3.0, 3.5, 4.0.

The final numerical grade for the first student appears in Row 4 Column J. The entry in Row 4 Column K (the entry that will assign to this cell the course grade) should be : =VLOOKUP(J4,L$4:M$11,2) . Once this entry is made for the first student, it should be copied to the other students. In this function, we are using relative and absolute referencing. You should understand the concepts as well as the concepts of the table lookup. If you do not, NOW is the time to ask. These are very important concepts.

The syntax of the VLOOKUP function is as follows:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) where

lookup_value is the value to be found in the first column of the array

table_array is the table information in which data is looked up

col_index_num is the column number in the table array from which the matching value must be returned.

Notice that we have omitted the fourth parameter: range_lookup. If this value is omitted, an approximate match is made rather than an exact match. So, if an exact match is not found (we want a range), the next largest value that is less than look_upvalue is returned. In other words, suppose 64 is the lookup value. 64 does not appear in the table. The next largest value that is less than lookup_value is 63, so the entry 1.5 is returned.

In the three rows following the last student (in the appropriate column), you should calculate the average, the minimum, and the maximum for each quiz, project, and final.

Average is calculated by: =average(first_cell_in_range:last_cell_in_range)

Minimum is calculated by: =min(first_cell_in_range:last_cell_in_range)

Maximum is calculated by: =max(first_cell_in_range:last_cell_in_range)

Save your spreadsheet under a different name than you saved part 1. Use save as to do this.

4.Data for both parts:

Fortunately for you, this is a very small class. There are only 9 students.

Their grades are as follows:

Name	Exam1	Exam2	Exam3	Lab1	Lab2	Hw1	Hw2	Final
Jack Tripper	90	88	76	100	98	87	73	89
Janet Wood	93	86	76	95	94	100	92	82
Chrissy Snow	78	82	90	100	84	100	95	90
Helen Roper	100	54	78	88	82	94	85	92
Stanley Roper	64	72	80	91	82	90	67	89

5.Before coming to class on 2/9:

1. Enter all of the data into the spreadsheet
2. Attempt to write the formulas as described
3. Print out the work that you have done
4. Be sure you have this file with you, either on your server space or on your diskette. Without this work, you will not be able to keep up with the pace of the class

At the beginning of class on 2/9

Submit the print out of your work. This will be graded on a pass/fail basis and the result averaged in with your homework assignments. This is to ensure that all students come to class prepared. If you have entered all of the data and attempted the formulas (they do not have to be right, you will receive 100. If you have not entered the data or attempted the formulas, you will receive a 0.

In the real lab, you will be asked to submit the following:

1. Print your spreadsheet and hand it in.
   
2. Display the formulas in your spreadsheet and print the spreadsheet with the formulas. The way that you do this is to click on Tools on the main menu then click options. Select the view tab and check formulas . After doing this print the worksheet. You can then follow the same pocedure to uncheck formulas to restore the worksheet.

6.You should save your worksheet in your server space or on diskette.

[CS 115 Home Page] [CS 115 Lab Page] Maintained by Barbara Bracken Last Modified 2:58 PM 1/18/04