1. Basic Excel Operaton: Rename this sheet "Grade Calculator". Create an Grade Calculator for this class. The spreadsheet should inclu weights of each graded assignment and your grades in each assignment, and your Overall Grade. After this we that you may use will be posted. Problem 1 is worth 25%: 5% - Used grades in range 0 to 100 (perfect = 100) 10% - Used the correct weights in the weighted average 10% - The spreadsheet automatically calculates the Overall Grade grade spreadsheet should include columns of verall Grade. After this week a solution 2. Basic Descriptve statstcs A quality characteristic of interest for a tea-bag-filling process is the weight of the tea in the individual bags. If the under filled, two problems arise. First, customers may not be able to brew the tea as strong as they wish. Second company may be in violation of the truth-in-labeling laws. In this example, the label on the package indicates tha there are 5.5 grams of tea in a bag. If the average amount of tea in a bag exceeds the label weight, the company i product. Getting an exact amount of tea in a bag is problematic because of variation in the temperature and hum the factory, differences in the density of the tea, and the extremely fast filling operation of the machine (approxim bags a minute). The data in column O provides the weight in grams of a sample of 50 bags produced in one hour machine: a. Compute the arithmetic mean and median using Excel functions or Data Analysis Descriptive Statistics. b. Compute the first quartile and third quartile using Excel function =QUARTILE.EXC (QUARTILE if your version of E then 2010). c. Compute the range, interquartile range, variance, standard deviation, and coefficient of variation in percent for d. Show your understanding of statistics. You are a consultant brought in by the company. Consider the mean wei How much extra tea is bagged per hour? Why should the company producing the tea bags be concerned about th tendency? e. Consider the results of part c. Should the company producing the tea bags be concerned about variation? Problem 2 is worth 25%: 3% for the correct answer to part a 3% for the correct answer to part b 3% for the correct answer to part c 8% for the correct answer to part d 8% for the correct answer to part e a in the individual bags. If the bags are s strong as they wish. Second, the on the package indicates that, on average, e label weight, the company is giving away n in the temperature and humidity inside tion of the machine (approximately 170 50 bags produced in one hour by a single Descriptive Statistics. QUARTILE if your version of Excel is older ent of variation in percent format. mpany. Consider the mean weight per bag. a bags be concerned about the central ncerned about variation? 5.85 5.57 5.67 5.67 5.68 5.74 5.50 5.40 5.56 5.45 5.42 5.53 5.47 5.42 5.44 5.40 5.54 5.61 5.58 5.25 5.53 5.55 5.53 5.59 5.56 5.34 5.68 5.32 5.50 5.63 5.54 5.66 5.70 5.39 5.50 5.45 5.48 5.30 5.50 5.57 5.52 5.64 5.49 5.53 5.67 5.41 5.51 5.55 5.58 5.36 3. Frequency Distributon Zillow.com prices of the property sold (in thousands) in Washington, DC June 29 - 30, 2016 are given below. Creat distribution using Excel's array formula {=FREQUENCY()}. a. Compute the minimum and the maximum of the data using Excel functions =MIN and =MAX b. Compute the bin width equal to 1.05 x (max - min) / number of bins c. Compute the bin upper limits. The first bin upper limit = minimum + width. Bins must be equal width and equall d. Compute frequencies of your bins. Select a range of cells with the same number of rows as the bin range. Type finish the inputs with ). Enter the formula by typing Ctrl + Shift + Enter. The array formula will display with curly bra {=frequency(your inputs)}. If you have completed each step correctly your bins and frequencies will be: Problem 3 is worth 30%: If you begin each computation with =, perform the computation as specified, and your result is correct. 5% for the correct answer to part a 5% for the correct answer to part b 10% for the correct answer to part c 10% for the correct answer to part d 316 265 299 869 240 580 1315 105 920 1250 192 721 320 1700 1420 440 368 918 2450 585 1460 763 1451 1460 1456 1333 536 625 1562 949 1775 406 673 780 1435 844 961 1570 888 1475 30, 2016 are given below. Create a 5-bin frequency N and =MAX must be equal width and equally spaced. r of rows as the bin range. Type = FREQUENCY and ormula will display with curly braces as d frequencies will be: your result is correct. 443 1875 449 1750 945 785 1213 1879 495 645 4. Histogram, bar chart, and pie chart a. Use the house price data and the bin values from problem 3 to create a histogram using Data Analysis, hist b. Use bin ranges and use the frequencies from problem 3 =FREQUENCY() distribution to create a vertical bar c. Use the bin ranges and use the frequencies from problem 3 =FREQUENCY() distribution to create a pie char 9% - Created correct histogram using data and bins from Problem 3, and Data Analysis. There should be no ga bars because price is a continuous variable. 1% - Added meaningful titles to the histogram. The horizontal labels should be the computed bin values, not 4% - Created a correct vertical bar graph with gaps between bars. Bin ranges to are horizontal axis labels. 1% - Added meaningful titles to the bar graph 4% - Created a correct pie chart showing frequency by bin range 1% - Added meaningful titles to the pie chart and legend. The legend has bin ranges. The areas of the segmen frequencies or relative frequencies in the bin ranges. 3. Frequency Distributon Zillow.com prices of the property sold (in thousands) in Washington, DC June 29 - 30, 2016 are given below. C frequency distribution using Excel's array formula {=FREQUENCY()}. a. Compute the minimum and the maximum of the data using Excel functions =MIN and =MAX b. Compute the bin width equal to 1.05 x (max - min) / number of bins c. Compute the bin upper limits. The first bin upper limit = minimum + width. Bins must be equal width and eq d. Compute frequencies of your bins. Select a range of cells with the same number of rows as the bin range. T FREQUENCY and finish the inputs with ). Enter the formula by typing Ctrl + Shift + Enter. The array formula will curly braces as {=frequency(your inputs)}. If you have completed each step correctly your bins and frequencies 316 265 299 869 240 580 1315 105 920 1250 192 721 320 1700 1420 440 368 918 2450 585 1460 763 1451 1460 1456 1333 536 625 1562 949 1775 406 673 780 1435 844 961 1570 888 1475 443 1875 449 1750 945 m using Data Analysis, histogram. on to create a vertical bar chart. bution to create a pie chart. sis. There should be no gaps between the omputed bin values, not bin ranges. horizontal axis labels. . The areas of the segments indicate , 2016 are given below. Create a 5-bin and =MAX ust be equal width and equally spaced. f rows as the bin range. Type = ter. The array formula will display with your bins and frequencies will be: 785 1213 1879 495 645