HOME WORK WEB PAGE FOR CLASS MATH 251 & MATE 251 & MATE 345

 

Chapter 1&2 Data and Statistics

Please do the HW for the attach data (softdrinks.xls) MAIN PAGE

 

 

Frequency (solution)

Follow the steps below to determine frequencies of each variable

Open the excel file

 

Write the soft drinks in C column. like Coke Classic..

Step1 then select cell D2, access the Function Wizard, choose (countif) from the list of statistical functions

 

Step2 Click OK

 

Step 3 when the countif dialog box appears:

                            Enter $A$2:$A$51 in the RANGE BOX

                            Enter C2 in the CRITERIA BOX

                            Click OK

STEP4 Copy cell D2 to cells D3:D6

 

Due Date 13th of October 2005 by 5pm

 

Good Luck

 

Mehmet E. San

 

1) Please do the following for the attach data (IBD.xls)

 

a. Prepare a crosstabulation of the data on sales/margins/ROE.and Industry Group Relation,

b. Prepare a frequency distribution for the data on sales/margins/ROE.

c. Prepare a frequency distribution for the data on industry group relative strength

d. how was the crosstabulation helped in preparing the frequency distributions in parts (b) and (c)

 

2)  Please do the following for the attach data (scatter.xls)

 

a. Develop a scatter diagram for the relationship between x and y

b. What is the relationship, if any, between x and y?

 

Due date October 13th 2005 by 5pm

 

Bar Graphs and Histograms (stereo.xls)(solution)

 

Here I show the Chart Wizard can be used to construct bar and histograms. Let us start by developing a bar graph for the soft drink data, the chart we are going to develop is an extension of that worksheet which is given as hw. And in the softdrinks.xls file Column A is all drinks Column C is repeating drinks and D is Frequency (it was HW)

Than in order to do the bar graphs of soft drinks purchases follow the steps.

Step1. Select cells C1:D6

Step 2 Select the Chart Wizard button on the Standard Toolbar(or insert pull-down menu and choose     the Chart option)

Step3 when the chart wizard-step1 of 4-chart dialog box appears: Choose Column in the chart type list

Chose clustered column from the chart sup-type display

Select next

Step4 When the chart wizard-step 3 of 4-chart source data dialog box appears: select Next

Step 5 when the chart wizard-step 3 of 4-chart options dialog box appears: select the Titles tab

            Enter Bar Graph of Soft Drink Purchases in the chart title box

            Enter the soft drink in the category (x) axis box

            Enter Frequency in the Values (Y) axis box

            Select the Legend tab and then

            Remove the check in the Show Legend Box

            Select Next

Step 6 When the Chart Wizard-Step4 of 4-Chart Location dialog box appears:

            Specify a location for the new chart (we used the current worksheet by selecting As Object in)

            Select Finish

In order to eliminate the gap between each of the bars. The following steps accomplish this process

 

Step1 Right Click on any bar in the column chart to produce a list of options

Step2 Chose Format Data Series

Step3 When the format data series dialog box appears: Select the Options tab Enter 0 in the Gap Width box Click OK

 

PIVOTTABLE REPORT (restaurant.xls)(solution)

Step1 Select the Tools pull-down menu

Step2 Choose Options

Step3 When the options dialog box appears:

                        Select the Custom lists tab

In the List entries box, type Good and press Enter, type Very Good and press Enter, and type Excellent

Select Add

Click OK

            We are ready to use the PivotTable Report to construct a crosstabulation of the data for qualifying rating and meal price. The following steps are necessary

 

Step1 Select the Data pull down menu

Step2 Choose the PivotTable and PivotChart Report

Step3 When the PivotTable and PivotChart Wizard-Step 1 and 3 dialog box appears:

                        Choose Microsoft Excel list or database

                        Choose PivotTable

                        Select Next

Step4 When the PivotTable and PivotChart Wizard-Step 2 and 3 dialog box appears:

                        Enter A1:C301 in the range box

                        Select Next

Step5 When the PivotTable and PivotChart Wizard-Step3 of 3 dialog box appears:

                        Select New Worksheet

                        Click the Layout button

Step6 When the PivotChart Wizard-Layout diagram appears

                        Drag the Quality Rating field button to the ROW selection of the diagram

                        Drag the Meal Price ($) field button the COLUMN selection of the diagram

                        Drag the Restaurant field button to the DATA selection of the diagram

                        Double click the Sum of Restaurant field button in the DATA selection

                        When the PivotTable Field dialog box appears:

                                    Choose Count under Summarize by

                                    Click OK

                        Click OK

Step7 When the PivotTable and PivotChart Wizard-Step3 of dialog box reappears select Finish

 

We must group the columns into four price categories for the worksheet shown in follow

Step1 right Click on Meal Price ($) in the cell B3 of the PivotTable

Step2 Select Group and Outline

            Choose Group

Step3 When the Grouping dialog box appears

                        Enter 10 in the Starting at box

                        Enter 49 in the Ending at box

                        Enter 10 in the By Box

                        Click OK

 

MAIN PAGE