PROBABILITY AND STATISTICS EXCEL PROJECT (Spring 2016) There are four problems in this workbook. There is one worksheet (tab) for each problems. Instructions are found at the top of each worksheet. You are put your answers in the cells which are YELLOW in color. Any statistics which are calculated must be calculated using an EXCEL fomula. You are not allowed to simply type the appropriate value into the cell. Due Date: April 22 (end of day) Excel Function Sum Average Stdev.s Var Percentile Quartile Count Countif Purpose of the Function Computes the sum of a range of numbers Computes the mean of a range of numbers Computes the samplestandard deviation of a range of numbers Computes the sample variance of a range of numbers Computes the pth percentile of a range of numbers Computes the first, second, or third quartile of a range of number Computes the number of cells with a number Computes the numbers of cells (in a range) that have a certain value What you type (starting cell = cell address of first value, ending cell = cell a =sum(starting cell: ending cell) =average(starting cell: ending cell) =STDEV.S(starting cell: ending cell) =VAR.S(starting cell: ending cell) =percentile(starting cell: ending cell, decimal) =quartile(starting cell: ending cell, value) value=1,2,3 =COUNT(starting cell: ending cell) =COUNTIF(starting cell: ending cell, "VALUE") Here, value can be a cel Set a cell equal to the contents of another cell =cell address You can find help for all these functions under the Formulas tab at the top. l address of last value) cell address The data (column B) are ice cream flavor preferences of a sample of people Fill in the appropriate values in the Frequency Table below and then use that table to contruct a Pie Chart. The sample data can be found in column B (rows 9 through 208). decimal place (such as 75.3%) Display Percents (column I) with a percent font and 1 Frequency Table Flavor Strawberry Chocolate Vanilla Other Chocolate Chocolate Strawberry Other Strawberry Vanilla Chocolate Vanilla Strawberry Vanilla Strawberry Vanilla Vanilla Chocolate Other Strawberry Vanilla Vanilla Other Vanilla Chocolate Vanilla Strawberry Other Strawberry Other Strawberry Vanilla Vanilla Strawberry Vanilla Other Strawberry Vanilla Chocolate Strawberry Chocolate Vanilla Chocolate Other Vanilla Strawberry Vanilla Strawberry Strawberry Vanilla Vanilla Vanilla Chocolate Chocolate Vanilla Strawberry Chocolate Vanilla Vanilla Strawberry Chocolate Vanilla Chocolate Chocolate Vanilla Other Strawberry Chocolate Strawberry Chocolate Strawberry Vanilla Strawberry Chocolate Vanilla Chocolate Chocolate Vanilla Strawberry Strawberry Flavor Chocolate Vanilla Strawberry Other Percent Frequency Frequency Total Create a pie chart from the Frequency Table you constructed above Chocolate Strawberry Chocolate Vanilla Vanilla Strawberry Chocolate Vanilla Chocolate Chocolate Vanilla Vanilla Strawberry Chocolate Strawberry Chocolate Other Strawberry Chocolate Vanilla Chocolate Strawberry Chocolate Vanilla Chocolate Chocolate Vanilla Vanilla Chocolate Chocolate Strawberry Chocolate Other Strawberry Vanilla Chocolate Strawberry Chocolate Vanilla Strawberry Chocolate Strawberry Chocolate Strawberry Chocolate Strawberry Vanilla Chocolate Vanilla Chocolate Vanilla Strawberry Vanilla Vanilla Chocolate Chocolate Chocolate Strawberry Vanilla Chocolate Chocolate Chocolate Strawberry Vanilla Chocolate Chocolate Chocolate Strawberry Chocolate Other Strawberry Vanilla Chocolate Strawberry Chocolate Vanilla Strawberry Chocolate Strawberry Chocolate Other Strawberry Chocolate Vanilla Chocolate Strawberry Chocolate Vanilla Chocolate Chocolate Vanilla Vanilla Chocolate Chocolate Strawberry Chocolate 0 Other Strawberry Vanilla Chocolate Strawberry Chocolate Vanilla Chocolate Vanilla Other Strawberry Strawberry Chocolate Strawberry Chocolate Other Strawberry Vanilla Chocolate Strawberry Chocolate Vanilla Strawberry Chocolate The following frequency distribution summarizes the grades for a certain Statistics class. Complete the Frequency Distribution and use this to construct a histogram (bar chart) below in the large YELLOW area. Your historgram should have bars with heights that reflect the frequencies (column D). Use a Percent font (with one decimal place) for Columns F and G. Use the data in columns C and D (rows 8 through 13) to complete the frequency distribution and the histogram. Grades A B C D F Cumulative Frequency Frequency Percent Frequency Cumulative Percent Frequency 12 25 28 15 5 Total Create a histogram (vertical bar chart or horizontal bar chart from the Frequency Distribution above) The following data represent a sample of people for which the height in inches has been measured. Using the appropriate EXCEL formulae, calculate the statistics in the YELLOW areas. The sample are found in column B (rows 9 through 128). Use 1 decimal place to format all the numerical results. Height 68 72 65 64 69 67 66 61 66 67 68 68 74 66 63 62 69 77 70 65 69 70 72 68 71 63 67 65 70 71 75 76 65 60 69 67 67 62 71 70 67 66 69 59 71 69 63 64 66 62 68 71 69 67 67 62 71 70 67 70 71 75 76 65 60 69 67 67 62 73 75 76 65 62 69 67 67 62 73 70 67 66 69 Statistic Sample Size Mean Median Q1 Q3 p20 Variance IQR Maximum Minimum 59 70 68 74 66 63 62 69 77 75 76 65 60 69 67 67 62 69 67 67 59 71 69 63 64 66 62 68 71 69 67 67 62 68 74 76 63 The data below (columns B and C, rows 17 through 66) show the number of years of service (rounded to the nearest integer) for a sample of employees at ABC compary, along with the employees' salaries. Construct a scatterplot (with "Years" on the horizontal axis, and "Salary" on the vertical axis). Inspect the graph the answer the question below (in cell A10). Remember: if two variables are positively correlated, then as one increases the other variables "generally" increases. If they are negatively correlated, then as one variable increases, the other one "tends" to decrease. Graph Title: ABC Salaries versus Experience Horizontal Axis Label: Years of Experience Vertical Axis Label: Salary Are the variables (Years, Salary) uncorrelated, positively correlated, or negatively correlated? Answer below (in words) Identify any outliers (what are the years, salary for any observations which are unusual). Type answer in the cell (A14) below: (years, salary) Years 0 1 2 3 4 5 6 7 8 9 10 4 2 5 4 8 7 5 6 3 9 12 6 5 3 6 8 2 0 5 15 9 5 8 3 9 4 14 5 13 5 15 9 5 8 3 9 4 14 5 Salary $22,678 $28,355 $31,119 $35,109 $32,094 $40,502 $41,560 $41,294 $44,515 $47,533 $45,371 $46,514 $30,001 $39,815 $38,903 $51,359 $49,825 $35,935 $45,248 $34,000 $55,603 $59,813 $40,914 $37,783 $30,912 $37,905 $50,028 $29,836 $23,967 $41,522 $82,407 $50,203 $44,827 $60,236 $29,354 $58,711 $36,304 $77,458 $41,423 $30,394 $39,522 $82,407 $50,203 $44,827 $60,236 $28,165 $58,711 $36,304 $77,458 $41,423