Excel_Assignment 1_Instructions

Excel_Assignment 1_Instructions - Excel Assignment #1 For...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
Excel Assignment #1 For information on how to do any of these processes, search the Excel help or go to www.microsoft.com/office Exercise 1: Student Grade Book (expected time – less than 45 minutes) Learning Objective : Become familiar with aggregate functions (sum, average, standard deviation, max, min, etc.) and complex IF statements. Please download the spreadsheet Excel Assignment 1.xlsx from Blackboard. Click on worksheet E1 (you may be in Worksheet E1 by default). This spreadsheet keeps track of students’ grades. There are two exams – Exams 1 and Exam 2. There are two more columns – Total Weighted Score and Final Grade. Exam 1 is weighted 35% of the total, and Exam 2 is weighted 65% of the total. These weights are in cells B4 & C4. (Hint: you might want to use absolute cell references when referring to these cells.) Please do the following : (a) Find the total weighted score for the semester based on Exam 1 and Exam 2 and their respective weights. (i.e., Exam 1 * Weight 1 + Exam 2 * Weight 2) This formula belongs in the Weighted Total column. (b) Find the average (AVERAGE), standard deviation (STDEV), maximum (MAX), and minimum (MIN) for Exam 1, Exam 2 and Total Weighted Score. (Click on the “Formulas” tab and then select “Insert Function.” Select “Statistical” from the category drop down box.) Put these formulas in cells B40:D43, as appropriate. (c) Assign a grade for each student based on the following rule: If Total >=90, then the grade is an A; between 80 and 89.99 it is a B; between 70 and 79.99 it is a C, otherwise D. Please use an IF statement to calculate the grade. Here you will use a nested IF. That is an IF statement within an IF statement. Place this formula in the Final Grade column for all students. Consider the following hypothetical example. If you have a credit score of above 700, you are “E” (i.e., excellent credit history); between 600 and 699, you are a “G” (i.e., good), otherwise “R” (i.e., Risky). In this situation, the formula would be: A B Credit Score
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 3

Excel_Assignment 1_Instructions - Excel Assignment #1 For...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online