Create Excel Sheet

ASSIGNMENT 2: CREATING A COURSE GRADESHEET IN EXCEL

10pts

OVERVIEW

Your mission is to create a grade sheet for a hypothetical section of Health Science 867. Below, you will find a sample of what your grade sheet should look like when it is done. Please note that on the sample, yellow cells indicate that the cells are for data input. All other cells containing data require either a formula or a function to produce the results. The columns for Lab Assignment, Paper, Midterm, and Final Exam are for the scores each student receives on each of these assignments. At the bottom of each column, you will need to calculate the average (mean), standard deviation, min, and max for each assignment.

Additionally, you will find below the various functions below which you will need to perform the calculations.

For an overview or additional help with Excel, please refer to the online tutorials for Excel that I have placed in the Moodle course.

For this assignment, you will need to do the following:

1. Create a new Excel spreadsheet (create a new one from scratch and do not share someone else’s spreadsheet), using the basic structure in the example below.

2. Create names for each of the hypothetical students and place the names in the range: A7:A19

3. Enter the scores in the yellow highlighted areas of the sample spreadsheet

4. Cells which are not yellow highlighted contain formulas, which must be created using a variety of functions. Please refer to the “Functions You Will Need” section below for the functions and their arguments. If you need additional assistance using any of the functions, please refer to the online tutorials in Moodle, and to the H

FUNCTIONS/FORMULAS YOU WILL NEED

· Date and time - now() - This function returns the current date and time. No arguments are needed. Example: =now() will return the current date and time into the cell

· Summing ranges of cells - sum() - The range of cells to sum go inside of the parentheses

· Standard deviations for populations - stdevp() - The range of cells to calculate the Std Dev goes inside of the parentheses. Example: =Stdevp(a1.a10) will return the standard deviation for the values in cell a1 to a10.

· Mean - Average() - The range of cells to calculate the average goes inside of the parentheses. Example: =average(a1.a10) will return the average for the values in cell a1 to a10.

· Maximum value - Max() - The range of cells to calculate the max goes inside of the parentheses. Example: =max(a1.a10) will return the maximum value for the values in cell a1 to a10.

· Minimum value - Min() - The range of cells to calculate the minimum goes inside of the parentheses. Example: = Min(a1.a10) will return the minimum value for the values in cell a1 to a10.

· (OPTIONAL) Looking up grades in a table - Vlookup() - The arguments to this function are as follows:

1. The value you are looking up (the score)

2. The range of cells containing the score/grade table in which the score will be looked up.

3. The column in the score/grade table containing the grade.

Example: =VLOOKUP(A1, A5.B10, 2) - This example will look up the score contained in cell A1 using a table of grades in A5..B10 (assume that the score is in column A and the letter grade is in column B, which is the second column in the range). Keep in mind that since you will be copying the vlookup() formula to the rows of the other students, you will need to use the dollar sign to make the cell references of the lookup table absolute. Therefore, instead of : =VLOOKUP(A1, A5.B10, 2), you will need to use : =VLOOKUP(A1, $A$5.$B$10, 2), so that the reference to the table doesn’t shift on every row that you copy the formula to.

Weighted Average: Calculating the weighted average requires that you create a formula that multiplies each student’s score for a particular assignment by that assignment’s weighting, and then adds them all together. For example, the formula could be stated as follows:

(Lab assignment score X Lab Assignment weight) + (Paper score X Paper weight) + (Midterm Score X Midterm Weight) + (Final Exam score X Final Exam weight)

Hint: you will need to use an absolute cell reference in order to successfully copy the formula for the first student to the remaining students.

EXCEL HELP AND FUNCTION REFERENCE

To access the Excel Help system, press the Question Mark button in the upper right-hand corner of the screen to launch Help. The Function Reference will provide you a comprehensive listing of the functions available to you, along with what they do and how to set up their arguments.

A FEW HINTS AND TIPS

· Please make sure to use constant/absolute cell references whenever necessary.

Make sure that your spreadsheet contains the same formatting as in the example below. You may “enhance” your formatting if you like, but, at a bare minimum, it must contain bolding, spacing, etc. found in the example.

· The title (Health Sciences 867 Grades) was entered into a merged range. In order to create a merged range of cells, begin by selecting the range (C1 to F1), right-click in the selected range, and then select FORMAT CELLS from the shortcut menu. Next, click on the ALIGNMENT tab and check the MERGE CELLS checkbox and press OK. The selected range of cells will be merged together into a single cell that you can type the complete title into.

· Make sure that all calculations are actually calculated (excluding the letter grade). Simply entering the results into the cell will not earn full credit.

Please make sure to create your own unique spreadsheet and do not share your spreadsheet with another student. Although I encourage you to work together, each student must create his or her own unique spreadsheet. If two students submit the same spreadsheet (yes, I can tell them apart), neither will receive credit for the assignment.