1. Basic Excel Operation: 1) Rename this sheet "Grade Calculator". 2) In the green cells, enter grades that you want to earn as a number from 0 to 100. 3) In the yellow cells , enter the weights based the Syllabus (some weights are already entered) as percentages. 4) In E12, enter a formula formula for Points: Type = click C12 * click D12 Enter. Copy this formula down colu The results in column E should be a number between 0 and 20. Assignment Your Grade Participation Wk1 Wk4 Wk6 Wk9 Wk11 Excel Homework 2 Quiz 3 Quiz 4 Quiz 5 Report - Test the Expert Part 1 Quiz 6 MidTerm Exam Quiz 8 Report - Test the Expert Part 2 Quiz 10 Excel Homework 11 Extra Points Quiz Final Exam Total 100 100 100 100 100 90 90 90 90 100 95 95 90 100 92 90 90 96 1708 Weight 3% 3% 3% 3% 3% 10% 5% 5% 5% 4% 5% 10% 5% 9% 5% 10% 2% 10% 100% Overall Grade Points Earned toward Overall Grade 3 3 3 3 3 9 4.5 4.5 4.5 4 4.75 9.5 4.5 9 4.6 9 1.8 9.6 94.88888889 already entered) as percentages. Copy this formula down column E to row 29. 2. Descriptive statistics A quality characteristic of interest for a rice-bag-filling process is the weight of the rice in bags. In this example, t the package indicates that, on average, there are 15 pounds of rice in a bag. The data following provide the wei pounds of a sample produced in one hour by a single machine: 15 15.2 15 15.3 14.9 15.3 15.2 15.4 15.1 15.4 15.2 15 15.5 15.2 15.5 14.9 15.3 14.7 15.4 15 15.5 14.8 15.3 14.9 15 15.4 15.2 14.7 15.5 15.5 15.4 14.8 15.5 15 15.5 15.5 15.3 15 15.2 15.4 Lets use Excel to compute some descriptive statistics. For credit, compute the following by entering = in a blank by the Excel function or formula. a. Descriptive Statistics ● Mean with =AVERAGE ● Median with =MEDIAN ● 3rd quartile with =QUARTILE.EXC ● 1st quartile with =QUARTILE.EXC ● IQR addressing the cells with 3rd and 1st quartiles ● Range measure of spread addressing cells with functions MAX and MIN ● Variance with VAR.S ● Standard deviation with STDEV.S ● Coefficient of variation addressing cells with previous results and in percent format. b. Getting an exact amount of rice in a bag is problematic because of variation in the temperature and humidity factory, differences in the size of the rice, and the extremely fast filling operation of the machine If the bags are under filled, the company may be in violation of the truth-in-labeling laws. If the bags are over fill company is giving away product. You are a consultant brought in by the company. Show your understanding of s Why should the company be concerned about the mean bag weight? d. Review Course Content CV (Coefficient of Variation), a relative measure of variation. Based on the CV, should be concerned about variation? the rice in bags. In this example, the label on e data following provide the weight in 15.3 15.4 15.3 15.3 15 15.2 14.9 15 14.8 15.5 ollowing by entering = in a blank cell followed n the temperature and humidity inside the on of the machine at about 150 bags a minute. eling laws. If the bags are over filled, the ny. Show your understanding of statistics. riation. Based on the CV, should the company 3. Built In Excel Functions a. Reference week 2 Course Content, "Statistics are numbers that measure", "Position". ● What is the difference between the functions QUARTILE.INC() and QUARTILE.EXC()? ● Which of the two will you get if you use QUARTILE()? b. Reference Excel help files, and explain in your own words the meaning of .P and .S in STDEV.P() and STDEV.S(), and VAR.P() and VAR.S() c. Reference Course Content Covariance. In your own words, what is covariance? d. How do you calculate the Coefficient of Variation of data using Excel? 4. Frequency Distribution Here is a hypothetical list of the prices of the property sold (in thousands) in Columbia, MD February 1 247 1190 210 211 415 567 195 187 280 1100 354 326 170 572 892 195 349 182 829 405 987 997 532 155 520 195 1020 572 775 896 208 330 1345 268 247 879 175 180 265 1435 We will create a 5-bin frequency distribution using Excel’s array formula {=FREQUENCY()}. In the following parts the computations. For credit, compute the following by entering = in a blank cell followed by the Excel function a. The minimum of the data using Excel function =MIN, and the maximum using =MAX. b. The bin width equal to 1.10 x (max - min) / number of bins c. The first bin upper limit = minimum + width d. The remaining 4 bin upper limits. The bins must be equally spaced by the bin width. e. Reference Course Content, How to Videos, =FREQUENCY. Compute frequencies of your bins step Select all 5 cells next to the 5 bin values at the same time. All 5 cells should be bolded. Type = FREQUENCY( Select all the data Type , Select all the bin values Type ) while holding down Ctrl and Shift strike Enter. This takes at least 2 fingers. The array formula will display with curly braces like {=frequency(your inputs)} and your frequencies must match proceding to Problem 5: a, MD February 1-5, 2018.: 588 698 325 986 1328 559 95 590 1490 290 In the following parts of this Problem, let Excel do lowed by the Excel function or formula. f your bins step-by-step: be bolded. our frequencies must match the following before 5. Histogram, bar chart, and pie chart Watch Course Content How To Videos as needed. For credit, create the following charts on worksheet Problem 5. Note: Charts linked to external files will not be c when an object, e.g., chart, in a different file is pasted into another file ● Do not copy the data on tab Problem 4 to tab Problem 5. Good practice is not to duplicate data and results. Ad results update to data updates, avoid typing and rounding errors. You can select the original data on a different a) Create a histogram using Data Analysis, Histogram, addressing the data and bin values on tab Problem 4. ● A histogram produced by Data Analysis will display =SERIES("Frequency" in the formula window when a bar is b) Create a bar chart addressing the frequencies on tab Problem 4. d) Create a pie chart addressing the frequencies on tab Problem 4. Example based on old problem: nked to external files will not be credited. An external link is created t to duplicate data and results. Advantages: save time, support audit, t the original data on a different tab. bin values on tab Problem 4. he formula window when a bar is clicked.
Purchase answer to see full attachment