Guide to Using Excel 2007 For Basic Statistical
Guide to Using Excel 2007 For Basic Statistical Applications To Accompany Business Statistics: A Decision Making Approach, 7 th Ed. Chapter 2: Graphs, Charts and Tables - Describing Your Data By Groebner, Shannon, Fry, & Smith Prentice-Hall Publishing Company Copyright, 2008
Chapter 2 Excel Examples n n Frequency Distributions Athletic Shoe Survey Histograms. Capital Credit Union Joint Frequency Distributions. Capital Credit Union Joint Relative Frequencies. Capital Credit Union More Examples
Chapter 2 Excel Examples (continued) ® Bar Charts. Bach, Lombard & Wilson ® Line Charts. Mc. Gregor Vineyards ® Scatter Diagrams. Personal Computers
Frequency Distributions Athletic Shoe Survey Issue: Analyze the data from a survey of 100 college students regarding the number of Nike shoes they own. Objective: Use Excel 2007 to develop a frequency distribution for the number of Nike shoes owned by college students. Data File is Sport. Shoes. xls
Frequency Distributions-Athletic Shoe Survey Open the Excel file called Sport. Shoes. xls File contains 100 observations – Last row is 102 – Column E contains the Number of Nike Shoes Owned
Frequency Distributions-Athletic Shoe Survey Enter Possible number of shoes owned
Frequency Distributions-Athletic Shoe Survey Select the area to receive the frequency
Frequency Distributions-Athletic Shoe Survey • Select the Formulas Tab • Select the indicated pulldown menu (More Functions button) • Select Statistical • Select Frequency
Frequency Distributions-Athletic Shoe Survey • The Array containing “Number Nike” • The Array containing the possible choices • The Results are displayed Note: The function returns an additional result (The Zero)
Frequency Distributions-Athletic Shoe Survey Press Control, Shift and Enter Do Not press OK
Frequency Distributions-Athletic Shoe Survey And the results are:
Histograms Capital Credit Union Issue: Analyze credit card balances for Capital Credit Union customers using a frequency distribution and histogram. Objective: Use Excel 2007 to develop a frequency distribution and histogram for credit card balances. Use ten class intervals. Data file is Capital. xls
Histograms-Capital Credit Union Notice that this worksheet uses the R 1 C 1 reference style for column and row labels. To change this to the standard reference style (A 1), on the Office Button, click the Excel Options button, select Formulas, and then remove the check from the R 1 C 1 reference style check box. Click OK. • Open the Excel file “Capital. xls” • The file contains 300 observations – The last row is 302 – Column 2 (B) contains the balances
Histograms-Capital Credit Union c Use Excel’s “Max” function to determine the largest value
Histograms-Capital Credit Union c Use Excel’s Min function to determine the smallest value
Histograms-Capital Credit Union Compute Class Width • Compute range as Maximum – Minimum Intervals are: • $90 to < $240 • $240 to < $390 • $390 to < $540 Etc… • Divide range by 10 to construct 10 classes – 1394 / 10 = 139. 40 • Round up to $150. 00
Histograms-Capital Credit Union Construct the Bins (The upper limits of each class
Histograms-Capital Credit Union • Select the Data tab • Select Data Analysis • Select Histogram • OK
Histograms-Capital Credit Union Input Range is Column B Bin Range is Column F – The range of Bins that you entered Select New Worksheet Ply Select Chart Output OK
Histograms-Capital Credit Union This is Excel’s default output. It should be modified for most applications. The first change would be to delete the Frequency legend , change the Bins title to Balances and increase the size of the chart using the resize handles. Frequency legend Bins Title Sizing Handle
el’s default on a histogram is nclude a More category. move the More category from chart. Select the chart, right use click, Select Data, Edit quency. In the Edit Series og box, change Series Values B$11 rather than $B$12. Click
Histograms-Capital Credit Union • Right click on any of the bars in the Histogram • Select Format Data Series
Histograms-Capital Credit Union Set Series Overlap and Gap Width to Zero to format the Histogram as shown
Histograms-Capital Credit Union To put border colors around the bars of the histogram , select Border Color, Choose Solid line, Select the Color arrow, and Choose Dark Blue, Text 2 Theme Color.
Histograms-Capital Credit Union Change the Bins categories to 0239. 99, 240 -389. 99, etc. and delete the More bins and frequency. Change the Histogram title to Credit Card Balances. See next slide for results of these changes.
Histograms-Capital Credit Union
Joint Frequency Distributions Capital Credit Union Issue: Analyze the credit card balances by gender of the card holder. Objective: Use Excel 2007 to develop a joint frequency distribution for the credit card balances by gender. Data File is Capital. xls
Joint Frequency Distributions-Capital Credit Union Open the Excel file called Capital. xls • The file contains 300 observations. The last row is 301. • Column 2 (B) contains the balances. • Column 3 (C) contains the gender codes
Joint Frequency Distributions-Capital Credit Union • Select Insert • Select Pivot Table • Table/Range selected automatically • Place on New Worksheet 1. Place the cursor in any data cell
Joint Frequency Distributions-Capital Credit Union • Drag Gender to the “Drop Column Fields Here” box • Drag Gender to the “Drop Data Items Here” box • Drag “Credit Card Account Balance” to the “Drop Row Fields Here”
Joint Frequency Distributions-Capital Credit Union Yielding! However we need to group the balance Information and change the Sum of Gender to Count of Gender
Joint Frequency Distributions-Capital Credit Union • Place the cursor in any Balance Cell • RIGHT click and select Group
Joint Frequency Distributions-Capital Credit Union • Starting at: 90 • Ending at: 1589 • By: 150
Joint Frequency Distributions-Capital Credit Union • Right click Sum of Gender • Select Value Field Setting to Open Value Field Setting dialog box • In dialog box, select Count
Joint Frequency Distributions-Capital Credit Union Complete!
Joint Relative Frequencies Capital Credit Union Objective: Use Excel 2007 to develop a joint relative frequency distribution for the credit card balances by gender. Data File is Capital. xls Note: See Previous Example for Pivot Table Instructions.
Joint Relative Frequencies-Capital Credit Union • Right-mouse click anywhere in the values of the pivot table. • Select Value Field Settings
Joint Relative Frequencies-Capital Credit Union Select the Show Values as tab Scroll down and click % of total
Joint Relative Frequencies-Capital Credit Union Now displayed as percentages rather than values
Bar Charts Bach, Lombard, & Wilson Issue: An electronics company is being sued on the grounds that female new hires are paid less than male new hires. The law firm needs to analyze salary data to prepare the case. Objective: Use Excel 2007 to develop bar charts for the starting salary data for males and females. Data File is Bach. xls
Bar Charts-Bach, Lombard & Wilson Open the Excel file called Bach. xls
Bar Charts-Bach, Lombard & Wilson • Select data in columns B and C • Select Insert • Select Column
Bar Charts-Bach, Lombard & Wilson Select 2 -D paired columns
Bar Charts-Bach, Lombard & Wilson Which brings up…
Bar Charts-Bach, Lombard & Wilson To change the horizontal axis labels to Years: • Select Data • Select Edit
Bar Charts-Bach, Lombard & Wilson Select cells A 2 through A 8 to display years in the label
Bar Charts-Bach, Lombard & Wilson
Bar Charts. Bach, Lombard, & Wilson (continued) Issue: An electronics company is being sued on the grounds that female new hires are paid less than male new hires. The law firm needs to analyze salary data to prepare the case. Objective: Use Excel 2007 to develop bar charts for the percent of males and females hired with MBA’s. Data File is Bach. xls
Bar Charts-Bach, Lombard & Wilson
Bar Charts-Bach, Lombard & Wilson • Select the Graph • Select “Select Data”
Bar Charts-Bach, Lombard & Wilson Select Edit Select the data in Column A
Bar Charts-Bach, Lombard & Wilson
Bar Charts. Bach, Lombard, & Wilson (continued) Issue: An electronics company is being sued on the grounds that female new hires are paid less than male new hires. The law firm needs to analyze salary data to prepare the case. Objective: Use Excel 2007 to develop a bar chart for the average starting salary of males and females hired with and without MBA’s. Data File is Bach. xls
Bar Charts-Bach, Lombard & Wilson • Select Columns G 1 – J 2 • Select Insert • Select Column
Bar Charts-Bach, Lombard & Wilson
Bar Charts-Bach, Lombard & Wilson To add data labels to the top of the bars: • Select Layout Tab • Select Data Labels • Select Outside End Delete the legend Series 1
Bar Charts-Bach, Lombard & Wilson
Line Charts. Mc. Gregor Vineyards Issue: Analyze the sales and profits data over time. Objective: Use Excel 2007 to develop line charts for weekly sales and profits. Data File is Mc. Gregor. xls
Line Charts-Mc. Gregor Vineyards Open the Excel file called Mc. Gregor. xls The file contains 20 weeks of historic data. The last row is 21
Line Charts-Mc. Gregor Vineyards • Select The data in Column B • Select Insert • Select Line • Select the 2 -D Line with Markers sample line chart
Line Charts-Mc. Gregor Vineyards Need to: • Change the title (Click title and replace with Sales Trend) • Delete the legend Sales (dollars)(click the legend and press Delete) • Add x-axis and y-axis titles • Remove the gridlines
Line Charts-Mc. Gregor Vineyards is Titles: yout Tab is Titles ct Horizontal Axis Title and Title Below Axis – Replace k gh same steps above and ary Vertical Axis Title and Rotated Title – Replace e Gridlines: lines Primary Horizontal Gridlines e fit in the range D 1 through
Line Charts-Mc. Gregor Vineyards
Line Charts-Mc. Gregor Vineyards Repeat process Profit, changin as show
Line Charts-Mc. Gregor Vineyards Rebuild the line chart using both Sales and Profit, changing titles as shown and using Move Chart to move to another page.
Line Charts-Mc. Gregor Vineyards
Line Charts-Mc. Gregor Vineyards mprove the scaling a 2 -axis chart k the Profit line on the Format tab, select at Selection ect Secondary Axis n button
Line Charts-Mc. Gregor Vineyards
Scatter Diagrams. Personal Computers Issue: Analyze the relationship between PC sales price and the processing speed of the PC Objective: Use Excel 2007 to develop a scatter diagram for PC price and speed. Data File is Personal Computers. xls
Scatter Diagrams- Personal Computers Open the Excel file called Personal Computers. xls The file contains data for 14 PC’s
Scatter Diagrams- Personal Computers • Select columns to be graphe (C and G) • Select Insert • Select Scatter with only Markers
Use Design tab to ove to a new sheet Use Layout tab to dd titles and remove id lines Scatter Diagrams- Personal Computers
- Slides: 72