Assignment #2 for Computer Tools KJC (08/31/99) -------------------------------- Note: Proper formatting of numbers (e.g., use of "$" if number is a dollar value) and graphs (e.g., labeling of axes) will be considered in quiz grading. A sloppy spreadsheet will have points deducted. For example, writing intermediate values to "scratch" cells is considered very sloppy. 1) The following data has been collected for an Ecommerce site for one week of online operation. The data shows the number of customers entering the site each day and the total number of purchases made on the day. Enter this data into a spreadsheet. Day Number of customers Number of purchases made ------------------------------------------------------------ Monday 10067 2190 Tuesday 6789 1015 Wednesday 7890 1210 Thursday 8222 2500 Friday 9431 1010 Saturday 10941 3500 Sunday 12416 3801 a) At the bottom of the number of the customers column output the min, max, mean, and standard deviation of customers by day for the week. Also output the total number of customers for the week. These cells should be "live" (i.e., if the data changes, so do the results in these cells). b) Create a single XY plot showing the day on the X-axis and the average number of purchases per customer (per day) on the Y-axis. c) Create a single XY plot with two Y axes showing day on X-axis and number of customers on the first Y-axis and number of purchases made on the second Y-axis. d) Add a fourth column called WARNING. In this column output "alert" if the number of customers is greater than 10000 or the number of purchases made is greater than 3000 for the associated day. 2) Enter 10 numbers (all between 0 and 20) in cells A4 through A14. In cell C3 create a formula that outputs the work "less than 100" if the sum of the cells A1 through A10 is less than 100, outputs "is 100" if the sum is 100, and "greater than 100" is the sum is greater that 100. When the formula in cell C3 is moved, it should still work and reference the cells in A1 through A10. 3) The following shows the quiz grades for 10 students. Student # Quiz Score --------------------------- 1 46 2 66 3 83 4 78 5 72 6 70 7 61 8 12 9 94 10 99 a) Output a histogram of the quiz scores using a bin size of 10 points. b) Assume that the student # corresponds to the order in which the student was accepted into the class (i.e., student #1 was accepted first, then student #2, and so on). Plot the student # on the X-axis and the quiz score on the Y-axis. What is the tend in the quiz scores? Give the equation of the linear trend line. c) Write a macro that will automatically copy the table of student number and quiz scores to another place on your spreadsheet and then sort the data in ascending order by quiz score. The macro should run when "Ctrl-s" is pressed. 4) For a given class of 3 students there are 3 quizzes, 2 homeworks, and 2 exams. The quizzes are worth 30% overall, the homework 20%, and the exams 50% for a total of 100%. Everything is graded on a 0 to 100 scale. Enter the following class data: Student Q1 Q2 Q3 HW1 HW2 E1 E2 Grade ---------------------------------------------- Jamie 95 43 54 100 80 94 Jeff 99 76 78 100 85 99 Jill 99 75 76 90 75 91 a) Enter the formula for grade compuration into the cells in the Grade column. b) What grade must each student make on Exam #2 in order to earn an A (average of 90, or higher) in the class? c) Add a another column called "Grade1" where the student grade is computed based on dropping the lowest quiz score. Enter the formula for grade computation into the cells in this new column. d) Compute the average quiz grades for the class and plot them on an XY plot (quiz # on X-axis, score on Y-axis). Determine the linear trendline for quiz scores. Give the equation of the trend line. e) Write a formula that returns a student's HW2 grade given the students name. Put this formula in a cell directly below the entered grade data. 5) Do the following: a) Create a spreadsheet that takes as input a starting salary and yearly raise. Output the yearly salary for the first 5 years (e.g., as a column of 5 values). If a yearly salary exceeds $100,000 output a message that states "high tax bracket" for that year (e.g., in a second column). All salary values should be rounded-up to the nearest dollar and output with a "$" sign. The input portion of the spreadsheet should look like: Enter starting salary =====> Enter yearly raise ========> b) Create a spreadsheet that computes the sales price of an item entered as a part number. Each item (given in a table below) has an associated cost. To the cost, 5% sales tax is added to determine the sales prices. If an invalid part number is entered, a warning message "ERROR - Invalid part number" should be displayed on the the spreadsheet. The spreadsheet should look like: Enter part number of item =====> Purchase price of item ========> The table of item part numbers and cost is: Part Number Cost --------------------------- 1001 $15.00 3011 $14.50 2034 $120.00 104 $111.00 1111 $22.50 6) Using the Microsoft equation editor, typeset the formula for standard deviation as given in Excel help for the Excel function COVAR. Place the equation (as an equation object) in a spreadsheet cell. Neatness counts! =====