Question

Use only the Excel functions (no calculator). I need to see all Excel formulas to give you a full credit. Give answers to the 4th decimal place.

1. (Sheet #1) (10 points) Use the following categorical frequency distribution to answer each problem. a. (4 pts) Create a pie chart and pareto chart. b. (3 pts) Add two more columns: Relative Frequency (rf) and Cumulative Relative Frequency (crf). Calculate rf and crf. c. (1 pt.) Find a mean. d. (1 pt.) Find a sample standard deviation. e. (1 pt.) Find a variance.

2. (Sheet #2) (10 points) The table represents the Temperature in Fahrenheit and the number of cricket chirps per sec. a. (2 pts) Create a scatter plot. b. (2 pts) Add a trendline with an equation and the coefficient of determination. Calculate the correlation coefficient. c. (2 pts) How do you describe the correlation between the temperature and the number of chirps? d. (2 pts) Round a slope to the tenths and interpret the slope of the equation in the context of the problem. e. (2 pts) Check whether the correlation is significant at the 5% significance level. Use the table of 95% Critical Values form the textbook on page 509.

3. (Sheet #3) (10 points) Use Excel functions to find the following probabilities of given distributions. a. (3 pts) ?(−?.? < ? < ?.?) b. (3 pts) ?(? < ??), ???? ? ~ ? (??,?.?) c. (4 pts) ) ?(? > ??), ???? ? ~ ? (??,??)

Give answers to the 4th decimal place.

Please post excel downloadable file including formulas or message to email please and thank you!!

Use only the Excel functions (no calculator). I need to see all Excel formulas to give you a full credit. 1. (Sheet #1) (10 points) Use the following categorical frequency distribution to answer each problem. Top Cell Phone Providers Verizon US Cellular T-Mobile Sprint Other AT&T Number of Students 271 87 206 177 35 224 a. (4 pts) Create a pie chart and pareto chart. b. (3 pts) Add two more columns: Relative Frequency (rf) and Cumulative Relative Frequency (erf). Calculate rf and crf. c. (1 pt.) Find a mean. d. (1 pt.) Find a sample standard deviation, e. (1 pt.) Find a variance. 2. (Sheet #2) (10 points) The table represents the Temperature in Fahrenheit and the number of cricket chirps per sec 82 Temp (x) 886 71.6 93.3 84.3 75.2 69.4 83.3 79.6 80.6 20 16 Chirps (y) 16 15 19.8 18.4 15.5 17.1 15.4 16.2 a. (2 pts) Create a scatter plot. b. (2 pts) Add a trendline with an equation and the coefficient of determination. Calculate the correlation coefficient c. (2 pts) How do you describe the correlation between the temperature and the number of chirps? d. (2 pts) Round a slope to the tenths and interpret the slope of the equation in the context of the prehlam. 30 FO A $ 4 % 5 & 7 9 B 0 3 6 E R. T Y "

Answer 1

using excel,

1)

B. С D Q.1) E F G H K M Pie-Chart Pareto Chart 300 100% 250 AT&T 22% 90% Vertzon 1 80% 200 70% Other 3% 150 60% 2 3 4 5 50% Sprint 18% US cellular 9% 100 40% 26 30% T-mobile 2196 50 20% 10% 0 Verizon AT&T mobile 0% Sprint US cellular Other 28 29 30 31 32 b) 33 34 35 36 37 crf Top Cell phone providers Verizon + US cellular T-mobile Sprint Other AT&T Total No. of students rcf 271 0.271 87 0.087 206 0.206 177 0.177 35 0.035 224 0.224 1000 0.271 0.358 0.564 0.741 0.776 38 39

D E וד G H using AVERAGE function find mean Mean 2 166.6667 3 4 d) 15 using STDEV function find Standard Deviation Standard Deviation 88.8924 46 using VAR function find variance Variance 7901.8667 47 e) 48 49 50 Q.2) 51 52 a) 53 54 Answer Chirps (y) scatter plot 25 20 55 15 56 Chirpsly) 10 57 58 5 • Chirps (y) 59 20 60 61 80 100 Temp(x) 62

E F G b) Chirps (y) 25 5. 20 8 y = 0.2061x +0.2884 R? = 0.6809 9 15 O Axis Title 1 10 Chirps (y) Linear (Chirps (y)) 2. 5 73 74 0 75 20 40 60 80 100 76 Axis Title 77 78 79 coefficient determination= 0.6809 80 81 82 using CORREL function find correlation coefficient, Coefficient of Correlation= 0.8252 83 84 c) 85 There is positive correlation between Temp(x) & No. Of Chirps.

d) The SOLPE of regression line is 0.206. slope 0.2060 9 De) 1 Testing Null & Alternative Hypothesis, HO: p=0 Against H1: p=0 2 93 94 Level of significance a=0.05 df=n-218 35 96 97 critical values t= 2.3060 98 99 the test statistic is, 100 101 102 103 t=rVn-2 √1-p12 104 0.825 V10 - 2 V1 - (0.825)^2 105 106 107 4.1290 108 109 Hence we Reject HO. Conclusion:- 110 There is significant difference between correlation coefficient at 5% level of significance. 111 112

Q.3) Answer a) P(-0.3<z<1.4) using Normsdist Function, 0(-0.3) (1.4) 0.3821 0.9192 P(-0.3<z<1.4) 0(1.4)-0(-0.3) 0.5372 P(-0.3<z<1.4)= 0.5372 5b) 6 P(x<30) when x follows B(53,0.6) using Binomdist Function , Numbers =30, Trials= 53, prob.=0.6, Cumulative = true. P(x<30)= 0.3549 27 28 29 c) 30 P(x>65) when x follows N(80,12) 31 32 P(x>65)= 1-P(x<=65) = 1-Normdist(65,80,12,true) = 1-0.1056 33 34 P(x>65)= 0.8944 135 136