Question

*Work the problems in EXCEL.

1. Problem 1:

=======>>>>>>Open data files: JeepSales.xlsx and JeepTable.xlsx ( copy this)

(a) Learn the PivotTable to derive JeepTables.xlsx from JeepSales.xlsx. [Hint: Click Insert,  

PivotTable, highlight the data]

(b) Construct a Frequency Bar chart, Pie chart.  

Reduce graph size. Copy/paste into a MS Word file.

Jeep Model

W

L

G

L

G

C

L

G

C

G

L

W

L

G

W

G

L

W

W

G

L

L

L

G

L

G

L

G

L

W

L

G

L

G

L

C

C

W

C

G

L

G

G

L

C

C

C

G

L

L

C

G

L

C

W

G

G

W

C

W

W

L

C

L

L

C

G

L

G

L

C

W

C

G

G

L

L

C

W

L

G

C

C

G

C

C

L

C

G

C

G

G

C

W

W

L

C

C

L

L

C

C

C

L

L

C

C

L

L

G

L

C

G

G

C

C

L

C

G

C

C

G

C

C

L

C

C

L

W

C

L

W

C

L

W

L

W

G

W

L

L

C

C

W

L

C

L

G

W

C

W

W

L

L

C

L

L

G

L

G

C

C

G

G

C

C

L

L

L

W

C

G

G

L

W

G

L

L

L

G

L

G

G

L

G

G

G

C

C

C

G

L

G

C

C

G

C

G

L

W

G

C

L

G

L

C

G

C

C

L

G

L

G

C

L

G

G

C

G

G

W

L

G

L

C

C

L

L

C

L

C

G

L

L

C

L

C

G

G

G

C

W

G

G

L

W

L

G

G

L

L

2. Problem 2:   

=========>>>>>>Open data file: Design.xlsx. Construct,

(a) A Histogram with a revised Bin size. (Bin width =2).  

(b) A Frequency Polygon.  

(c) A Cumulative Percentage Histogram (ogive).

Reduce size. Copy and paste into the MS Word file.

Rating
34
32
24
32
31
32
33
25
30
28
28
30
33
27
20
30
33
34
29
33
34
31
31
32
26
22
31
31
32
30
33
27
32
33
28
30
28
32
30
29
26
32
25
33
35
27
29
31
32
32
33
34
32
29
33
29
31
31
34
33

Answer 1

(a) Learn the PivotTable to derive JeepTables.xlsx from JeepSales.xlsx. [Hint: Click Insert,  

PivotTable, highlight the data]

Step1: First bring data in to excel sheet. Select the entire data set

2: Next go to "Insert" tab in the excel option and choose pivot table.

3:Updat the pivot table option to create a pivot table in the new sheet.

4:New sheet will be created with enabled pivot table options

5: click and drag "Jeep Model" data in to both rows and values.

6: Pivot table has been created for all jeep model and with the count.

(b) Construct a Frequency Bar chart, Pie chart.  

1. Select the pivot table and goto Insert tab again and choose "column - 2D" chart and create frequency bar chart.

2: Change the header and Axis titles (if required). Right click the bar in the chart and choose "Add DAta labels to add the count of each jeep models

3: same procedure to create pie chart for the pivot values.

(a) A Histogram with a revised Bin size. (Bin width =2)

Class interal
20 - 22
22 - 24
24 - 26
26 - 29
29 - 31
31 - 33
33 - 35

Cumulative perentage

Formula to calculate:

select class inteval, frequency and cumulative frequency to create "cumulative frequency diagram ( chart 1)

- Next midpoint and frequency to create frequency polygon chart (chart 2)