## Excel Assignment 1

**Unformatted text preview: **1 2 3 4 5 6 7 8 9 10 Sum StdDev Median Mode Max Min count countif sumif 12 small 31 large 43 large 54 large 12 small 23 large 23 large 15 small 23.4 large 12.2 small 248.6 14.19 23.00 12 54 12 10 6 197.4 12 31 43 54 12 23 23 15 23.4 12.2 Round 23.4 23.00 12.2 12.00 CONCATENATE John Smith Roundup Zero Roundup One 24.00 23.4 13.00 12.2 JohnSmith John Smith Item Value 1 2 3 4 5 6 7 8 9 10 Sum Max Min Median Range Average 8 Vlookup Sum (Item*Value) Count If/Then Product 12 31 43 54 12 23 23 15 23.4 12.2 If/Then If/Then/If 23.4 12.2 <13 13=<x<20 x=>20 1 2 3 Store # City 101 Huntsville 102 Conroe 103 New Orleans 104 Lake Charles 105 Spring 106 Montgomery 107 Navasota 108 Moss Bluf 109 Jennings 110 Lafayette State TX TX LA LA TX TX TX LA LA LA Zipcode Phone # Region Dist 33211 864-555-1234 1 33212 864-555-1235 1 33213 864-555-1236 1 33214 864-555-1237 1 33215 864-555-1238 1 33216 864-555-1239 2 33217 864-555-1240 2 33218 864-555-1241 2 33219 864-555-1242 2 33220 864-555-1243 2 Item # Description 12345 40 Watt Light Bulbs 12346 60 Watt Light Bulbs 12347 100 Watt Light Bulbs 12348 200 Watt Light Bulbs 12349 1000 Watt Light Bulbs Cost Price $3.39 $6.78 $13.56 $27.12 $54.24 $1.60 $3.20 $6.40 $12.80 $25.60 1. In total, how many of item 12345 have been sold? (Pivot table on separate sheet "Answer #1") 2. In total, how many light bulbs (all item numbers) have been sold in Lake Charles (Add a column "City", use Vlookup to fill cells in the "City" column) (Find the answer using a pivot table on separate sheet "Answer #2") 3. In total, how many light bulbs (all item numbers) have been sold in LA? (Add a column "State", use Vlookup to fill cells in the "State" column) (Find the answer using a pivot table on separate sheet "Answer #3") 4. In total, how many of item 12347 have been sold in district 104? (Pivot table on separate sheet "Answer #4") 5. In total, how many dollars have been earned (total sales dollar) in TX? (Find the answer using a pivot table on separate sheet "Answer #5") 101 101 102 102 102 103 103 103 104 104 ...

View Full Document

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 Worksheet.xls 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. (Use INSERT FUNCTION and select “Statistical” from the category drop down box.) Put these formulas in cells C40: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.

## One thought on “Excel Assignment 1”