Data Central tendency with quartiles and varaince

21
23 mean 61.93333 Column1
34 median 65
45 mode 68 Mean 61.93333
47 Quartiles Standard Error 6.416101
55 Q1 45 Median 65
56 Q2 65 Mode 68
65 Q3 87 Standard Deviation 24.84945
68 RANGE 76 Sample Variance 617.4952
68 variance 576.3289 Kurtosis -0.990478
78 max 97 Skewness -0.195328
87 min 21 Range 76
90 Minimum 21
95 Maximum 97
97 Sum 929
Count 15
 Obtaining the freq of data Dot plot

Original data X freq

21 21 1 Chart Ti
23 23 1 2.5
34 34 1
45 45 1 2
47 47 1
55 55 1 1.5
56 56 1
65 65 1
1
68 68 2
68 78 1
0.5
78 87 1
87 90 1
90 95 1 0
10 20 30 40 50 60
95 97 1
97
 Chart Title

20 30 40 50 60 70 80 90 100 110
Q1.

Data Steam row Leaf value column_loc steam

31 3 1 1 [Link](Num/10)
35 3 5 2 leaf
36 3 6 3 value(right(Num,1))
36 3 6 4 column_loc
37 3 7 5 countif(range,criteria)
40 4 0 1
40 4 0 2 steam-leaf plot
40 4 0 3
41 4 1 4
41 4 1 5
42 4 2 6
42 4 2 7
42 4 2 8
42 4 2 9
43 4 3 10
44 4 4 11
45 4 5 12
46 4 6 13
14
46 4 6 14
47 4 7 15 12
48 4 8 16
10
48 4 8 17
51 5 1 1 8

Frequency
54 5 4 2
54 5 4 3 6
55 5 5 4 4
58 5 8 5
62 6 2 1 2
66 6 6 2 0
66 6 6 3
67 6 7 4
68 6 8 5
 Q2

Data steam row leaf value col_loc

16 1 6 1
18 1 8 2
18 1 8 3
26 2 6 1
33 3 3 1
41 4 1 1
54 5 4 1
87 8 7 1
95 9 5 1
98 9 8 2
106 10 6 1
109 10 9 2
111 11 1 1
118 11 8 2
127 12 7 1
127 12 7 2
135 13 5 1
145 14 5 1
147 14 7 2
149 14 9 3
151 15 1 1
168 16 8 1
172 17 2 1
183 18 3 1
189 18 9 2
190 19 0 1

Column1

Mean 108.1923077
Standard E 11.07443615
Median 114.5
Mode 18
Standard D 56.46876604
Sample Var 3188.721538
Kurtosis -1.042513988
Skewness -0.349182246
Range 174
Minimum 16
Maximum 190
Sum 2813
Count 26
Sum of Leaf row column_loc
Steam row 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
3 1 5 6 6 7
4 0 0 0 1 1 2 2 2 2 3 4 5 6 6 7 8 8
5 1 4 4 5 8
6 2 6 6 7 8

Bin Frequency
31 1
38.4 4
45.8 12
53.2 6
60.6 4
More 5

Histogram
14

12

10

8
Frequency

Frequency
6

0
31 38.4 45.8 53.2 60.6 More
Bin
Sum of leaf value col_loc
steam row 1 2 3
1 6 8 8
2 6
3 3
4 1
5 4
8 7
9 5 8
10 6 9
11 1 8
12 7 7
13 5
14 5 7 9
15 1
16 8
17 2
18 3 9
19 0

Bin Frequency
16 1
50.8 5
85.6 1
120.4 7
155.2 7
More 5

Histogram
8
7
6
5
Frequency

4 Frequency
3
2
1
0
16 50.8 85.6 120.4 155.2 More
Bin
1
0
16 50.8 85.6 120.4 155.2 More
Bin
 Practical 2
Q1
Find the binomial dustribution of the given data and find
a)Probabbility at x=4 (b) probability for x<5 (c)probability for x<=5
(d)probability for x>4
random varaible=x

