|
|
|
MS
EXCEL : GENERAL, DATABASE, GRAPHICAL AND STATISTICAL DATA ANALYSIS
FEATURES USING A CASE STUDY
Problem:
The following is the data collected from a survey on a crop in a
district with the following objectives:
-
To assess the performance of the particular crop at various locations
-
To suggest ways to improve crop productivity on sustainable basis
-
To suggest alternate crop or cropping system
The
data is from five villages selected at random. Ten farmers from
each village were again selected at random.(Data
in Excel format)
|
SNO
|
Name of
the Farmer
|
Village
|
Crop Area (ha)
|
Crop Production (Qtl.)
|
Total
Land Holding
|
FYM
|
N
|
P
|
K
|
Soil Depth
|
Pesticide
Cost per Ha
|
Labour
Cost ha
|
Machinery
Cost Ha
|
|
1
|
F1
|
A
|
0.41
|
4.00
|
0.62
|
20.0
|
111
|
12.9
|
0
|
90.0
|
1209.68
|
1451.61
|
726
|
|
2
|
F2
|
A
|
0.82
|
10.00
|
0.62
|
30.0
|
148
|
25.8
|
16.1290
|
45.0
|
1209.68
|
2903.23
|
1452
|
|
3
|
F3
|
A
|
0.4
|
6.00
|
1.03
|
50.0
|
112
|
7.8
|
0
|
45.0
|
776.70
|
873.79
|
437
|
|
4
|
F4
|
A
|
0.6
|
9.00
|
1.23
|
50.0
|
93
|
6.5
|
8.1300
|
90.0
|
569.11
|
1097.56
|
549
|
|
5
|
F5
|
A
|
0.8
|
11.00
|
2.26
|
50.0
|
51
|
3.5
|
0
|
90.0
|
619.47
|
796.46
|
398
|
|
6
|
F6
|
A
|
0.8
|
9.00
|
2.05
|
40.0
|
67
|
3.9
|
0
|
90.0
|
731.71
|
878.05
|
439
|
|
7
|
F7
|
A
|
0.8
|
12.00
|
2.05
|
30.0
|
56
|
7.8
|
0
|
45.0
|
682.93
|
878.05
|
439
|
|
8
|
F8
|
A
|
1.2
|
17.99
|
5.35
|
70.0
|
30
|
3.0
|
0
|
90.0
|
467.29
|
504.67
|
252
|
|
9
|
F9
|
A
|
6.2
|
62.01
|
11.11
|
30.0
|
78
|
10.8
|
0
|
90.0
|
630.06
|
1215.12
|
608
|
|
10
|
F10
|
A
|
2.1
|
38.01
|
5.76
|
20.0
|
32
|
4.2
|
1.7361
|
45.0
|
607.64
|
781.25
|
391
|
|
11
|
F11
|
C
|
2.06
|
37.68
|
0.82
|
20.0
|
187
|
32.5
|
0
|
90.0
|
0.00
|
3306.10
|
889
|
|
12
|
F12
|
C
|
2.06
|
20.00
|
12.34
|
80.0
|
139
|
9.7
|
0
|
90.0
|
0.00
|
752.27
|
98
|
|
13
|
F13
|
C
|
1.7
|
32.19
|
1.23
|
30.0
|
112
|
19.4
|
0
|
90.0
|
0.00
|
2008.13
|
790
|
|
14
|
F14
|
C
|
0.8
|
12.00
|
1.65
|
100.0
|
112
|
19.4
|
0
|
90.0
|
0.00
|
1496.97
|
442
|
|
15
|
F15
|
C
|
0.8
|
8.00
|
2.47
|
50.0
|
78
|
11.7
|
0
|
90.0
|
0.00
|
1395.55
|
590
|
|
16
|
F16
|
C
|
0.8
|
16.67
|
3.71
|
30.0
|
61
|
12.4
|
0
|
90.0
|
0.00
|
1384.91
|
393
|
|
17
|
F17
|
C
|
0.8
|
16.67
|
2.06
|
50.0
|
96
|
19.4
|
0
|
90.0
|
0.00
|
2609.71
|
295
|
|
18
|
F18
|
C
|
0.8
|
12.00
|
4.12
|
30.0
|
56
|
11.3
|
0
|
90.0
|
0.00
|
2311.17
|
177
|
|
19
|
F19
|
C
|
0.8
|
9.95
|
6.17
|
50.0
|
36
|
11.4
|
0
|
90.0
|
0.00
|
952.51
|
217
|
|
20
|
F20
|
C
|
0.6
|
8.13
|
4.93
|
70.0
|
35
|
7.3
|
0
|
90.0
|
0.00
|
1191.08
|
148
|
|
21
|
F21
|
D
|
0.41
|
1.59
|
1.64
|
20.0
|
140
|
39.0
|
0
|
90.0
|
0.00
|
768.29
|
0
|
|
22
|
F22
|
D
|
0.82
|
12.00
|
1.65
|
20.0
|
28
|
6.5
|
0
|
90.0
|
0.00
|
948.48
|
0
|
|
23
|
F23
|
D
|
0.8
|
10.00
|
0.41
|
10.0
|
93
|
13.0
|
0
|
90.0
|
0.00
|
2829.27
|
0
|
|
24
|
F24
|
D
|
0.8
|
12.00
|
2.06
|
20.0
|
84
|
14.5
|
0
|
90.0
|
0.00
|
1000.00
|
0
|
|
25
|
F25
|
D
|
1.2
|
16.00
|
3.09
|
40.0
|
84
|
19.4
|
0
|
90.0
|
0.00
|
894.82
|
0
|
|
26
|
F26
|
D
|
0.8
|
11.94
|
3.70
|
50.0
|
62
|
17.3
|
0
|
90.0
|
0.00
|
941.89
|
0
|
|
27
|
F27
|
D
|
1.2
|
15.01
|
2.67
|
50.0
|
89
|
19.4
|
0
|
90.0
|
0.00
|
771.54
|
0
|
|
28
|
F28
|
D
|
1.0
|
15.07
|
5.35
|
20.0
|
56
|
7.8
|
0
|
90.0
|
0.00
|
423.36
|
0
|
|
29
|
F29
|
D
|
3.3
|
47.90
|
4.11
|
50.0
|
37
|
10.4
|
0
|
90.0
|
0.00
|
1110.71
|
0
|
|
30
|
F30
|
D
|
2.9
|
27.96
|
6.17
|
50.0
|
47
|
11.4
|
0
|
90.0
|
0.00
|
706.65
|
0
|
|
31
|
F31
|
E
|
0.41
|
5.29
|
2.57
|
20.0
|
70
|
19.5
|
0
|
90.0
|
408.56
|
751.95
|
284
|
|
32
|
F32
|
E
|
0.82
|
11.14
|
1.54
|
20.0
|
70
|
19.4
|
0
|
90.0
|
1136.36
|
2043.83
|
789
|
|
33
|
F33
|
E
|
0.82
|
7.40
|
2.06
|
20.0
|
168
|
39.0
|
0
|
90.0
|
1274.27
|
2263.99
|
826
|
|
34
|
F34
|
E
|
0.62
|
7.22
|
1.85
|
20.0
|
45
|
19.4
|
0
|
90.0
|
945.95
|
1701.35
|
657
|
|
35
|
F35
|
E
|
0.82
|
11.94
|
3.56
|
50.0
|
37
|
10.4
|
0
|
90.0
|
983.15
|
1737.36
|
683
|
|
36
|
F36
|
E
|
0.82
|
9.95
|
4.12
|
40.0
|
62
|
13.0
|
0
|
90.0
|
849.51
|
1501.21
|
590
|
|
37
|
F37
|
E
|
0.82
|
9.95
|
4.12
|
30.0
|
69
|
9.0
|
0
|
90.0
|
849.51
|
1501.21
|
590
|
|
38
|
F38
|
E
|
1.23
|
11.14
|
5.43
|
50.0
|
17
|
7.5
|
0
|
90.0
|
966.85
|
1698.53
|
671
|
|
39
|
F39
|
E
|
1.23
|
8.97
|
8.22
|
50.0
|
67
|
13.6
|
0
|
90.0
|
1277.37
|
2230.54
|
887
|
|
40
|
F40
|
E
|
1.65
|
20.82
|
9.04
|
50.0
|
45
|
10.4
|
0
|
45.0
|
774.34
|
1356.19
|
538
|
|
41
|
F41
|
B
|
1.23
|
22.50
|
1.23
|
30.0
|
54
|
9.3
|
0
|
90.0
|
0.00
|
4969.92
|
988
|
|
42
|
F42
|
B
|
0.82
|
15.00
|
1.65
|
30.0
|
90
|
31.2
|
0
|
90.0
|
0.00
|
3561.82
|
736
|
|
43
|
F43
|
B
|
0.62
|
7.56
|
2.06
|
30.0
|
45
|
31.1
|
0
|
90.0
|
0.00
|
1555.34
|
531
|
|
44
|
F44
|
B
|
0.41
|
7.50
|
2.06
|
30.0
|
75
|
51.9
|
0
|
90.0
|
0.00
|
1340.58
|
531
|
|
45
|
F45
|
B
|
0.82
|
12.00
|
2.06
|
20.0
|
39
|
36.0
|
0
|
90.0
|
0.00
|
2027.18
|
590
|
|
46
|
F46
|
B
|
0.82
|
9.95
|
4.53
|
50.0
|
112
|
31.1
|
0
|
90.0
|
0.00
|
1618.10
|
241
|
|
47
|
F47
|
B
|
1.23
|
14.91
|
2.88
|
50.0
|
22
|
0.0
|
0
|
90.0
|
0.00
|
2378.13
|
380
|
|
48
|
F48
|
B
|
1.65
|
20.03
|
4.94
|
70.0
|
51
|
29.5
|
0
|
90.0
|
0.00
|
1533.20
|
221
|
|
49
|
F49
|
B
|
1.65
|
28.03
|
6.99
|
50.0
|
28
|
19.5
|
0
|
90.0
|
0.00
|
1048.64
|
209
|
|
50
|
F50
|
B
|
2.06
|
33.50
|
6.58
|
40.0
|
102
|
26.5
|
0
|
90.0
|
0.00
|
892.40
|
166
|
Perform
the following analysis
-
Prepare the data for analysis
-
Calculate crop productivity, convert the data of "Total land
holding", "Soil depth " amenable to analysis
-
Summarize the data with suitable statistical measures and frequency
distribution
-
Find the best relationship to explain the variations in crop
yield through regression analysis
-
View data in various ways using database features
-
Represent the data through appropriate graphs and tables
-
Prepare a report on your results
|
|
|
|
