Question

Science MajorsA bank has collected data on the opening balances of new home loans issued in the final quarter of 2018. The dataset of 200 observations is contained in the file: Home Loans (THA 1).xlsx. Based on the data, answer the following questions.

Using the Pivot Tablein Excel, construct a frequency distribution tableof Opening Balance and a histogramdisplaying the table. Suggested class intervals: 0 – 200,000; 200,000 – 400,000, etc.

Obs	Home Loan, Open. Bal.
1	544,723
2	686,461
3	422,582
4	373,228
5	369,600
6	353,011
7	259,339
8	236,532
9	496,713
10	1,052,824
11	454,077
12	806,701
13	482,691
14	920,000
15	444,720
16	780,000
17	377,858
18	366,611
19	283,918
20	216,536
21	291,182
22	776,145
23	375,723
24	403,552
25	342,989
26	191,632
27	800,350
28	268,200
29	519,466
30	293,192
31	414,739
32	352,222
33	258,587
34	228,633
35	329,476
36	324,013
37	239,375
38	489,659
39	279,174
40	457,456
41	409,731
42	1,332,051
43	367,738
44	331,450
45	364,676
46	472,790
47	332,199
48	525,831
49	393,803
50	291,081
51	469,585
52	363,128
53	360,221
54	290,435
55	452,734
56	493,088
57	362,044
58	232,913
59	367,991
60	451,662
61	495,394
62	473,753
63	245,583
64	212,484
65	495,633
66	348,539
67	380,756
68	395,000
69	554,626
70	346,094
71	482,000
72	279,040
73	431,405
74	712,740
75	479,868
76	632,612
77	334,365
78	290,458
79	583,572
80	311,145
81	406,683
82	234,646
83	236,695
84	239,475
85	877,764
86	559,089
87	278,475
88	1,005,103
89	341,572
90	235,907
91	347,033
92	452,277
93	542,540
94	859,707
95	426,113
96	451,640
97	275,643
98	461,818
99	273,911
100	332,744
101	625,000
102	313,953
103	246,979
104	556,798
105	223,566
106	434,694
107	330,051
108	528,349
109	296,459
110	673,742
111	255,977
112	497,893
113	173,452
114	364,564
115	423,801
116	412,745
117	344,350
118	375,594
119	576,789
120	384,224
121	454,949
122	429,826
123	372,453
124	375,593
125	502,794
126	426,347
127	448,161
128	223,132
129	459,372
130	522,012
131	297,186
132	343,947
133	648,363
134	121,147
135	288,658
136	269,492
137	1,310,311
138	167,636
139	377,545
140	564,538
141	1,113,770
142	442,390
143	258,433
144	433,609
145	256,726
146	249,058
147	258,709
148	244,828
149	581,815
150	344,052
151	331,641
152	303,224
153	328,384
154	187,425
155	312,951
156	476,582
157	951,336
158	284,487
159	400,050
160	536,902
161	439,255
162	266,541
163	263,611
164	442,662
165	557,690
166	375,426
167	337,402
168	240,806
169	367,070
170	621,110
171	317,486
172	266,651
173	466,251
174	304,040
175	297,971
176	199,448
177	340,568
178	236,054
179	500,615
180	187,737
181	330,010
182	1,340,394
183	365,058
184	396,187
185	375,523
186	435,712
187	426,242
188	407,575
189	301,040
190	380,631
191	280,846
192	393,315
193	217,536
194	457,019
195	180,584
196	636,068
197	247,106
198	1,458,183
199	434,295
200	462,356

Answer 1

Select the data and using insert--->pivot table

do this

get this

drag the home loan, Opn bal. field to row label ans sum values

click on the sum of home and pick value filed setting

make sure that the summarize by is count

right click on a number under row labels and choose group

enter these values

get this

this is the frequency distribution table (change the column title to Frequency and re-lable the classes (for example 0-199999 to 0-200000)   if needed)

Opening Balance	Frequency
0-199999	8
200000-399999	104
400000-599999	65
600000-799999	10
800000-999999	6
1000000-1199999	3
1200000-1399999	3
1400000-1600000	1

To get the histogram, select the data under Frequency and choose insert--->column

format the graph as needed