Data
x f pmf p(x) expected freq rounded expected freq
[Link](x,n,p,false)
0 5 0.0165764990810938 1.67422640719047 2
1 12 0.097508818124081 9.84839063053218 10
2 19 0.238992201284512 24.1382123297357 24
3 28 0.312408106254264 31.5532187316807 32
4 21 0.229711842834018 23.2008961262358 23
5 14 0.0900830756211835 9.09839063773954 9
6 2 0.014719456800847 1.48666513688555 1
101 1 101 101

cases symbol value formula

no. of cases n 6 max()
mean np 2.9702970297 sumproduct(x,f)/sum(f)
Ttoal freq N 101 sum(f)

probability of success p 0.49504950495 mean/n

probability of failure 1-p 0.50495049505 1-p

plot of binomial pmf binomial pm

x pmf p(x) 0.35

0.3
0 0.0165765
1 0.0975088 0.25
2 0.2389922 0.2
3 0.3124081
0.15
4 0.2297118
5 0.0900831 0.1
6 0.0147195 0.05

0
plot of freq and expected freq to see the best fit 1 2 3 4

pmf
x f Ef
 0 5 2
1 12 10 Best fi
2 19 24 35
3 28 32
30
4 21 23
5 14 9 25

6 2 1 20

15

10

0
1 2 3 4

probabilities x p(x) formula

x=4 4 0.22971184283 [Link](C65,6,$D$28,FALSE)

x<5 4 0.89519746758 [Link](C65,6,$D$28,TRUE)
x<=5 5 0.9852805432 [Link](C66,6,$D$28,TRUE)
x>4 4 0.10480253242 [Link](C65,6,$D$28,FALSE)

Q2

x f pmf expected freq round expected freq

0 7 0.0142613890266701 9.1272889770689 9
1 64 0.0881928520771447 56.4434253293726 56
2 140 0.227244670410306 145.436589062596 145
3 210 0.312286171429357 199.863149714788 200
4 132 0.2413983665187 154.494954571968 154
5 75 0.0995209764875616 63.6934249520394 64
6 12 0.0170955740502607 10.9411673921668 11
640 1 640 639

cases symbol value formula

no. of cases n 6 max()
mean np 3.0453125 sumproduct(x,f)/sum(f)
Ttoal freq N 640 sum(f)

probability of success p 0.50755208333 mean/n

probability of failure 1-p 0.49244791667 1-p

x pmf binomial
0 0.0142614 0.35
1 0.0881929
0.3
2 0.2272447
0.25
3 0.3122862
4 0.2413984 0.2
5 0.099521 0.15
6 0.0170956
0.1

0.05

0
1 2 3 4

pm

x f Ef
0 7 9 Best fit
1 64 56 250
2 140 145
3 210 200 200
4 132 154
5 75 64 150

6 12 11
100

50

0
1 2 3 4

probabilities x p(x) formula

x=4 4 0.22971184283 [Link](A15,6,$D$28,FALSE)

x<5 4 0.89519746758 [Link](A15,6,$D$28,TRUE)
x<=5 5 0.9852805432 [Link](A16,6,$D$28,TRUE)
x>4 4 0.10480253242 [Link](A15,6,$D$28,FALSE)
 binomial pmf p(x)

1 2 3 4 5 6 7 8

pmf p(x)
 Best fit

1 2 3 4 5 6 7

f Ef
 binomial pmf

1 2 3 4 5 6 7

pmf

Best fit

1 2 3 4 5 6 7

f Ef
Q1
Find the Poisson dustribution of the given data and find

a)Probabbility at x=4 (b) probability for x<5 (c)probability for x<=5

random varaible=x

