*Objective:Learn to use basic Excel utilities to create plots, graphs and simple calculations. TI calculator. .Submit in BB Messages.

Open a blank Excel file.

1. Problem 1 Open data files:JeepSales.Copy paste into Sheet 1

(a) Use PivotTable to write a Frequency table from JeepSales.xlsx.

(b) Construct a Frequency Bar chart, Relative Frequency Bar chart, Cumulative Bar chart,

(c) Relative Frequency Pie chart.

Add chart title, axis labels. Reduce plot sizes. Title Problem 1 on top of worksheet.

2. Problem 2 Open:Invest.Copy paste into Sheet 2

(a) Use Pivot Table to derive the Contigency Frequency table) for categories: BOND, STOCK, TAXDEF, HIGH , MED, LOW.

(b) Frequency Bar chart, Percent Bar Chart each for HIGH, MED, LOW. Add chart title, axis labels. Reduce plot sizes.

3. Problem 3[ Histograms, using Data analysis, Histogram]

(a) Open Highschool Dropout Rate. Construct a Frequency Histogram, using classes: 0 to 2, 3 to 5, 6 to 8, and 9 to 10

(b) Open Medicare Drug Plans Cost, Construct a Frequency Histogram using classes: 0 < 3, 3 < 6, 6 < 9, and so on.

(c) Open: Design rates Contruct a Frequency Histogram, with a revised Bin size. (Bin width =3 ).

Add chart title, axis labels. Reduce plot sizes.

1

4.

Problem 4[Descriptive Statistics]Open:GasMiles.Copy paste to Sheet 4

(a) Run a Descriptive Statistics. (b) Use simple Excel functions: AVERAGE(), VAR(), STDEV() to check the above results (c) Calculate Q1, Q2, Q3. Perform a Box Plot

Problem 5

5.

Open file:Weight-Height. Copy paste to Sheet 5 Data: Sample of 15 men. Weight (in pounds) and Height (in inches)

(a) Perform ascatterplotof Weight on the horizontal, Hight on the vertical. Notice any pattern. (b) UseExcel to calculate the following

=()2

=()2

=( )()

Correlation coefficient =

(c) Use TI Calculator, to calculate SXX, SYY, SXY, using 2-var stats