1 / 46

Joint Frequency Distributions Capital Credit Union

Joint Frequency Distributions Capital Credit Union. Issue: Analyze the credit card balances by gender of the card holder. Objective: Use Excel to develop a joint frequency distribution for the credit card balances by gender. Data File is Capital.xls.

pcostales
Download Presentation

Joint Frequency Distributions Capital Credit Union

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Joint Frequency Distributions Capital Credit Union Issue: Analyze the credit card balances by gender of the card holder. Objective: Use Excel to develop a joint frequency distribution for the credit card balances by gender. Data File is Capital.xls

  2. 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

  3. 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

  4. 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”

  5. 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

  6. Joint Frequency Distributions-Capital Credit Union • Place the cursor in any Balance Cell • RIGHT click and select Group

  7. Joint Frequency Distributions-Capital Credit Union • Starting at: 90 • Ending at: 1589 • By: 150

  8. 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

  9. Joint Frequency Distributions-Capital Credit Union Complete!

  10. Joint Relative Frequencies Capital Credit Union Objective: Use Excel 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.

  11. Joint Relative Frequencies-Capital Credit Union • Right-mouse click anywhere in the values of the pivot table. • Select Value Field Settings

  12. Joint Relative Frequencies-Capital Credit Union Select the Show Values as tab Scroll down and click % of total (2010: grand total)

  13. Joint Relative Frequencies-Capital Credit Union Now displayed as percentages rather than values

  14. ** Do the rest of them by yourself **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 to develop bar charts for the starting salary data for males and females. Data File is Bach.xls

  15. Bar Charts-Bach, Lombard & Wilson Open the Excel file called Bach.xls

  16. Bar Charts-Bach, Lombard & Wilson • Select data in columns B and C • Select Insert • Select Column

  17. Bar Charts-Bach, Lombard & Wilson Select 2-D paired columns

  18. Bar Charts-Bach, Lombard & Wilson Which brings up…

  19. Bar Charts-Bach, Lombard & Wilson • To change the horizontal axis labels to Years: • Select Data • Select Edit

  20. Bar Charts-Bach, Lombard & Wilson Select cells A2 through A8 to display years in the label

  21. Bar Charts-Bach, Lombard & Wilson

  22. 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 to develop bar charts for the percent of males and females hired with MBA’s. Data File is Bach.xls

  23. Bar Charts-Bach, Lombard & Wilson

  24. Bar Charts-Bach, Lombard & Wilson • Select the Graph • Select “Select Data”

  25. Bar Charts-Bach, Lombard & Wilson Select Edit Select the data in Column A

  26. Bar Charts-Bach, Lombard & Wilson

  27. 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 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

  28. Bar Charts-Bach, Lombard & Wilson • Select Columns G1 – J2 • Select Insert • Select Column

  29. Bar Charts-Bach, Lombard & Wilson

  30. 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 Series1

  31. Bar Charts-Bach, Lombard & Wilson

  32. Line Charts- McGregor Vineyards Issue: Analyze the sales and profits data over time. Objective: Use Excel to develop line charts for weekly sales and profits. Data File is McGregor.xls

  33. Line Charts-McGregor Vineyards Open the Excel file called McGregor.xls The file contains 20 weeks of historic data. The last row is 21

  34. Line Charts-McGregor Vineyards • Select The data in Column B • Select Insert • Select Line • Select the 2-D Line with Markers sample line chart

  35. Line Charts-McGregor 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

  36. Line Charts-McGregor Vineyards • To add Axis Titles: • Select Layout Tab • Select Axis Titles • First Select Horizontal Axis Title and then Click Title Below Axis – Replace with Week • Go through same steps above and click Primary Vertical Axis Title and then click Rotated Title – Replace with Sales • To Change Gridlines: • Click Gridlines • Point to Primary Horizontal Gridlines • Click None • Resize to fit in the range D1 through L11.

  37. Line Charts-McGregor Vineyards

  38. Line Charts-McGregor Vineyards Repeat the process for Profit, changing titles as shown

  39. Line Charts-McGregor Vineyards Rebuild the line chart using both Sales and Profit, changing titles as shown and using Move Chart to move to another page.

  40. Line Charts-McGregor Vineyards

  41. Line Charts-McGregor Vineyards • To improve the scaling need a 2-axis chart • Click the Profit line on the chart • On the Format tab, select Format Selection • Select Secondary Axis option button

  42. Line Charts-McGregor Vineyards

  43. Scatter Diagrams- Personal Computers Issue: Analyze the relationship between PC sales price and the processing speed of the PC Objective: Use Excel to develop a scatter diagram for PC price and speed. Data File is Personal Computers.xls

  44. Scatter Diagrams- Personal Computers Open the Excel file called Personal Computers.xls The file contains data for 14 PC’s

  45. Scatter Diagrams- Personal Computers • Select columns to be graphed (C and G) • Select Insert • Select Scatter • Select Scatter with only Markers

  46. Scatter Diagrams- Personal Computers • Use Design tab to move to a new sheet • Use Layout tab to add titles and remove grid lines

More Related