Data
x f pmf p(x) expected frequency Rounded freq pmf
(pmf*N) [Link](x,mean,com
0 56 0.139178221230484 69.5891106152419 70
1 156 0.274459452266514 137.229726133257 137
2 132 0.270617019934783 135.308509967391 135
3 92 0.177885587770464 88.9427938852319 89
4 37 0.0876975947708387 43.8487973854193 44
5 22 0.0345879313776188 17.2939656888094 17
6 4 0.0113679001127774 5.68395005638868 6
7 0 0.0032024998603424 1.60124993017121 2
8 1 0.0007894162155744 0.394708107787204 0
500

cases symbol value formula

mean lambda 1.972 SUMPRODUCT(x,f)/SUM(f)
total freq N 500 SUM(f)

x pmf p(x)
0 0.139178 Poisson pmf p(x)
1 0.274459 0.3
2 0.270617
0.25
3 0.177886
4 0.087698 0.2
5 0.034588
6 0.011368 0.15

7 0.003202 0.1
8 0.000789
0.05

0
1 2 3 4 5 6

pmf p(x)

Poisson pmf p(x)

0.3

0.25

0.2
 Poisson pmf p(x)
0.3

0.25

0.2

0.15

0.1

0.05

0
0 1 2 3 4 5 6

x f EF
0 56 69.5891106152419 Best fit comparative f and ef
1 156 137.229726133257 180
2 132 135.308509967391 160
3 92 88.9427938852319 140
4 37 43.8487973854193 120
5 22 17.2939656888094 100
6 4 5.68395005638869 80
7 0 1.60124993017121 60
8 1 0.394708107787204
40
20
0
1 2 3 4 5 6

f EF

probabilities
x p(x) formula
x=5 5 0.0345879313776188 [Link](B75,$C$23,FALSE)
x<5 4 0.949837875973083 [Link](B76,$C$23,TRUE)
x<=5 5 0.984425807350702 [Link](B77,$C$23,TRUE)

Q2
Find the Poisson dustribution of the given data and find

a)Probabbility at x=3 (b) probability for x<3 (c)probability for x>4

random varaible=x
Data
x f pmf expected freq rounded freq
0 153 0.328044259706079 145.979695569205 146
1 169 0.365640343402731 162.709952814215 163
2 72 0.203772595873881 90.6788051638773 91
3 31 0.0757087697029552 33.6904025178151 34
4 12 0.0210963762767785 9.38788744316645 9
5 6 0.0047028326441718 2.09276052665643 2
6 2 0.0008736348282806 0.388767498584865 0

cases symbol value formula

mean lambda 1.11460674157303 SUMPRODUCT(A93:A99,B93:B99)/SUM(B93:B99)
total freq N 445 SUM(B93:B99)

x pmf
0 0.328044 Poisson pmf
1 0.36564 0.4
2 0.203773 0.35
3 0.075709
0.3
4 0.021096
0.25
5 0.004703
6 0.000874 0.2
0.15
0.1
0.05
0
1 2 3 4 5

pmf

Poisson pmf
0.4

0.35

0.3

0.25

0.2

0.15

0.1

0.05

0
0 1 2 3 4 5
 0.15

0.1

0.05

0
0 1 2 3 4 5

x f EF
0 153 145.979695569205 Best fit comparative f and ef
1 169 162.709952814215 180
2 72 90.6788051638773 160
3 31 33.6904025178151 140
4 12 9.38788744316645 120
5 6 2.09276052665643 100
6 2 0.388767498584864 80
60
40
20
0
0 1 2 3 4 5

f EF

probabilities
x p(x) formula
x=3 3 0.0757087697029552 [Link](B156,$C$105,FALSE)
x<3 2 0.897457198982692 [Link](B157,$C$105,TRUE)
x>4 4 0.0057376550375748 1-([Link](B158,$C$105,TRUE))
 [Link](x,mean,commulative)

oisson pmf p(x)

4 5 6 7 8 9

pmf p(x)

oisson pmf p(x)

oisson pmf p(x)

4 5 6 7 8 9

comparative f and ef

4 5 6 7 8 9

