Question

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 (ert). Calculate rf and erf. c. (1 pt.) Find a mean. d. (1 pt.) Find a sample standard deviation. e. (1 pt.) Find a variance.

Answer 1

b) Sr.No. frequency relative frequency cf

1. 271 0.271 271

2. 87 0.087 358

3. 206 0.206 564

4. 177 0.177 741

5. 35 0.035 776

6. 224 0.224 1000

Here N =1000

a)c) using excel

Top cell phone provider Verizon US T 22.4% 27.1% AT&T 17.7% 20.6%

Mean = 166.67

d) standard deviation = 88.892

e) variance = 7901.87

Excel code

AVEDEV

STDEV. S

VAR

EITHER WE GO TO DESCRIPTIVE STATISTICS AND GRAPH HAVE GO TO GRAPHS .

2)a)

a)

X 20 X 19.5 19 18.5 x 18 17.5 17 16.5 x 16 15.5 15 70 72.5 75 77.5 80 825 85 87.5 90 925

b)The regression equation is,

Y= 0.2061 + 0.2884X

r = correlation coefficirnt= 0 .8252

R2= coefficient of determination = 0 .6810

c)

20 19.5 19 18.5 排 18 17.5 17 165 载 1 基 15.5 15 基 14.5 70 75 5 了5 60 25 5 875 90 925

d)There is high positive correlation between temperature and chips.

Interpretation :- The slope is 0. 29 =0.3you can write this as 0.3/1 and say that as you move along the line, as the value of the X variable increases by 1, the value of the Y variable increases by 0.3.

(169.4 x 65754.71) - (807.9 x 13785.68)) (10 x 65754.71) - (807.9)2 11138847.874 - 11137450.872 657547.1 - 652702.41 1397.0019999985 = 4844.6899999999 Slope = 0.2884