f EF
Poisson pmf

4 5 6 7

pmf

Poisson pmf

3 4 5 6 7
 3 4 5 6 7

comparative f and ef

3 4 5 6 7

f EF
Q.5
Fit the normal distribution to the given data ad hence plot the pdf,frequency and EF.

Class 60-65 65-70 70-75 75-80 80-85 85-90 90-95 95-100

freq 3 21 150 335 326 135 26 4

Lower upper mid value Expected rounded

f (x-mu)^2 F(a) F(b) pdf f(x)
limit(a) limit(b) x freq y EF EF

[Link] [Link]
average(a (a,mu,std (b,mu,std f(x)*sum( round(EF,
F(b)-F(a)
,b) dev,TRUE dev,TRUE f) 0)
) )

60 65 62.5 3 304.328 0.000125 0.003028 0.002903 2.903069 3

65 70 67.5 21 154.878 0.003028 0.033889 0.030861 30.86142 31
70 75 72.5 150 55.42802 0.033889 0.181889 0.148 148.0002 148
75 80 77.5 335 5.978025 0.181889 0.50403 0.32214 322.1405 322
80 85 82.5 326 6.528025 0.50403 0.823398 0.319368 319.3679 319
85 90 87.5 135 57.07803 0.823398 0.967603 0.144206 144.2057 144
90 95 92.5 26 157.628 0.967603 0.997154 0.02955 29.5503 30
95 100 97.5 4 308.178 0.997154 0.999885 0.002731 2.731259 3
Sum 1000 1050.024 0.99976 999.7603 1000

cases symbol value formula

mean mu 79.945 sumproduct(x,f)/sum(f)
std dev 5.444904 SQRT(SUMPRODUCT((x-mu)^2,f)/SUM(f))

x f(x)
62.5 0.002903 pmf f(x)
67.5 0.030861 0.35
72.5 0.148
0.3
77.5 0.32214
82.5 0.319368 0.25
87.5 0.144206
0.2
92.5 0.02955
97.5 0.002731 0.15

0.1

0.05

0
60 65 70 75 80 85 90 95 100
 0.05

0
60 65 70 75 80 85 90 95 100

x f EF
62.5 3 2.903069 Best fi t comparati ve f and ef
67.5 21 30.86142
f EF
72.5 150 148.0002
400
77.5 335 322.1405
82.5 326 319.3679 350

87.5 135 144.2057 300

92.5 26 29.5503 250
97.5 4 2.731259 200
150
100
50
0
60 65 70 75 80 85 90 95 100

Q.6
Fit the normal distribution to the given data of heights in cms of 200 Indians adult male and hence plot the pdf,freq and EF.

class 144-150 150-156 156-162 162-168 168-174 174-180 180-186

freq 3 12 23 52 61 39 10

Lower upper mid value Expected rounded

f (x-mu)^2 F(a) F(b) pdf f(x)
limit(a) limit(b) x freq y EF EF

[Link] [Link]
average(a (a,mu,std (b,mu,std f(x)*sum( round(EF,
F(b)-F(a)
,b) dev,TRUE dev,TRUE f) 0)
) )

144 150 147 3 457.5321 0.000928 0.009469 0.008541 1.708166 2

150 156 153 12 236.8521 0.009469 0.056928 0.047459 9.491811 9
156 162 159 23 88.1721 0.056928 0.207412 0.150484 30.09687 30
162 168 165 52 11.4921 0.207412 0.480154 0.272742 54.5483 55
168 174 171 61 6.8121 0.480154 0.762973 0.282819 56.56382 57
174 180 177 39 74.1321 0.762973 0.930772 0.167799 33.55981 34
180 186 183 10 213.4521 0.930772 0.987687 0.056916 11.38312 11
200 0.98676 197.3519 197

cases symbol value formula

mean mu 168.39 sumproduct(x,f)/sum(f)
std dev 7.836319 SQRT(SUMPRODUCT((x-mu)^2,f)/SUM(f))
x f(x)
147 0.008541 pdf f(x)
153 0.047459 0.3
159 0.150484
165 0.272742 0.25
171 0.282819
177 0.167799 0.2
183 0.056916
0.15

0.1

0.05

0
145 150 155 160 165 170 175 180 185

x f EF
147 3 1.708166 Best fi t comparati ve f and EF
153 12 9.491811
f EF
159 23 30.09687
70
165 52 54.5483
171 61 56.56382 60
177 39 33.55981 50
183 10 11.38312
40

30

20

10

0
145 150 155 160 165 170 175 180 185
90 95 100
 90 95 100

and ef

90 95 100

ence plot the pdf,freq and EF.

 175 180 185

and EF

175 180 185

Q1.
Find the correlation coefficient ,interpret it,and fit a regression line (Y or X)
Using the priciple of least square for the given data of varaibles X and Y.
Using Formula
X Y X^2 Y^2 XY count n
9 6 81 36 54 slope m
15 9 225 81 135 intercept c
4 12 16 144 48 regression y=mx+c
4 8 16 64 32 corr coef R
15 13 225 169 195 coeff of d R^2
4 13 16 169 52
15 8 225 64 120
13 11 169 121 143
11 3 121 9 33
13 10 169 100 130
3 1 9 1 3
1 14 1 196 14
13 4 169 16 52
7 6 49 36 42
7 2 49 4 14
15 7 225 49 105
3 10 9 100 30
1 1 1 1 1
9 10 81 100 90
3 3 9 9 9
6 13 36 169 78
11 8 121 64 88
13 11 169 121 143
14 13 196 169 182
8 10 64 100 80
8 15 64 225 120
8 5 64 25 40
8 1 64 1 8
1 4 1 16 4
15 12 225 144 180
Sum 257 243 2869 2503 2225

Using Excel functions

count n 30 Regressio
slope m 0.214725
16
intercept c 6.260526
14
regression line y=mx+c 0.214725x+6.260526
corr coef R 0.239888 12
coeff of determination R^2 0.057546 10
f(x) = 0.214724539233804 x + 6.260
8
Y

4
 14

12

10
f(x) = 0.214724539233804 x + 6.260
8

Y
On the basis of correlation of determination we can say that the relation is 6
moderate.
4

0
0 2 4 6
X

Q2.
Find the correaltion coefficient, regression line.
Does the scatter plot of this data support the use of simple linear regression model.

X Y X^2 Y^2 XY
5 4 25 16 20
12 10 144 100 120
14 13 196 169 182
17 15 289 225 255
23 15 529 225 345
30 25 900 625 750
40 27 1600 729 1080
47 46 2209 2116 2162
55 38 3025 1444 2090
67 46 4489 2116 3082
72 53 5184 2809 3816
81 70 6561 4900 5670
96 82 9216 6724 7872
112 99 12544 9801 11088
127 100 16129 10000 12700
Sum 798 643 63040 41999 51232

Using Excel functions Using Formula

count n 15 15 Regressio
slope m 0.826973 0.826973
120
intercept c -1.128305 -1.128305
regression line y=mx+c y=0.826973x-1.1283 y=0.826973x-1.1283 100 f(x) = 0.826973147320561 x − 1.1283
corr coef R 0.987557 0.987557
coeff of determination R^2 0.975269 0.975269 80

60
Y

40

Yes, the scatter plot of this data support the use of simple linear regression
20
model.
0
0 20 40 60
X
40

20

0
0 20 40 60
X
Using Formula
30
0.214725
6.260526
0.214725x+6.260526
0.239888
0.057546

Regression line

= 0.214724539233804 x + 6.26052644723041
= 0.214724539233804 x + 6.26052644723041

4 6 8 10 12 14 16
X

Regression line

0.826973147320561 x − 1.12830477078719

20 40 60 80 100 120 140

X
20 40 60 80 100 120 140
X